Is the date between 2 dates?

[Guest]

Dear All,

I have a question that is annoying me:

If I have 2 tables TableA (Financial Periods) and TableB (Sales). What I
want to do is to return the Period for a given sale date without using a
Dlookup since I have about 1 million records.

The table structures are as follows:

TableA
Period (integer): 1 (values from 1 to 13)
StartDate: 01/01/07
EndDate: 28/01/07

TableB
SaleDate: 5/1/07
SaleAmount: £100

What I want is to create a query that says that this SaleDate is for
Period1, ie

SaleDate:5/1/07
Period: 1
SaleAmount: £100

I am unsure of the best way to structure this query without using a DLookup
which on a large number of records would be very slow.

Thanks again and I hope you can understand what I am trying to achieve.

Alastair MacFarlane

 

[Allen Browne]

Try:

SELECT TableA.*, TableB.*
FROM TableA, TableB
WHERE TableB.SaleDate Between TableA.StartDate And TableA.EndDate

More ideas on how you could set this up:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[Guest]

Thanks if anyone scratched their heads on this one but I have since found the
answer.

Alastair

 

[Jason Lepack]

You won't be able to view this query in design view, but it will work.

SELECT TableB.SaleDate,
TableA.Period,
TableB.SaleAmount
FROM TableA, TableB
WHERE TableB.SaleDate
BETWEEN TableA.StartDate AND TableA.EndDate;