Create join where one value is not exact to other

[Steve D]

Hi,
I have 2 tables one contains the following fields "Month", "Start Date",
"End Date" the second contains data with week ending dates. Each month should
contain 4 or 5 week ending dates. Is there a way to create a query that
queries data based on a month. For example I want January to be my criteria
and January is valid in the "Month" field of the first table and I want it to
bring back any data in the data table where the week ending date is between
the "start date" and "end date" in the first table. Is this possible? And if
so how do I do it? I do not see a way to join the tables.

 

[Roger Carlson]

You can also "Join" tables by creating a cartesean product in your From
clause and restricting the records in your Where clause. Something like
this:

Select table1.*, table2.*
From table1, table2
Where table1.Month = "January" and table2.EndingDate between
table1.StartDate and table1.EndDate

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John W. Vinson]

Hi,
I have 2 tables one contains the following fields "Month", "Start Date",
"End Date" the second contains data with week ending dates. Each month should
contain 4 or 5 week ending dates. Is there a way to create a query that
queries data based on a month. For example I want January to be my criteria
and January is valid in the "Month" field of the first table and I want it to
bring back any data in the data table where the week ending date is between
the "start date" and "end date" in the first table. Is this possible? And if
so how do I do it? I do not see a way to join the tables.

That's an awkward situation obviously - you may not be able to create an
updateable join in any case. But in addition to Roger's cartesian join
suggestion, you can actually do a "non equi join" in the Join clause. You do
need to go into the SQL view. The SQL might be something like

SELECT <whatever fields you want>
FROM [table1] INNER JOIN [table2]
ON [table1].[start date] <= [table2].[weekenddate]
AND [table1].[end date] >= [table2].[weekenddate]

HOWEVER - several questions! First off, Month is a reserved word and a bad
choice of fieldname; more importantly, given the availability of a rich set of
builtin date/time functions, I don't see any benefit to having this table AT
ALL. Is the first table just to provide convenient search criteria? If so
there are better ways! Please explain the context; you may be doing this the
hard way.