468,550 Members | 1,709 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,550 developers. It's quick & easy.

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

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
1 1399
MMcCarthy
14,534 Expert Mod 8TB
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.

Similar topics

2 posts views Thread by Donald Firesmith | last post: by
8 posts views Thread by dstefani | last post: by
3 posts views Thread by Dr. Oz | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.