How to run query to find address within date range

[Jason]

I have tables with address information in it (I cannot edit the structure of
these tables).
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip


I would like to figure out how to run a query that would look in both
tables. If today falls within the date range of the second table, we'd like
to pull that address. Otherwise, just pull the address from Main Address
Table.

Seems simple in logic, but can't figure it out.

THanks.

 

[Beetle]

Would be slightly easier if you can at least rename the fields in the
secondary table to Address2, City2, etc. and use a query like;

SELECT tblCustomer.FirstName,
IIf([BeginDate]<=Date() And [ExpireDate]>=Date(),[Address2],[Address])
AS CurrentAddress
FROM (tblCustomer INNER JOIN tblMainAddress ON tblCust.CustID =
tblMainAddress.CustID) INNER JOIN tblSecondAddress ON tblCust.CustID =
tblSecondAddress.CustID;

You'll need to add additional IIf statements for City, ST & Zip (or you could
concantenate them in a single IIf statement I suppose).

If you can't change the field names you can still do the above, but you'll
need
to reference [TableName].[FieldName].

 

[John Spencer]

Do you have the same set of custId in both tables or are there times
when you have a custid in the main table that is not in the secondary table?

First Query: saved as qAddrOnDate
SELECT *
FROM [Secondary Address]
WHERE Begin_Address_Date <= Date()
AND Expire_Address_Date >= Date()

Second Query:
SELECT A.CustId
, IIF(Q.CustID is Null, A.Address,Q.Address) as Addr
, IIF(Q.CustId is Null, A.City,Q.City) as TheCity
, IIF(Q.CustID is Null, A.St,Q.St) as State
, IIF(Q.CustId is Null, A.Zip,Q.Zip) as ZipCode
FROM [Main Address] as A LEFT JOIN qAddrOnDate as Q
ON A.CustID = Q.CustID

Of course if your table names don't contain spaces, you could do this in
one query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================