How to populate summary data in a related table?
Greetings all! I'm trying to set up a sales tracking database, and I'm running into a snag with trying to store some summary data. I realize I can run various reports using summary functions and different sort orders, but that just won't work -- I want to store the summary values for using in other calculations.
Background: I get a sales report from our online credit card processor. People can buy from us using one of two stores, and we have a handful of products with both full and upgrade versions. I'm trying to track sales by day, with detail on by-store and by-product.
The sales report lists every single sale (even one person buying multiple products) as one row per item, so it looks something like this (showing only relevant sample fields, not all the fields I get):
DATE STORE PRODUCT QTY AMT FEES
04-01-2010 Store1 Prod1 2 20 10
04-01-2010 Store2 Prod2 1 12 3
04-02-2010 Store2 Prod1Upgd 1 5 1
04-02-2010 Store1 Prod2 1 12 3
04-02-2010 Store1 Prod2 3 36 9
04-03-2010 Store2 Prod1Upgd 1 8 3
04-03-2010 Store1 Prod2 1 12 3
04-03-2010 Store1 Prod1 1 10 5
One way to solve this is brute force, which is the route I took originally. While it works, it's a real pain, and it's incredibly slow. (The layout that does all the work has 210 fields on it, and adding one new product to the mix will require creating 35 new fields, ugh!)
So then I thought I'd use a related table to track the stats, and created a table with these fields as a test:DATE -> salesreport.DATE
STORE -> salesreport.STORE
PRODUCT -> salesreport.PRODUCT
This works perfectly when I test it: if I enter (based on the above data) 04-02-2010, Store1, and Prod2 then the values for QTY, AMT, and FEES come out right (4, 48, and 12 in this example). But that's just for one record that I typed in. How do I get FMPro, via script or whatnot, to automatically create the records for all the possible iterations of the data in the main table? That is, I need it to iterate through each row in the sales report, and create the entries in the related table, so I can then use those values in other places.
Is this possible, or am I going about this all wrong (entirely possible).