Help with a query

[jimbo]

Hello FutureQuestarians,

I am overhauling an Access application for my company, and have been struggling with taking the existing poorly designed database and normalizing it. Basically, there is only one table that contains all the information, and obviously that is not good. Here's an example of what I need to do:

Table 1
ID
First Name
Last Name
Location
Yadda
Yadda

I need to extract First Name and Last Name as unique, insert them into Table 2, and then take the new ID and insert it into Table 1.

Basically I need to take the above table, and turn it into two tables like the following:

Table 1
ID
Location
ContactID
Yadda
Yadda

Table 2
ID
First Name
Last Name

I hope that made some sense! :)

Can someone give me some guidance as to if and how it is possible to do this? It's the multi-step part of things that is throwing me off.

Thanks!

-jim

[Arthur]

I think that the easiest way to do this would probably be to use the table analysis wizard in Access. It will analyze the table and suggest ways to split it up and distribute the data to the tables automatically.
It's under the menu 'Extra' -> 'Analyze' (I have a Dutch version, so the actual names in English may differ).

HTH,
Arthur

[Bruce]

Assuming you have table1 and table2 created, and "current" is the existing table, try:
INSERT INTO table2 (lastname, firstname)
SELECT lastname, firstname
FROM current;

INSERT INTO table1 (location, contactid, etc)
SELECT location, table2.id, etc
FROM current,table2
WHERE current.lastname=table2.lastname
AND current.firstname=table2.firstname;


Then, drop "current" and rename table1 to whatever is appropriate (after verifying the data in it, of course).

[jimbo]

Arthur,

A solid suggestion, I thank you for your reply! I should have mentioned in my original post that I tried that method, and it isn't the most surefire way of accomplishing what I need to do. It gave me 11,000 items to correct, and if I'm going to do that, I may as well just do it by hand in Excel or something.

But normally that would be a great suggestion, thanks again!



Bruce,

Splendid - I never thought of going at it from that angle, and I think it will work wonderfully! It seems to do exactly what I needed to do.

Thanks for your guidance :).

:D

Jim
-- someday...

[jimbo]

Bruce,

The first query worked great!

But I'm having trouble with the second one.

Could you just tell me if this looks right from a syntax point of view?

INSERT INTO Table1 ( Owner_ID )
SELECT Contacts.ID
FROM Contacts
WHERE ((([Table1].[OWNER LAST NAME])=[Contacts]![LASTNAME])
AND (([Table1].[OWNER FIRST NAME])=[Contacts]![FIRSTNAME]));

It keeps prompting me for input when I run the query - it wants a parameter value for Table1.OWNER LAST NAME.

Is there anything fundamentally wrong with my syntax that would be causing this?

Thanks a ton!

-jim

[Bruce]

There appear to be several problems with that query.

First off, you're inserting more than just the owner_id into the new table -- this new table is going to replace the old one, so you need to insert everything except those parts that are being replaced by the owner_id.

Secondly, your select statement is coming from "contacts", but your where conditions reference both "contacts" and "table1".

Finally, does your query literally contain "[OWNER LAST NAME]"? I'm unfamilar with that syntax.
If you post complete descriptions of the tables (like the original create statement), I can probably produce the exact queries you need to produce the final result.

[jimbo]

Okay, I was just trying to insert the new record id into the existing table, since there are many more fields in the existing table - I figured it would be easier to only import one instead of many.

The syntax was generated by Access - I tried using the SQL that I'm familiar with and it didn't work - so I used their Builder to build the query.

I didn't use queries to create the tables, but I could give you a table analysis that gives you all of the info about the table. Would you be able to use that in PDF form?

I appreciate your help, I really really do! This has been driving me up a wall for several weeks now - because I could do this in a heartbeat with php/mysql.

It might be easier for me to do it with PHP, save the results as a CSV file, and import it into Access all done properly :). That's actually not a bad idea! Thanks again :).

-jim