Hello. I have a question re. using a “Rollup” field type but where the values are only “rolled-up” if these meet a certain criterion.
My situation … Two tables:
“ACCOUNTS” which contain a unique ID for the record (Account), Account Name, supporting details, etc…
“ORDERS” which contains unique record ID, Account ID (the unique ID from the Accounts table), Order Date, Product Type ordered, how many units of that product ordered, etc…
Each account has one-to-many orders; each order has one and only account; each order has only one product; one account can have more than one order on the same day (typically for different products).
I have successfully built multiple rollup fields in the ACCOUNTS table that show how many units of each Product Type that account has ordered as well as total units ordered. I have also built a rollup field in the Accounts table that shows the date of the latest order. All this works great.
But here’s where I’m stumped: In the Accounts table, while I have a single field that correctly displays the date of the latest order, what I really want are individual fields showing the dates of the latest order for each Product Type. “Last Order Date Product X” “Last Order Date Product Y” etc… I’m only dealing with two product types right now, and this is not likely to grow to more than 4-6 so I don’t want to start complicating things with a Product Type table. Basically the query is “For this account, look at the ORDERS table and for Product Type X return the latest order date”, and then another field for “… Product Type Y …” etc. as needed.
Suggestions?
