By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,933 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

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

P: 1
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!
Dec 19 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
This should create a new table with the fields you want. You can rename the tables later.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO NewTableName (FieldA, FieldB, FieldC, FieldE, FieldF)
  2. SELECT Table1.FieldA, Table1.FieldB, First(Table2.FieldC), First(Table2.FieldE), First(Table2.FieldF)
  3. FROM Table1 LEFT JOIN Table2
  4. ON Table1.FieldC = Table2.FieldC
  5. GROUP BY  Table1.FieldA, Table1.FieldB;
  6.  
Mary
Dec 21 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.