Is the date between 2 dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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]...
 
Thanks if anyone scratched their heads on this one but I have since found the
answer.

Alastair
 
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;
 
Back
Top