Create join 'between dates'

[Andy]

Each row in table A has a dd/mm/yyyy date in it. A reference table has a
'Date From', 'Date To' and 'Week Reference' field.

I need to create a table from table A, but generate the 'Week Reference'
field within my table. I therefore need to somehow link the Table A date to
these from and to dates, but am not sure how.

 

[John Spencer MVP]

It can be done but you need to use the SQL view to do so.

SELECT TableA.*, TableB.[Week Reference]
FROM TableA INNER JOIN TableB
ON (TableA.DateField >= TableB.[Date From]
AND TableA.DateField<= TableB.[Date To])

An alternative is to add both tables to your query
-- DO NOT join the two tables
-- Use criteria under tableA.Datefield that is
Between [TableB].[Date From] and [TableB].[Date To]

Using the second alternative is good AS LONG AS you don't need to update the
query results and the tables are fairly small. The non-join will mean that
every record in tableA will be matched to every record in tableB. So 100
records in TableB and 10,000 records in TableA means that 1,000,000 rows will
be processed. Change that to 200 and 20,000 records and you end up with
4,000,000 rows to process.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Try a query like:

SELECT [UnnamedDateField], [Week Reference]
FROM [A], [Reference]
WHERE [UnnamedDateField] Between [Date From] And [Date To];

 

[Andy]

Table A will have circa a million records by year-end so probably not a good
way to do it. I've just created a 365 day reference look-up file instead.
Thanks anyway

It can be done but you need to use the SQL view to do so.

SELECT TableA.*, TableB.[Week Reference]
FROM TableA INNER JOIN TableB
ON (TableA.DateField >= TableB.[Date From]
AND TableA.DateField<= TableB.[Date To])

An alternative is to add both tables to your query
-- DO NOT join the two tables
-- Use criteria under tableA.Datefield that is
Between [TableB].[Date From] and [TableB].[Date To]

Using the second alternative is good AS LONG AS you don't need to update the
query results and the tables are fairly small. The non-join will mean that
every record in tableA will be matched to every record in tableB. So 100
records in TableB and 10,000 records in TableA means that 1,000,000 rows will
be processed. Change that to 200 and 20,000 records and you end up with
4,000,000 rows to process.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Each row in table A has a dd/mm/yyyy date in it. A reference table has a
'Date From', 'Date To' and 'Week Reference' field.

I need to create a table from table A, but generate the 'Week Reference'
field within my table. I therefore need to somehow link the Table A date to
these from and to dates, but am not sure how.

 

[Noëlla Gabriël]

Hi,

isn't it simpler to create a query based on table A and create a calculated
field that gets the week reference with a dlookup function?

 

[John Spencer MVP]

That is one way to solve the problem. The first method I proposed should be a
better method assuming the date fields involved are indexed. For one thing it
will not require an additional table that will need to be maintained.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Table A will have circa a million records by year-end so probably not a good
way to do it. I've just created a 365 day reference look-up file instead.
Thanks anyway

It can be done but you need to use the SQL view to do so.

SELECT TableA.*, TableB.[Week Reference]
FROM TableA INNER JOIN TableB
ON (TableA.DateField >= TableB.[Date From]
AND TableA.DateField<= TableB.[Date To])

An alternative is to add both tables to your query
-- DO NOT join the two tables
-- Use criteria under tableA.Datefield that is
Between [TableB].[Date From] and [TableB].[Date To]

Using the second alternative is good AS LONG AS you don't need to update the
query results and the tables are fairly small. The non-join will mean that
every record in tableA will be matched to every record in tableB. So 100
records in TableB and 10,000 records in TableA means that 1,000,000 rows will
be processed. Change that to 200 and 20,000 records and you end up with
4,000,000 rows to process.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Each row in table A has a dd/mm/yyyy date in it. A reference table has a
'Date From', 'Date To' and 'Week Reference' field.

I need to create a table from table A, but generate the 'Week Reference'
field within my table. I therefore need to somehow link the Table A date to
these from and to dates, but am not sure how.

 

[John Spencer MVP]

Perhaps simpler, but with a lot a records that could be very, very slow. With
DLookup you would be basically running one query for each row in the table
that you were returning. With a million rows that would be a LOT of queries
to run.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County