Append query

[Deborah Mowry]

How exactly does an append query work? I tried to normalize a table of
contacts into tblCompany and tblContacts. When I appended to the structure
only 1/3 of my expected records came.
Here is what I did:
I copied the original table - structure only.
Delete the fields I don't need in this table for Company Names. Kept the
unique identifier which was correct in the original just listed multiple
times for each contact name.
Then did an append query from original to Company Names.
I did get 200 but when I checked the original there are atlease 300 more.
They do not have the same identifier, I checked.
Do all the fields have to be populated before the append query will send a
record over? It looks that way?

Some of the Companys are missing some info in the fields but they are not
required at this time.
Thank you

 

[Jeff Boyce]

Deborah

Without a look at the SQL statements (queries) you are using, it'll be tough
to offer you suggestions (or at least, reasonably accurate suggestions).

More info, please

Jeff Boyce
<Access MVP>

 

[Deborah Mowry]

As requested, this is the SQL query statement:

INSERT INTO Company ( AcctD, CSta, CNam, CLoc, Room, Add1, Add2, City, Prov,
PCod, CS1, CS2, CS3 )
SELECT [Original Contacts].AcctD, [Original Contacts].CSta, [Original
Contacts].CNam, [Original Contacts].CLoc, [Original Contacts].Room,
[Original Contacts].Add1, [Original Contacts].Add2, [Original
Contacts].City, [Original Contacts].Prov, [Original Contacts].PCod,
[Original Contacts].CS1, [Original Contacts].CS2, [Original Contacts].CS3
FROM [Original Contacts];

 

[Jeff Boyce]

Deborah

Right off, I didn't spot anything that raised suspicions. What happens if
you convert this to a simple select query, without the append? Do you get
the rows you expect?

Jeff Boyce
<Access MVP>

 

[Deborah Mowry]

Here is the first 2 records changing the append query to a regular select
query. There are 70000 records in total, I have to be able to delete the
duplicates. I tried on another made-up table the exact steps I did and it
worked find - hense my question...do all the fields have to be populated?
That is the only deduction I can make as there are no longer any empty
fields.

qryAppendCompany AcctD CSta CNam CLoc Room Add1 Add2 City
0000200 S Sears

34 1st Ave.
Langley
0000200 S Sears

34 1st Ave.
Langley

 

[Jeff Boyce]

Deborah

Perhaps I didn't do a good job of explaining...

I was suggesting that you create a select query and run it, then see if you
get the correct number of rows. Your original post(s) suggested that you
are NOT getting the correct number of rows when you do the append query.

I would first confirm that you can get the rows you expect, then move on to
why they might not all be appended.

Jeff Boyce
<Access MVP>

 

[Deborah Mowry]

Now it is my turn to not understand what you need.
The table is huge (70000+). It contains Company names listed mutliple times
for each contact found within.
They never had a unique identifier, the closest is the AccID which refers to
the Company.
Therefore in creating a new table, I made that AccID a key fields so that
only one of each would be appended to it - in otherwords, getting rid of the
duplicates.
The Contact table I will just make an foreign key to the AccID, then give
them an AutoNumber for ID.
I was told that I could query for duplicates, but the only way to actually
get rid of the duplicate was to append them to another table (structure
only) that contains a key field. When I do a query for duplicates, I get
64,000 records.

I apologize if I am not explaining myself correctly. The duplicate query
does show what I expected. When I append them to another table structure, I
expected the see the number reduced to about 8,000 due to the duplicates,
thus leaving me one copy of each company.
Is what I am doing sound wrong? I really need to normalize this database.

With greatest appreciation,
Deborah

 

[Jeff Boyce]

Deborah

I hope I understand now...

If you want an append query to "remove" duplicates, you have to have
something unique in the "from" table/query. You also have to set that
something unique as an Index-No Duplicates in the "to" table.

When you run the append query, the No Duplicates index prevents the second
(and third, and ...) "copies" from being appended. You'll get what appears
to be an error message -- something like "not all have been appended, due to
..... key violation". That's a good thing!

Does that get closer to what you're seeking?

Jeff Boyce
<Access MVP>