I have an interesting problem:
I have created a Dealer Information System, that we install in our dealers to track sales to their customers. We then pay the dealer and sales rep commission based on the sales.
I have serveral table, as outlined below:
1. Inventory Data: contains SKUID, SKUName, SKUexworksPrice, etc, This table will grow as the dealer adds new lines and SKUs.
2. Customer Data: contains CustomerID, name, etc
3. Order Table: looks up the customer information and is then linked to InventoryOut table
4. InventoryOut Table: when a ordered is completed in the order table the information is saved here as Customer ID, SKUID, SKUOrderQuanity, SKUTotalprice, RecordID(RecordID is linked to the recordID in the Order Table).
5. CustomerOrder Table: This just contains the CustomerID, (When an order is place this information is written to this table, it checks for Duplicate ID first. This eliminated the problem of trying to sort the InventoryOut table for duplicate Customer IDs when it comes time to record payments from the customers).
Now I am trying to create a sales report that would look like this:
SKU1 SKU2 SKU3 SKU4 .......etc
Customer 1 (Total) (Total) (Total) (Total)
Customer 2 (Total) (Total) (Total) (Total)
Customer 3 (Total) (Total) (Total) (Total)
Grand Total (Total) (Total) (Total) (Total)
I have set up a new table "Inventory Report" This is linked to all the above table:
I created a body in the Inventory report table that contains the CusomerID and a Portal that links to the inventoryOut Table. I can easily see all the orders for a certain period and customer, but I do not want to see each individual order, just the Quanity totals (As per the above).
I tried to set a find request that use a repeating field, in each field I can put the SKU that I want to see and the portal will show me all the records for the SKUs that are listed in the find's repeating fields. However I can not seem to just develop a way to summarizes all the information that I want in the above format.
any help is appreciated