join on data ranges

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi there. Any help with this would be fantastic. I have a
problem and I'm almost there, but I could use some help
from an ace on the final part.

I have a table, tblShipped, that's full of invoices that I
get from SAP. It looks like this.

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Dallas 300 0000055555
01/01/04 Dallas 100 0000055555


The problem is that accounts sometimes transfer from one
territory to another. When that happens, the whole
historical record changes to the new territory. For
example, if Customer 0000055555 was reassigned to Houston,
then SALES_TERR would all change from Dallas to Houston
regardless of the date which the transfer took place.

Starting in 2004, I'm trying to come up with a way to
preserve the historical integrity of the data by retaining
the old territory; I don't have to worry about data prior
to 01/01/04. To do this, I've started keeping track of
transfers in a table, tblAccountTransfersArchive. This
table looks as follows:

tblAccountTransfersArchive:

SAPNumber Territory EffectiveDate
0000055555 Houston 01/01/04
0000055555 Dallas 01/10/04

It's a pretty simple table. SAPNumber is the account
number, Territory is the NEW territory and EffectiveDate
is the date on which the transfer takes place. What I'd
like to have happen is for all sales at account 0000055555
that took place greater than or equal to 01/01/04 and less
than 01/10/04 to be attributed to Houston, like this:

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Houston 300 0000055555
01/01/04 Houston 100 0000055555

Because tblShipped is a linked dbf file, I'm first using
vb to make a copy everyday and then update the records.
There's a couple million records but it only takes about
30 seconds to copy it. I don't think it will take long to
run the update everyday, but I'm not sure yet.

The update query, qryShippedAccountTransferUpdate, looks
like this:

UPDATE tblShipped INNER JOIN (tblAccountTransfersArchive
INNER JOIN tblTerritories ON
tblAccountTransfersArchive.Territory=tblTerritories.Territo
ry) ON
(tblShipped.INVOICE_DA>=tblAccountTransfersArchive.Effectiv
eDate) AND
(tblShipped.CUSTOMER__=tblAccountTransfersArchive.SAPNumber
) SET tblShipped.SALES_TERR = tblTerritories.[SAP
Territory Name];

The query is working great except for one thing and, after
all of this writing (thanks for continuing to read!!),
THIS is what I'm having trouble solving...

