Append Query Auto Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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,
 
Brilliant! Of course! My motto is usually "When in doubt, take the most
complicated path." thanks
 
Back
Top