Append Query - too many rows

[allredkj]

I am hoping to get some assistance for MS Access append query. I have two tables with a one to one ratio, number of rows is 96406 in both tables. I have joined the tables by an Acct_Num. Simple right? I want to append to a table which currently has no rows. No matter how I join the tables, Access wants to append 110562 rows, which is 14156 rows too many. I am not sure why this is happening. HELP!!!

Thanks, Kelly

 

[jdraw]

Tell us about all the tables -- fields, primary keys etc.
Is it possible that there are some duplicates and these are not being stored in the new table?

Show us the full query code (sql).

Have you tried doing Select queries to find out where the discrepancy is?

 

[Galaxiom]

This would only happen if the relationship is not one-to-one.

There are multiple records for some of the Acct_Num values. Each combination from the two tables will result in a record.

You need to be able to join on unique values. Yhis may be possible by using multiple joins.

 

[allredkj]

Both tables have the exact same number of rows.... 96406

I wasn't able to set a primary key... Access did say there were duplicates. Hmmm, maybe I do need multiple joins.... I'll take another look at the data. Thank you so much for your help. Since you asked, here's the SQL code for the join:

INSERT INTO DALPPROP ( ACCT_NUM, REACCT, REGION, ROUTE, BLDG_CLASS, TYPE_FIRM, STATUS, REND_CURR, REND_PAST, REFUSED, REF_PAST, POC, ST_NUM1, ST_DIR1, ST_NAME1, ST_SUFFIX, ST_HALFNUM, SUITE, CITY, TAXP_NAME1, PHONE, EST_DATE, TAX_CONSUL, BLDG_AREA, [CURRENT], PAST, SCITY, SSCHOOL, EFF_AGE, J1, SP1, HOSPITAL, COLLEGE, APPDATE, WKINV, WKMATL, WKSUPPLIES, WKCONSGD, WKFURN_FIX, WKMACHEQ, WKCOMPUTER, WKVEHICLE, WKLEASEDEQ, WKAIRCRAFT, WKLEASEHOL, WKMISC, PREVINVVAL, NAME_BUS, MAPSCO )
SELECT DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ACCOUNT_NUM, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.GIS_NUM, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.REGION, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ROUTE, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.PROPERTY_CLASS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.BUSINESS_TYPE, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ACCT_STATUS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.RENDERED_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.RENDERED_PRIOR, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.REFUSED_ENTRY_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.REFUSED_ENTRY_PRIOR, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.PERSON_CONTACTED, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.STREET_NUM, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.STREET_DIR, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.STREET_NAME, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.STREET_SUFFIX, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.STREET_HALF_NUM, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.SUITE_NUMBER, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.CITY, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.OWNER_NAME, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.OWNER_PHONE, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ESTABLISHED_DT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.TAX_CONSULTANT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.TOT_SF, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.TOT_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.TOT_VAL_PRIOR, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.CITY_TAXABLE_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ISD_TAXABLE_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.EFFECTIVE_AGE, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.CNTY_JURIS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.SPDS_JURIS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.HOSP_JURIS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.COLL_JURIS, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.APPRAISAL_DT_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.INVENTORY_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.RAW_MATERIALS_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.SUPPLIES_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.CONS_GOODS_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.FURN_FIX_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.MACH_EQUIP_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.COMPUTERS_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.VEHICLES_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.LEASE_EQUIP_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.AIRCRAFT_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.LH_IMPROVE_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.MISC_VAL_CURRENT, DCAD2011_BPP_DETAIL_CERTIFIED_07252011.INVENTORY_VAL_PRIOR, BPP_AccountInfo_07252012.BIZ_NAME, BPP_AccountInfo_07252012.MAPSCO
FROM DCAD2011_BPP_DETAIL_CERTIFIED_07252011 INNER JOIN BPP_AccountInfo_07252012 ON (DCAD2011_BPP_DETAIL_CERTIFIED_07252011.APPRAISAL_YR = BPP_AccountInfo_07252012.APPRAISAL_YR) AND (DCAD2011_BPP_DETAIL_CERTIFIED_07252011.ACCOUNT_NUM = BPP_AccountInfo_07252012.ACCOUNT_NUM);

 

[Galaxiom]

Both tables have the exact same number of rows.... 96406

HAving the same number of rows does not enure the tables have a one-to-one relationship.

I wasn't able to set a primary key... Access did say there were duplicates. Hmmm, maybe I do need multiple joins

You need to be able to set a common key in both tables or you won't be able to get a one-to-one. This may be a composite key (more than one field). Once you can achieve this you can join on those fields.

BTW When you post code put it in code tags and it will display better.
http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015