Hi,
I’ve got a bit of a weird one that I am not entirely sure if it’s an issue of my database design or can be solved another way.
I have two bases for a webstore, one which holds high level information (items for sale, vendors, master order records) and the other base has individual records for each unique item in each master order record.
Excuse the awful drawing and writing, but the below diagram illustrates how I’ve structured things so far.
Black lines with two arrows show linked fields. One arrow shows synced tables, and the blue line shows what I want to achieve.
The end result I am aiming for is to be able to report on sales associated with each vendor (i.e. Vendor A has sold $500 of items this month). The problem I have is what seems like it would be the logical link of pulling the vendor detail in via the item doesn’t allow me to then total up based on the Vendor anywhere.
I could add in a second link field, to directly connect line items and vendors, but then I’d need to implement a solution to literally replicate the exact data that already exists in the lookup field from the original Vendor table, and ideally I’d rather avoid tacking on extra ‘fixes’ like that wherever possible.
Has anyone come across this type of problem before and/or has a potential solution/advice?
If it helps further illustrate I’ve put together the following example bases:
Thanks in advance! :slightly_smiling_face:
