How to append records to a table that already has those records...

[Triedtwice]

I can't seem to make an Append Query do what I want...any help will be appreciated!

I need to append records to a table called "tbl_6A_POP" from a table called "tbl_Media_Code". tbl_Media_Code has 7 records ("1000" thu "6000" plus "6500" in a field called "Media_Code"). tbl_6A_POP already has a field called "Media_Code" that has several hundred entries...an excerpt from tbl_6A_POP looks like this:

[TABLE="width: 350"]
<tbody>[TR]
[TD]MC_PK[/TD]
[TD]TTLM_No_PK[/TD]
[TD]Media_Code[/TD]
[TD] Rec_No[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]19-39-5[/TD]
[/TR]
[TR]
[TD]336[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]749-9-1[/TD]
[/TR]
[TR]
[TD]337[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]56-55-378[/TD]
[/TR]
[TR]
[TD]338[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]50-3-8[/TD]
[/TR]
[TR]
[TD]339[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]20-9-2[/TD]
[/TR]
[TR]
[TD]340[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]53-704-3[/TD]
[/TR]
[TR]
[TD]341[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]13-9-5[/TD]
[/TR]
[TR]
[TD]343[/TD]
[TD]244[/TD]
[TD]5000[/TD]
[TD]1479-73-001[/TD]
[/TR]
[TR]
[TD]344[/TD]
[TD]244[/TD]
[TD]5000[/TD]
[TD]79-1-634[/TD]
[/TR]
[TR]
[TD]348[/TD]
[TD]247[/TD]
[TD]6000[/TD]
[TD]NA_None[/TD]
[/TR]
[TR]
[TD]349[/TD]
[TD]248[/TD]
[TD]6000[/TD]
[TD]NA_None[/TD]
[/TR]
[TR]
[TD]350[/TD]
[TD]249[/TD]
[TD]6000[/TD]
[TD]NA_None[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, there are multiple records for a given TTLM_No_PK, but not every Media_Code is recorded for each TTLM_No_PK. I need to append tbl_6A_POP to have each of the 7 records from tbl_Media_Code for each TTLM_No_PK in tbl_6A_POP. Even as I'm typing this, I realize I'm probably not making any sense...but if you might have a suggestion for getting the missing "Media_Code" records into the tbl_6A_POP table WITHOUT deleting the existing Media_Code records in tbl_6A_POP, I'll be a happy camper!

Thanks!

 

[Joe4]

You are right - it is not making much sense.

First, understand the different types of Action Query types, and choose the appropriate one(s) for your task at hand:
- Make Table Query - this creates a brand new table and adds records to it
- Append Query - this only ADDS records to an existing table
- Update Query - this only UPDATES existing records in another existing table
- Delete Query - this will DELETE records from an existing query

When creating an Append Query, here is the way I usually go about it.
- First create a Select Query that contains JUST the records you want to append to the other Table
- Once you have that first part working right, change the Query to an Append Query, and make sure that you have all the correct fields being mapped to the right place.
- Run the query

So, which part is giving you issues?

 

[Triedtwice]

Thanks for the prompt reply!!! I'll try to better explain what I need. I'm trying to append the table "tbl_6A_POP" to show each of the 7 media_codes from the "tbl_Media_Code" table...but tbl_6A_POP already has data for some of the media_codes that I don't want to overwrite with the append query. For instance, the current records from tbl_6A_POP for a single TTLM_No_PK looks like this:

[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]TTLM_No_PK[/TD]
[TD]Media_Code[/TD]
[TD]Rec_No[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]19-39-5[/TD]
[/TR]
[TR]
[TD]336[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]749-9-1[/TD]
[/TR]
[TR]
[TD]337[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]56-55-378[/TD]
[/TR]
[TR]
[TD]338[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]50-3-8[/TD]
[/TR]
[TR]
[TD]339[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]20-9-2
[/TD]
[/TR]
</tbody>[/TABLE]

I need it to look like this:
[TABLE="width: 350"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]TTLM_No_PK[/TD]
[TD]Media_Code[/TD]
[TD]Rec_No[/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]242[/TD]
[TD]1000[/TD]
[TD]NA_None [/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]242[/TD]
[TD]2000[/TD]
[TD]NA_None [/TD]
[/TR]
[TR]
[TD]335[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]19-39-5[/TD]
[/TR]
[TR]
[TD]336[/TD]
[TD]242[/TD]
[TD]3000[/TD]
[TD]749-9-1[/TD]
[/TR]
[TR]
[TD]337[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]56-55-378[/TD]
[/TR]
[TR]
[TD]338[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]50-3-8[/TD]
[/TR]
[TR]
[TD]339[/TD]
[TD]242[/TD]
[TD]4000[/TD]
[TD]20-9-2[/TD]
[/TR]
[TR]
[TD](auto)[/TD]
[TD]242[/TD]
[TD]5000[/TD]
[TD]NA_None [/TD]
[/TR]
[TR]
[TD](auto)[/TD]
[TD]242[/TD]
[TD]6000[/TD]
[TD]NA_None [/TD]
[/TR]
[TR]
[TD](auto)[/TD]
[TD]242[/TD]
[TD]6500[/TD]
[TD]NA_None[/TD]
[/TR]
</tbody>[/TABLE]

the Media_Code values of 1000, 2000, 5000, 6000, and 6500 were added, each with a value of "NA_None" for the field Rec_No.

Man, I'm doubting that I'm making any more sense...but maybe?

 

[Joe4]

I don't want to overwrite with the append query.

Append queries never overwrite data - they only add new records.
Update queries are the ones that would update (or overwrite) existing records.

So if you are using an Append query, you would not have to worry about any data being overwritten.

 

[Triedtwice]

so...is it possible to get the 7 different records from tbl_Media_Code into each TTLM_No_PK from tbl_6A_POP?

 

[dogdays]

I think it is possible using VBA. Couple of questions first: is ID unique, second is ID an autonumber (slightly redundant question),
what is the volume of records? in the main table?

Jack

 

[Joe4]

Remember what I said back in the first post?

When creating an Append Query, here is the way I usually go about it.
- First create a Select Query that contains JUST the records you want to append to the other Table

So, your first step is to just create a Select Query that has the records you want to write to your table.
If some of the records already exist, you should be able to create an Unmatched Query to return just the records that do NOT already exist (there is even an Unmatched Query Wizard).
So create that first, then change to an Append Query, and it should add just the records that you want.

 

[Triedtwice]

Thanks! I'll give that a try (never tried an Unmatched Query)!

 

[Joe4]

Thanks! I'll give that a try (never tried an Unmatched Query)!

Its always a good idea to make a backup/copy of your database. That way you can feel free to play around with it, and if you mess anything up, you can restore the copy.

One caveat on the Unmatched Query Wizard is that the Wizard only lets you join on one single field. If you need more than one field in your join, just go through the Wizard and set it up to join on one field. Then when finished, open the query in the Query Builder and add any other joins that you need (making sure that the go in the same order as original one, as you will be using Left Joins). After you click on one field and drag to the field in the other table to create the join, if you click on the join line, it will let you change the join type so that it matches the first (they should both have arrows pointing in the same direction if done properly).

 

[Triedtwice]

Brother, I need to take an Access class!!! Thanks for the help...definitely!