Tough Query

[Perlyman]

Here is the scenario.

I have a table (table1) with 2 fields ("Order#" and "Tracking Number")

The table will have 3-4 records with the same order number but
different tracking numbers.

I have another table (table2) with 3 fields ("Order#", "1st tracking
number" and "more tracking numbers")

I need to update table 2 "1st tracking number" with only the first
record containing a matching order number from table 1 and update "more
tracking numbers" with any records left in the table with matching
order numbers.

I can accomplish the first task by using a "View totals" query and
grouping by order# and "first" under tracking number. But I cannot
figure out how to delete the only the first instance of a record so i
can run another update query and update "more tracking numbers" with
whats left in the table.

Any help greatly appreciated (If this doesn't make sense send me an
email to [email protected] and i'll try to explain it better)

Gracias,

Robert

 

[John Vinson]

Here is the scenario.

I have a table (table1) with 2 fields ("Order#" and "Tracking Number")

The table will have 3-4 records with the same order number but
different tracking numbers.

Ok... I take it that the two fields jointly constitute the Primary
Key, or that you have a unique two-field index to prevent duplicates?

I have another table (table2) with 3 fields ("Order#", "1st tracking
number" and "more tracking numbers")

Well, you shouldn't. This table contains repeated fields and violates
third normal form. This sounds more like a Report than a table!

I need to update table 2 "1st tracking number" with only the first
record containing a matching order number from table 1 and update "more
tracking numbers" with any records left in the table with matching
order numbers.

Since Access has no record numbers, and since a Table is an unordered
"bag" of data with no controllable sequence to the records, this isn't
possible even in principle (unless you have some other field
indicating the sequence of tracking numbers).

I can accomplish the first task by using a "View totals" query and
grouping by order# and "first" under tracking number.

First is actually rather deceptive. It gives you the first record IN
DISK STORAGE ORDER - but disk storage order is arbitrary and
uncontrollable. Often it will match the sequence in which the records
were entered, but often it will *not* - Access will put the new record
wherever there is room. I'd only use First() if it didn't make any
difference which record was chosen.

But I cannot
figure out how to delete the only the first instance of a record so i
can run another update query and update "more tracking numbers" with
whats left in the table.

Even given the above, putting multiple fields from multiple records
into a single field in a single record is a) redundant, b) cannot be
done in a simple query, and c) will probably require VBA code to loop
through the records and concatenate values.

Any help greatly appreciated (If this doesn't make sense send me an
email to [email protected] and i'll try to explain it better)

I just have to question why Table2 *should even exist at all*. If you
want to see the Order information and all the tracking numbers, you
can create a Report with a Subreport (or a Form with a Subform for
onscreen use). Could you explain how you intend to use Table2, and why
you should handle one tracking number differently than the rest of the
tracking numbers?