A
AnExpertNovice
The current method works but I'm looking for a better and perhaps faster
method of doing it. The sections are:
********** [The two tables] **********
********** [The catch] **********
********** [test data] ********** (if you can help, this will make it
easier for you to do so)
********** [comment] **********
********** [the queries] ********** (contains my queries, may be of
interest to others who have the same situation)
********** [The two tables] **********
There are two tables involved and both tables contain data that come from
two different mainframe files.
tblA is a data entry table and has no unique key. The pertinent fields are
Loc (long)
Event (date)
Operation (text, the operation type of the location on this event date)
tblB is the Operation Type table. The necessary fields are: (These 4
fields, in this order, represent the PrimaryKey.)
Loc (long)
BegDate (date)
EndDate (date)
Operation (text)
********** [The catch] **********
Ok, I know what you are thinking. this is easy. Hopefully, you are correct!
The catch is that BegDate and EndDate can overlap from one record to
another. See a a simple example in the test data below.
In some situations the event date is to be matched to the first record of
the store for that day (A) and in other cases the last event type (C) needs
to be matched.
********** [test data] **********
tblA has the following data: (the third field can be any value as it is to
be updated)
Loc Event Operation
1 2/1/2003 A
1 6/1/2003 A
1 7/1/2003 C
2 2/1/2003 A
2 6/1/2003 A
2 7/1/2003 A
2 8/1/2003 B
2 9/1/2003 C
tblB has the following data: (12/31/9999 = no-ending-date)
Loc BegDate EndDate Operation
1 1/1/2003 6/1/2003 A
1 6/1/2003 6/1/2003 B
1 6/1/2003 12/31/9999 C
2 1/1/2003 7/1/2003 A
2 6/1/2003 8/1/2003 B
2 7/1/2003 12/31/9999 C
********** [comment] **********
I'm trying to do this with queries but if there is a reason to use code that
would be no problem. The second query will eventually become an append
query and will append the results to a work table. A work table is needed
because qry2 is not updateable so it renders tblA non-updateable from a
query
The work table (and Qry2) has the following fields: (BegDate and EndDate
are not necessary, but helpful in debugging.)
Loc
EventDate
BegDate
EndDate
Operation
The tables are small (tblA has 224,000 records and tblB has 7,900 records)
so it only takes 7 seconds to return 71,000 joins.
********** [the queries] **********
[Qry1]
SELECT tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate,
First(tblB.Operation) AS Operation
FROM tblA INNER JOIN tblB ON tblA.Loc = tblB.Loc
GROUP BY tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate
HAVING (((tblB.BegDate)<=[tblA]![Event]) AND
((tblB.EndDate)>=[tblA]![Event]))
ORDER BY tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate;
[Qry2]
SELECT Qry1.Loc, Qry1.Event, First(Qry1.BegDate) AS BegDate,
First(Qry1.EndDate) AS EndDate, First(Qry1.Operation) AS Operation
FROM Qry1
GROUP BY Qry1.Loc, Qry1.Event;
Any suggestions?
method of doing it. The sections are:
********** [The two tables] **********
********** [The catch] **********
********** [test data] ********** (if you can help, this will make it
easier for you to do so)
********** [comment] **********
********** [the queries] ********** (contains my queries, may be of
interest to others who have the same situation)
********** [The two tables] **********
There are two tables involved and both tables contain data that come from
two different mainframe files.
tblA is a data entry table and has no unique key. The pertinent fields are
Loc (long)
Event (date)
Operation (text, the operation type of the location on this event date)
tblB is the Operation Type table. The necessary fields are: (These 4
fields, in this order, represent the PrimaryKey.)
Loc (long)
BegDate (date)
EndDate (date)
Operation (text)
********** [The catch] **********
Ok, I know what you are thinking. this is easy. Hopefully, you are correct!
The catch is that BegDate and EndDate can overlap from one record to
another. See a a simple example in the test data below.
In some situations the event date is to be matched to the first record of
the store for that day (A) and in other cases the last event type (C) needs
to be matched.
********** [test data] **********
tblA has the following data: (the third field can be any value as it is to
be updated)
Loc Event Operation
1 2/1/2003 A
1 6/1/2003 A
1 7/1/2003 C
2 2/1/2003 A
2 6/1/2003 A
2 7/1/2003 A
2 8/1/2003 B
2 9/1/2003 C
tblB has the following data: (12/31/9999 = no-ending-date)
Loc BegDate EndDate Operation
1 1/1/2003 6/1/2003 A
1 6/1/2003 6/1/2003 B
1 6/1/2003 12/31/9999 C
2 1/1/2003 7/1/2003 A
2 6/1/2003 8/1/2003 B
2 7/1/2003 12/31/9999 C
********** [comment] **********
I'm trying to do this with queries but if there is a reason to use code that
would be no problem. The second query will eventually become an append
query and will append the results to a work table. A work table is needed
because qry2 is not updateable so it renders tblA non-updateable from a
query
The work table (and Qry2) has the following fields: (BegDate and EndDate
are not necessary, but helpful in debugging.)
Loc
EventDate
BegDate
EndDate
Operation
The tables are small (tblA has 224,000 records and tblB has 7,900 records)
so it only takes 7 seconds to return 71,000 joins.
********** [the queries] **********
[Qry1]
SELECT tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate,
First(tblB.Operation) AS Operation
FROM tblA INNER JOIN tblB ON tblA.Loc = tblB.Loc
GROUP BY tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate
HAVING (((tblB.BegDate)<=[tblA]![Event]) AND
((tblB.EndDate)>=[tblA]![Event]))
ORDER BY tblA.Loc, tblA.Event, tblB.BegDate, tblB.EndDate;
[Qry2]
SELECT Qry1.Loc, Qry1.Event, First(Qry1.BegDate) AS BegDate,
First(Qry1.EndDate) AS EndDate, First(Qry1.Operation) AS Operation
FROM Qry1
GROUP BY Qry1.Loc, Qry1.Event;
Any suggestions?