470,842 Members | 1,642 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

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?

Dec 19 '06 #1
1 1450
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;
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 mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.