I'm stuck writing a query and could really do with some pointers. I'm using ACCESS 2000.
I have a table, named sales, that contains sales of products by date range (in serial number format), like so:
id, productcode, supplier, salesdate, purchasedate
1, potatoes, ABC, 38869, 38866
2, tomatoes, XYZ, 38899, 38869
3, potatoes, DEF, 38901, 38825
etc etc
I have another table that contains the purchase prices and a third table containg sales prices, like so:
Purchase Prices
id, productcode, supplier, validfromdate, validtodate, purchaseprice
1, potatoes, ABC, 01/06/2006, 30/06/2006, 25
2, tomatoes, DEF, 01/06/2006, 30/06/2006, 35
3, potatoes, ABC, 01/07/2006, 31/07/2006, 27
etc etc
Sales Prices
id, productcode, supplier, validfromdate, validtodate, purchaseprice
1, potatoes, ABC, 01/06/2006, 30/06/2006, 50
2, tomatoes, DEF, 01/06/2006, 30/06/2006, 35
3, potatoes, ABC, 01/07/2006, 31/07/2006, 54
etc etc
I'm trying to build a query that will take info from Sales table and bring across from the purchases and sales table relevant info so that I can create a margin/profit report. The query result would look something like this:
productcode, supplier, salesdate, purchaseprice, salesprice, margin: salesprice-purchaseprice (calculated field)
potatoes, ABC, 38869, 25, 50, 50-25
Any helpful pointers would be greatly appreciated.
I have a table, named sales, that contains sales of products by date range (in serial number format), like so:
id, productcode, supplier, salesdate, purchasedate
1, potatoes, ABC, 38869, 38866
2, tomatoes, XYZ, 38899, 38869
3, potatoes, DEF, 38901, 38825
etc etc
I have another table that contains the purchase prices and a third table containg sales prices, like so:
Purchase Prices
id, productcode, supplier, validfromdate, validtodate, purchaseprice
1, potatoes, ABC, 01/06/2006, 30/06/2006, 25
2, tomatoes, DEF, 01/06/2006, 30/06/2006, 35
3, potatoes, ABC, 01/07/2006, 31/07/2006, 27
etc etc
Sales Prices
id, productcode, supplier, validfromdate, validtodate, purchaseprice
1, potatoes, ABC, 01/06/2006, 30/06/2006, 50
2, tomatoes, DEF, 01/06/2006, 30/06/2006, 35
3, potatoes, ABC, 01/07/2006, 31/07/2006, 54
etc etc
I'm trying to build a query that will take info from Sales table and bring across from the purchases and sales table relevant info so that I can create a margin/profit report. The query result would look something like this:
productcode, supplier, salesdate, purchaseprice, salesprice, margin: salesprice-purchaseprice (calculated field)
potatoes, ABC, 38869, 25, 50, 50-25
Any helpful pointers would be greatly appreciated.