Append Query Auto Number

[Guest]

I just spent two full days reading the great book "Database Design for Mere
Mortals" and designing what seems like a simple database to me. As he
suggests, I designed it all on paper and was meticulous about the table and
field design and learned tons about relationships...

So, I started to put tables in the database and there is something I am
missing.

I have the following table I want to populate

tblAgency w/
Agency_ID (Auto Number) PK
AgencyName (Text)

tblLOC w/
LOC_ID (Auto Number) PK
LOCName

tblProgram w/
Prm_Code PK
Agency_ID FK
LOC_ID FK

I have another older table that has
Prm_Code
AgencyName
LOCName

I did an append query from the olf table to populate tblAgency and tblLOC
and each table filled in it's own auto number PK. When I try to do a make
table query to make the tblProgram with the Prm_Code, Agency_ID and LOC_ID,
it tells me I can't have more than one Auto Number in a table. I don't want
any auto number in that table. I want Prm_Code to be the PK.

How can I have the right IDs as foreign keys in tblProgram if I can't bring
them in from the other tables?

 

[George Nicholson]

Don't use a make table query. I'm guessing that it's trying to create fields
with the exact same properties as the fields in your source tables, and it
can't "recreate" the 2 autonumber fields in a single table..

Create the table (one way would be to copy & paste the fields in design view
from the other tables, changing Autonumber to Number once pasted), and then
do an Append query to fill it.

HTH,

 

[Guest]

Brilliant! Of course! My motto is usually "When in doubt, take the most
complicated path." thanks