My Final Query/Script- Please Help with Moving field data between tables

[SyddyS]

Hi all, please help ASAP!

Well, it's been a long and bumpy ride, but my little Access DB is finally graduating and moving out of the house. My homebrewed contact and sales database is being taken over by the big leagues and converted by a pro company to a web-based SQL database- well outside my realm of abilities.

I have one more duty to perform, then I'm out of the DB business for the forseeable future.

Heres the basic structure boiled down:

Table 1: Field A, Field B, Field C
Table 2 (1-Many on "Field C"): Field C, Field D, Field E, Field F

I need to move Fields E and F from Table 2 to Table 1. The problem is that there are many entries for D, E, F for Each C in Table 1. What I need is a query or script that will take the FIRST (earliest) record of each Field C group and move the data from E and F in that record to the new fields in Table 1.

I have spent hours playing with Totals functions in queries, and just can't seem to get the right information displayed to make the switch.

Make sense?

Thanks!

[MMcCarthy]

This should create a new table with the fields you want. You can rename the tables later.

INSERT INTO NewTableName (FieldA, FieldB, FieldC, FieldE, FieldF)
SELECT Table1.FieldA, Table1.FieldB, First(Table2.FieldC), First(Table2.FieldE), First(Table2.FieldF)
FROM Table1 LEFT JOIN Table2
ON Table1.FieldC = Table2.FieldC
GROUP BY  Table1.FieldA, Table1.FieldB;
 

Mary