if date between range, lookup value in other table

  • Thread starter Thread starter michael
  • Start date Start date
M

michael

Hi. Any help with this would be great! I have a query,
qryShipments, that shows shipments of product like so:

Territory SAPNumber InvoiceDate Amount

Sometimes accounts move from one Territory to another.
When that happens, my company's SAP database changes the
entire historical record so that the accounts' territory
change from the old territory to the new one. This makes
it hard for me to make accurate historical reports. What
I'd like to do is keep a table, tblAccountTransfers, that
lists all account transfers like so:

SAPNumber
TerritoryOld
TerritoryNew
DateBeganInOldTerritory
DateEndedInOldTerritory

Then I'd make an SQL query that says if the SAPNumber in
qryShipments is the same as in tblAccountTransfers, and
the InvoiceDate is between DateBeganInOldTerritory and
DateEndedInOldTerritory, then use TerritoryOld from
tblAccountTransfers. Otherwise, use Territory from
tblShipments. Any thoughts on how I can do this?
 
Dear Michael:

I think I can follow what you have. I would suggest you use the data
you have in the following manner:

- use the information in tblAccountTransfers as a list of territories
for each SAPNumber over ranges of dates. Frankly, I'd only use the
columns SAPNumber, TerritoryOld, and DateEnded. The rest is
superfluous. Let me show you:

SAPNumber Territory DateEnded
1 TerritoryA 1/1/01
1 TerritoryB 3/20/01
1 TerritoryC 7/14/02
1 TerritoryD NULL

The above shows that SAP #1 began in TerritoryA through January 1,
2001. Then it was in TerritoryB from January 2, 2001 through March
20, 2001 and TerritoryC from March 21, 2001 till July 14, 2002. Since
then it has been in TerritoryD. There is no need to record and ending
date. That is redundant data. If you create a column for ending
date, this just begs users to enter mistaken data and have gaps
between date ranges or overlapping date ranges.

There is no need to record TerritoryNew either. It is perfectly clear
from the above.

The one thing in the above you may be missing is the last row. You
can probably generate this from some table that tells the current
Territory of all SAPs. I'm thinking a union query of the selected
data from tblAccountTransfers with the information of the SAP's
current Territory would suffice.

Once this query is constructed and saved, you can move on from there.
You can obtain the Territory from the above using a correlated
subquery using the InvoiceDate from each Shipment. Probably the whole
thing would go like this:

SELECT SAPNumber, TerritoryOld AS Territory,
DateEndedInOldTerritory AS DateEnded
FROM tblTransfers
UNION ALL
SELECT SAPNumber, Territory, NULL AS DateEnded
FROM SAPMaster

Save this as qrySapTerritory.

Next, the main query (the way I'd prefer to write it).

SELECT
(SELECT Territory FROM qrySapTerritory qST
WHERE qST.SAPNumber = T.SAPNumber
AND Nz(qST.DateEnded, #12/31/2999#) =
(SELECT MIN(Nz(DateEnded, #12/31/2999#))
FROM qrySapTerritory qST1
WHERE qST1.SAPNumber = qST.SAPNumber
AND qST1.DateEnded >= qS.DateEnded)) AS Territory,
SAPNumber, InvoiceDate, Amount
FROM qryShipments

Now, if I've got this correct, it will find the location using the
earliest DateEnded that is on or after the invoice date.

One thing this relies on is that you won't have two rows in
tblTransfers for the same SAPNumber with the same ending date. Also,
it ignores your Beginning dates, assuming that any InvoiceDate before
the earliest EndingDate is in the earliest Territory, and that each
BeginDate after that is always the day after the previous EndingDate
(I pretty much explained that before, I hope!)

There's a difficulty with doing this. There are two levels of
correlated subquery, and the inner level makes a reference all the way
out to the main query, skipping the first level. If you're using Jet,
this is usually not permitted. If you're using MSDE it would be fine.

I've coded this for Jet, however, because that's probably what you're
using. In order to use MSDE, replace the #'s with single quotes for
it to function.

If necessary, we can create yet another intermediate level query
between the qrySapTerritory and the finished query, giving the range
of dates for each so you can just use a simple BETWEEN. But please
try this first. I think sometimes it works in Jet, although I don't
really know why or when it will. (I sure wish I did).

This may get moderately hairy. I may yet ask you to ship me a sample
database, zipped to under 1MB and I'll get it working myself.

Next time, ask an easier question, OK? One without so many
uncertainties.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom, wow, great suggestion. Thanks for taking time to
write all that down! I'll take a stab at this today and
see if I can get it to work. I have it working using the
technique I was originally using but your way seems
better. I'll take a look and see if I can get it going.
Thanks!!
 
Back
Top