Complex Update Query?

[Dustin B]

Here is what I would like. If it isn't possible please advise.

I have a tbl with 23 various columns.
The table includes information on a point A-point B "lane" and the rates for
that "lane".
One column in the sheet is an effective date that goes with each entry.
There are four columns that define the "lane" Three "origin" columns and one
"destination" column.
The remaining columns are all rates.

I would like the query to go through and find any "lane" that has a new
effective date. Once it finds the new effective date I would like to have it
add the entry to the sheet as long as one of the rates have changed.

This would have to first look at the first 5 columns I mentioned above (
Effective, 3 Origin columns, and 1 Destination Column) compare the lanes look
for like lanes and then find the effective dates. If there are lanes that
match but an effective date that does not I would like it to update a new
column I created called Updated.

This may not make sense the way I wrote it so please ask questions if
needed. Thank you.

 

[Jeff Boyce]

I'm having trouble visualizing your data structure and the "domain" to which
this data applies. More information about either/both might help clarify
what you're trying to do.

For instance, I can't be sure, but it sounds like you have more than one
record for the same "lane" (whatever that is), each with its own "effective
date". If that's your situation, then you might be able to find any "lanes"
with duplicate records (Access' query wizards include a "duplicates"
wizard).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

Maybe you are looking for something like this ---
SELECT tblLanes.origin1, tblLanes.origin2, tblLanes.origin3,
tblLanes.destination, tblLanes.[effective date], tblLanes_1.[effective date],
tblLanes.Updated, tblLanes.Rate1, tblLanes.Rate2, tblLanes.Rate3
FROM tblLanes INNER JOIN tblLanes AS tblLanes_1 ON (tblLanes.destination =
tblLanes_1.destination) AND (tblLanes.origin3 = tblLanes_1.origin3) AND
(tblLanes.origin2 = tblLanes_1.origin2) AND (tblLanes.origin1 =
tblLanes_1.origin1)
WHERE (((tblLanes.[effective date])<[tblLanes_1].[effective date]));