Okay, All tables and relationships are setup. All data is in tables. Now to the fun part...setting up the queries to create the output in the reports.
The objective of this database is to basically have a 6-12-view of each products' purchases orders, receipts, sales (POs s/b received within the same month placed. As a result, any qty not received is a backorder and will be added to following month's Receipts). Finally, the M/E inventory from prior month + Receipts-Sales=Inventory, which is what I was going to have calculated.
I have tried to use crosstab query for each of the 3 variables that has months on top and products on side, with quantity in values-then have a report use all 3 crosstab queries as the source. . . yeah-so I learned report can only have one query source. I am stumped...please please please help me.
I have attached a snapshot of the relationships for reference, and will show below what I am trying to acheieve in the format of a report. If any MVP guru can be obliged to help someone learning from the ground up and needs a boost that would totally awesome!! (and bring you good luck)
(assuming no inventory in DEC)
REPORT OUTPUT:
JAN FEB MAR
Product A
Purch Order Qty 12 10 15
Received Qty 10 12 15
MIT/BackOrder 2
Sales 8 4 30
*calc M/E Invent. 2 20 5
Seems conceptually easy, but execution to get the row headings aligned with the months (in 1 report) is essentially what I am struggling with.
Thanks for any help in advance.