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
'====================================================

 

[Jason]

Thanks Beetle, this worked well for me.
The only problem I'm having now is that one customer may have two or more
secondary addresses, so with this query, more than one address will appear
in the query.
I tried to do a Totals query and selected one of the fields as First, but
that didn't solve the problem.
The problem seems to be that the query will check every address (even if the
customer has two, four or five) and run the IIf statement against all of
them. How can I limit it to the address within today's date?
I added this line:
WHERE DATE() >=EFFECTIVE_DATE AND DATE()<=EXPIRE_DATE
But I'm not sure that makes sense. Seems to me like this line would exclude
customers because they don't have a secondary address to verify against.
Still stumped...

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].

--
_________

Sean Bailey

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.