Query with date range and other criteria

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
I would create a join on productcode and supplier and then use the salesdate in the criteria, but convert it to a date format to compare with the dates from the other tables...

Code:
SELECT sales.productcode, sales.supplier, sales.salesdate, [Purchase Prices].purchaseprice, [Sales Prices].purchaseprice AS salesprice, [Sales Prices]![purchaseprice]-[Purchase Prices]![purchaseprice] AS margin
FROM (sales INNER JOIN [Purchase Prices] ON (sales.supplier = [Purchase Prices].supplier) AND (sales.productcode = [Purchase Prices].productcode)) INNER JOIN [Sales Prices] ON (sales.supplier = [Sales Prices].supplier) AND (sales.productcode = [Sales Prices].productcode)
WHERE (((CDate([sales]![salesdate]))>=[Purchase Prices]![validfromdate] And (CDate([sales]![salesdate]))<=[Purchase Prices]![validtodate] And (CDate([sales]![salesdate]))>=[Sales Prices]![validfromdate] And (CDate([sales]![salesdate]))<=[Sales Prices]![validtodate]));
 

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
Many thanks for your suggestion. I tried the code you gave but it only returned one result, I have detailed that below.

potatoes ABC 38869 25 50 25

Why would this be happening?

The SQL code I have used is:

SELECT sales.productcode, sales.supplier, sales.salesdate, [Purchase Prices].purchaseprice, [Sales Prices].salesprice, [Sales Prices]![salesprice]-[Purchase Prices]![purchaseprice] AS margin
FROM (sales INNER JOIN [Purchase Prices] ON (sales.productcode = [Purchase Prices].productcode) AND (sales.supplier = [Purchase Prices].supplier)) INNER JOIN [Sales Prices] ON (sales.productcode = [Sales Prices].productcode) AND (sales.supplier = [Sales Prices].supplier)
WHERE (((CDate([sales]![salesdate]))>=[Purchase Prices]![validfromdate] And (CDate([sales]![salesdate]))<=[Purchase Prices]![validtodate] And (CDate([sales]![salesdate]))>=[Sales Prices]![validfromdate] And (CDate([sales]![salesdate]))<=[Sales Prices]![validtodate]));
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
My query has a criteria of...

[Purchase Prices]![validfromdate] <= [sales]![salesdate] <= [Purchase Prices]![validtodate]
AND
[Sales Prices]![validfromdate] <= [sales]![salesdate] <= [Sales Prices]![validtodate]

This limits the results to records with the dates within a given range. Did you want to limit the query to dates within a range?
 

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
Not really.

My objective is that I list all from my sales table, and it brings across where it can, the sales and purchases price by checking and matching correct date range as well as supplier info.

Where it cannot do that, it would still list sale but without that information.

Sorry, I maybe wasn't clear about that before.
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top