....the greater than or equal to part of the query that
reads "(tblShipped.INVOICE_DA>=tblAccountTransfersArchive.E
ffectiveDate)" is too simple. It needs to be able to
update tblShipped.SALES_TERR for those accounts where the
account number in tblShipped matches an account number in
tblAccountTransfersArchive and (this is where I'm stuck)
where the invoice dates, tblShipped.INVOICE_DA, are
greater than or equal to the closest effective date before
it and less than the closest effective date after it.
Whew!!

I think I need to use a subQuery but I'm not really
familiar with how to do that. Any thoughts? If you've read
this far, THANKS AGAIN!!!
 
The most simple thing 2 do is add a field to capture the
old sales territory...and go from there or if historical
data is bein kept u can look up the sales territory from
there based on the customer ID....

-----Original Message-----
Hi there. Any help with this would be fantastic. I have a
problem and I'm almost there, but I could use some help
from an ace on the final part.

I have a table, tblShipped, that's full of invoices that I
get from SAP. It looks like this.

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Dallas 300 0000055555
01/01/04 Dallas 100 0000055555


The problem is that accounts sometimes transfer from one
territory to another. When that happens, the whole
historical record changes to the new territory. For
example, if Customer 0000055555 was reassigned to Houston,
then SALES_TERR would all change from Dallas to Houston
regardless of the date which the transfer took place.

Starting in 2004, I'm trying to come up with a way to
preserve the historical integrity of the data by retaining
the old territory; I don't have to worry about data prior
to 01/01/04. To do this, I've started keeping track of
transfers in a table, tblAccountTransfersArchive. This
table looks as follows:

tblAccountTransfersArchive:

SAPNumber Territory EffectiveDate
0000055555 Houston 01/01/04
0000055555 Dallas 01/10/04

It's a pretty simple table. SAPNumber is the account
number, Territory is the NEW territory and EffectiveDate
is the date on which the transfer takes place. What I'd
like to have happen is for all sales at account 0000055555
that took place greater than or equal to 01/01/04 and less
than 01/10/04 to be attributed to Houston, like this:

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Houston 300 0000055555
01/01/04 Houston 100 0000055555

Because tblShipped is a linked dbf file, I'm first using
vb to make a copy everyday and then update the records.
There's a couple million records but it only takes about
30 seconds to copy it. I don't think it will take long to
run the update everyday, but I'm not sure yet.

The update query, qryShippedAccountTransferUpdate, looks
like this:

UPDATE tblShipped INNER JOIN (tblAccountTransfersArchive
INNER JOIN tblTerritories ON
tblAccountTransfersArchive.Territory=tblTerritories.Territ o
ry) ON
(tblShipped.INVOICE_DA>=tblAccountTransfersArchive.Effectiv
eDate) AND
(tblShipped.CUSTOMER__=tblAccountTransfersArchive.SAPNumber
) SET tblShipped.SALES_TERR = tblTerritories.[SAP
Territory Name];

The query is working great except for one thing and, after
all of this writing (thanks for continuing to read!!),
THIS is what I'm having trouble solving...

....the greater than or equal to part of the query that
reads "(tblShipped.INVOICE_DA>=tblAccountTransfersArchive. E
ffectiveDate)" is too simple. It needs to be able to
update tblShipped.SALES_TERR for those accounts where the
account number in tblShipped matches an account number in
tblAccountTransfersArchive and (this is where I'm stuck)
where the invoice dates, tblShipped.INVOICE_DA, are
greater than or equal to the closest effective date before
it and less than the closest effective date after it.
Whew!!

I think I need to use a subQuery but I'm not really
familiar with how to do that. Any thoughts? If you've read
this far, THANKS AGAIN!!!
.
Click to expand...
 
That won't work because it would mean having the SAP
administrators load a new field into their system and then
have someone in another division keep track of it which
will never happen. I receive all of my data from an SAP
feed, so I have to keep track of where the accounts move
in and out of.
-----Original Message-----
The most simple thing 2 do is add a field to capture the
old sales territory...and go from there or if historical
data is bein kept u can look up the sales territory from
there based on the customer ID....

-----Original Message-----
Hi there. Any help with this would be fantastic. I have a
problem and I'm almost there, but I could use some help
from an ace on the final part.

I have a table, tblShipped, that's full of invoices that I
get from SAP. It looks like this.

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Dallas 300 0000055555
01/01/04 Dallas 100 0000055555


The problem is that accounts sometimes transfer from one
territory to another. When that happens, the whole
historical record changes to the new territory. For
example, if Customer 0000055555 was reassigned to Houston,
then SALES_TERR would all change from Dallas to Houston
regardless of the date which the transfer took place.

Starting in 2004, I'm trying to come up with a way to
preserve the historical integrity of the data by retaining
the old territory; I don't have to worry about data prior
to 01/01/04. To do this, I've started keeping track of
transfers in a table, tblAccountTransfersArchive. This
table looks as follows:

tblAccountTransfersArchive:

SAPNumber Territory EffectiveDate
0000055555 Houston 01/01/04
0000055555 Dallas 01/10/04

It's a pretty simple table. SAPNumber is the account
number, Territory is the NEW territory and EffectiveDate
is the date on which the transfer takes place. What I'd
like to have happen is for all sales at account 0000055555
that took place greater than or equal to 01/01/04 and less
than 01/10/04 to be attributed to Houston, like this:

tblShipped:

INVOICE_DA SALES_TERR SALES__ CUSTOMER__
01/15/04 Dallas 300 0000055555
01/14/04 Dallas 300 0000055555
01/12/04 Dallas 200 0000055555
01/04/04 Houston 300 0000055555
01/01/04 Houston 100 0000055555

Because tblShipped is a linked dbf file, I'm first using
vb to make a copy everyday and then update the records.
There's a couple million records but it only takes about
30 seconds to copy it. I don't think it will take long to
run the update everyday, but I'm not sure yet.

The update query, qryShippedAccountTransferUpdate, looks
like this:

UPDATE tblShipped INNER JOIN (tblAccountTransfersArchive
INNER JOIN tblTerritories ON
tblAccountTransfersArchive.Territory=tblTerritories.Terri
Click to expand...
t
o
ry) ON
Click to expand...
(tblShipped.INVOICE_DA>=tblAccountTransfersArchive.Effectiv
eDate) AND
Click to expand...
(tblShipped.CUSTOMER__=tblAccountTransfersArchive.SAPNumber
) SET tblShipped.SALES_TERR = tblTerritories.[SAP
Territory Name];

The query is working great except for one thing and, after
all of this writing (thanks for continuing to read!!),
THIS is what I'm having trouble solving...

....the greater than or equal to part of the query that
reads "(tblShipped.INVOICE_DA>=tblAccountTransfersArchive
Click to expand...
..
E
ffectiveDate)" is too simple. It needs to be able to
update tblShipped.SALES_TERR for those accounts where the
account number in tblShipped matches an account number in
tblAccountTransfersArchive and (this is where I'm stuck)
where the invoice dates, tblShipped.INVOICE_DA, are
greater than or equal to the closest effective date before
it and less than the closest effective date after it.
Whew!!

I think I need to use a subQuery but I'm not really
familiar with how to do that. Any thoughts? If you've read
this far, THANKS AGAIN!!!
.
Click to expand...
.
Click to expand...
 
Back
Top