How to run query to find address within date range - repost

[Jason]

My previously posted question had to do with a query that would look in two
tables and pull the correct address for a customer depending on the dates of
the secondary address table:
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip

I had a response from Beetle that partially solved the issue for me:
----------
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;
----------

The problem now is that for each customer ID in the Main table, there may
exist the same customer with multiple addresses (the IDs in both tables
match). How can I modify the script so it returns ONLY the address meeting
the date criteria?

See below for full thread:
-------------
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].

 

[mark]

My previously posted question had to do with a query that would look in two
tables and pull the correct address for a customer depending on the datesof
the secondary address table:
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip

I had a response from Beetle that partially solved the issue for me:
----------
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;
----------

The problem now is that for each customer ID in the Main table, there may
exist the same customer with multiple addresses (the IDs in both tables
match). How can I modify the script so it returns ONLY the address meeting
the date criteria?

See below for full thread:
-------------
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.

 

[Beetle]

Sorry, I didn't see your last reply in the previous thread. Did you try
the solution that John Spencer suggested in that thread? It may
work better for you than what I suggested.
--
_________

Sean Bailey

My previously posted question had to do with a query that would look in two
tables and pull the correct address for a customer depending on the dates of
the secondary address table:
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip

I had a response from Beetle that partially solved the issue for me:
----------
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;
----------

The problem now is that for each customer ID in the Main table, there may
exist the same customer with multiple addresses (the IDs in both tables
match). How can I modify the script so it returns ONLY the address meeting
the date criteria?

See below for full thread:
-------------
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.

 

[mark]

My previously posted question had to do with a query that would look in two
tables and pull the correct address for a customer depending on the datesof
the secondary address table:
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip

I had a response from Beetle that partially solved the issue for me:
----------
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;
----------

The problem now is that for each customer ID in the Main table, there may
exist the same customer with multiple addresses (the IDs in both tables
match). How can I modify the script so it returns ONLY the address meeting
the date criteria?

See below for full thread:
-------------
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.

You could add an additional column expression,
that uses an IIF statement to determine if the given date is between
the date range for that record.
If it is then return TRUE, else return FALSE... then only show records
that are TRUE for this criteria..

For example, you could add this to the where clause:

WHERE (((IIf([enter_date] Between [Begin_Address_Date] And
[Expire_Address_Date],"TRUE","FALSE"))="TRUE"))

You might need to construct a nested IF statement to handle various
scenarios. For example, what if the customer doesn't
have a related record in the secondary table? Or what if the date
isn't valid, or it doesn't fall within any date range, etc.

Mark

 

[Paul Shapiro]

Something like this should work.

Save this as query as SecondaryAddressEffectiveToday (omit the dates from
the output so we can later use a Union with the main table):
Select A2.CustID, A2.Address, A2.City, A2.ST, A2.zip
From [Secondary Address Table] As A2
Where Date Between A2.Begin_Address_Date And A2.Expire_Address_Date

Now you want the main addresses for which no special address is effective
today, saved as MainAddressEffectiveToday:
Select A.*
From [Main address Table] As A
Where Not Exists (
Select * From SecondaryAddressEffectiveToday As A2 Wher
A2.CustID=A.CustID
)

And finally you can combine the two sets (not sure about Access' union
syntax, so you might need to check the syntax here):
Select * From MainAddressEffectiveToday
Union All
Select * From SecondaryAddressEffectiveToday

My previously posted question had to do with a query that would look in
two tables and pull the correct address for a customer depending on the
dates of the secondary address table:
Main address Table:
CustID
Address
City
ST
Zip

Secondary Address Table;
CustID
Begin_Address_Date
Expire_Address_Date
Address
City
ST
Zip

I had a response from Beetle that partially solved the issue for me:
----------
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;
----------

The problem now is that for each customer ID in the Main table, there may
exist the same customer with multiple addresses (the IDs in both tables
match). How can I modify the script so it returns ONLY the address meeting
the date criteria?

See below for full thread:
-------------
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.