We help IT Professionals succeed at work.

Append Query Doesn't List Table in the Drop-Down

carlosmonte
carlosmonte asked
on
947 Views
Last Modified: 2011-10-19
I have a table that I need to append records to, but in the Append Query dialog box, my table does not appear!

More Details:

1.  I have a linked table to an external database that lists all my records (dbo_tblAdmissionSource).  
2.  I then have a static table (dbo_tblAdmission) that needs to get updated from the linked table.  Also, this static table has a One-To-Many relationship with another table (tblIncidents).
3.  So, I have built a Find Unmatched Query (qry_dbo_MissingResidents) to compare the linked table and the static table, so far so good.  
4.  I then refined the query (in order to remove a like-named-field) - (qry_dboMissingResidentsToAppend).
5.  Now, when I try to change this select query into an Append Query, the original static table name that I need to append to is just not there (dbo_tblAdmission)!  What's up with that?
Append-Dialog-Box---Table-Not-Li.JPG
Queries.JPG
Tables.JPG
Comment
Watch Question

Most Valuable Expert 2014

Commented:
There should be a button that says SQL in views list.

Click it and it should bring up a bunch of text. Can you copy it an paste it here?

Author

Commented:
The SQL View from the qry_dbo_MissingResidentsToAppend query is pasted below.  Is this what you were referring to?
SELECT qry_dbo_MissingResidents.CompanyNo, qry_dbo_MissingResidents.ResidentID, qry_dbo_MissingResidents.Lastname, qry_dbo_MissingResidents.Firstname, qry_dbo_MissingResidents.MI, qry_dbo_MissingResidents.ResidentAddress, qry_dbo_MissingResidents.ResidentCity, qry_dbo_MissingResidents.ResidentState, qry_dbo_MissingResidents.ResidentZip, qry_dbo_MissingResidents.ResidentCounty, qry_dbo_MissingResidents.ResidentPhone, qry_dbo_MissingResidents.ResidentWorkPhone, qry_dbo_MissingResidents.Birthday, qry_dbo_MissingResidents.Age, qry_dbo_MissingResidents.Sex, qry_dbo_MissingResidents.SSN, qry_dbo_MissingResidents.Medicare, qry_dbo_MissingResidents.[Medicaid#], qry_dbo_MissingResidents.BirthPlace, qry_dbo_MissingResidents.Religion, qry_dbo_MissingResidents.Language, qry_dbo_MissingResidents.MaritalStatus, qry_dbo_MissingResidents.Race, qry_dbo_MissingResidents.[Military Service], qry_dbo_MissingResidents.UsCitizen, qry_dbo_MissingResidents.ReferredBy, qry_dbo_MissingResidents.RP_lastname, qry_dbo_MissingResidents.RP_Firstname, qry_dbo_MissingResidents.RP_MI, qry_dbo_MissingResidents.RP_address, qry_dbo_MissingResidents.RP_city, qry_dbo_MissingResidents.RP_state, qry_dbo_MissingResidents.RP_zip, qry_dbo_MissingResidents.RP_attention, qry_dbo_MissingResidents.RP_phone, qry_dbo_MissingResidents.[RP_phone(work)], qry_dbo_MissingResidents.Relationship, qry_dbo_MissingResidents.Diagnosis1, qry_dbo_MissingResidents.Diagnosis2, qry_dbo_MissingResidents.Diagnosis3, qry_dbo_MissingResidents.Diagnosis4, qry_dbo_MissingResidents.Diagnosis5, qry_dbo_MissingResidents.Diagnosis6, qry_dbo_MissingResidents.Diagnosis7, qry_dbo_MissingResidents.Diagnosis8, qry_dbo_MissingResidents.Diagnosis9, qry_dbo_MissingResidents.Diagnosis10, qry_dbo_MissingResidents.Diagnosis11, qry_dbo_MissingResidents.Diagnosis12, qry_dbo_MissingResidents.Diagnosis13, qry_dbo_MissingResidents.Diagnosis14, qry_dbo_MissingResidents.Diagnosis15, qry_dbo_MissingResidents.Diagnosis16, qry_dbo_MissingResidents.Diagnosis17, qry_dbo_MissingResidents.Diagnosis18, qry_dbo_MissingResidents.Diagnosis19, qry_dbo_MissingResidents.Diagnosis20, qry_dbo_MissingResidents.Dcode1, qry_dbo_MissingResidents.Dcode2, qry_dbo_MissingResidents.Dcode3, qry_dbo_MissingResidents.Dcode4, qry_dbo_MissingResidents.Dcode5, qry_dbo_MissingResidents.Dcode6, qry_dbo_MissingResidents.Dcode7, qry_dbo_MissingResidents.Dcode8, qry_dbo_MissingResidents.Dcode9, qry_dbo_MissingResidents.Dcode10, qry_dbo_MissingResidents.Dcode11, qry_dbo_MissingResidents.Dcode12, qry_dbo_MissingResidents.Dcode13, qry_dbo_MissingResidents.Dcode14, qry_dbo_MissingResidents.Dcode15, qry_dbo_MissingResidents.Dcode16, qry_dbo_MissingResidents.Dcode17, qry_dbo_MissingResidents.Dcode18, qry_dbo_MissingResidents.Dcode19, qry_dbo_MissingResidents.Dcode20, qry_dbo_MissingResidents.DxCode1, qry_dbo_MissingResidents.DxCode2, qry_dbo_MissingResidents.DxCode3, qry_dbo_MissingResidents.DxCode4, qry_dbo_MissingResidents.DxCode5, qry_dbo_MissingResidents.DxCode6, qry_dbo_MissingResidents.DxCode7, qry_dbo_MissingResidents.DxCode8, qry_dbo_MissingResidents.DxCode9, qry_dbo_MissingResidents.DxCode10, qry_dbo_MissingResidents.DxCode11, qry_dbo_MissingResidents.DxCode12, qry_dbo_MissingResidents.DxCode13, qry_dbo_MissingResidents.DxCode14, qry_dbo_MissingResidents.DxCode15, qry_dbo_MissingResidents.DxCode16, qry_dbo_MissingResidents.DxCode17, qry_dbo_MissingResidents.DxCode18, qry_dbo_MissingResidents.DxCode19, qry_dbo_MissingResidents.DxCode20, qry_dbo_MissingResidents.AuthorizationNo, qry_dbo_MissingResidents.AuthorizationDate, qry_dbo_MissingResidents.MedicareDate, qry_dbo_MissingResidents.MedicaidDate, qry_dbo_MissingResidents.AdmissionCode, qry_dbo_MissingResidents.PhysicianName, qry_dbo_MissingResidents.Alternate, qry_dbo_MissingResidents.Dentist, qry_dbo_MissingResidents.Pharmacy, qry_dbo_MissingResidents.EyeDoctor, qry_dbo_MissingResidents.Podiatrist, qry_dbo_MissingResidents.Psychiatrist, qry_dbo_MissingResidents.Mortuary, qry_dbo_MissingResidents.Church, qry_dbo_MissingResidents.Hospital, qry_dbo_MissingResidents.Ambulance, qry_dbo_MissingResidents.OriginalAdmissionDate, qry_dbo_MissingResidents.InsName, qry_dbo_MissingResidents.InsAddress, qry_dbo_MissingResidents.InsCity, qry_dbo_MissingResidents.InsState, qry_dbo_MissingResidents.InsZip, qry_dbo_MissingResidents.InsPrimaryPhoneNo, qry_dbo_MissingResidents.InsSecondaryPhoneNo, qry_dbo_MissingResidents.InsCertificate, qry_dbo_MissingResidents.InsGroupNo, qry_dbo_MissingResidents.InsGroupName, qry_dbo_MissingResidents.InsContractNo, qry_dbo_MissingResidents.InsInsuredName, qry_dbo_MissingResidents.InsInsuredRelationship, qry_dbo_MissingResidents.LastDischargeDate, qry_dbo_MissingResidents.LastDischargeStatus, qry_dbo_MissingResidents.LastDischargeDestination, qry_dbo_MissingResidents.EmergencyContactName, qry_dbo_MissingResidents.EmergencyContactAddress, qry_dbo_MissingResidents.EmergencyContactCity, qry_dbo_MissingResidents.EmergencyContactState, qry_dbo_MissingResidents.EmergencyContactZip, qry_dbo_MissingResidents.EmergencyContactPhone, qry_dbo_MissingResidents.EmergencyContactWorkNo, qry_dbo_MissingResidents.EmergencyContactRelationship, qry_dbo_MissingResidents.SecondContactName, qry_dbo_MissingResidents.SecondContactAddress, qry_dbo_MissingResidents.SecondContactCity, qry_dbo_MissingResidents.SecondContactState, qry_dbo_MissingResidents.SecondContactZip, qry_dbo_MissingResidents.SecondContactPhone, qry_dbo_MissingResidents.SecondContactWorkNo, qry_dbo_MissingResidents.SecondContactRelationship, qry_dbo_MissingResidents.ThirdContactName, qry_dbo_MissingResidents.ThirdContactAddress, qry_dbo_MissingResidents.ThirdContactCity, qry_dbo_MissingResidents.ThirdContactState, qry_dbo_MissingResidents.ThirdContactZip, qry_dbo_MissingResidents.ThirdContactPhone, qry_dbo_MissingResidents.ThirdContactWorkNo, qry_dbo_MissingResidents.ThirdContactRelationship, qry_dbo_MissingResidents.Comments, qry_dbo_MissingResidents.Allergies, qry_dbo_MissingResidents.AdvanceDirectives, qry_dbo_MissingResidents.[Medicare(B)], qry_dbo_MissingResidents.[Medicare(B)Date], qry_dbo_MissingResidents.CustomItem1, qry_dbo_MissingResidents.CustomItem2, qry_dbo_MissingResidents.CustomItem3, qry_dbo_MissingResidents.CustomItem4, qry_dbo_MissingResidents.CustomItem5, qry_dbo_MissingResidents.CustomItem6, qry_dbo_MissingResidents.CustomItem7, qry_dbo_MissingResidents.CustomItem8, qry_dbo_MissingResidents.CustomItem9, qry_dbo_MissingResidents.CustomDate1, qry_dbo_MissingResidents.CustomDate2, qry_dbo_MissingResidents.CustomAmount1, qry_dbo_MissingResidents.CustomAmount2, qry_dbo_MissingResidents.CustomAmount3, qry_dbo_MissingResidents.CustomAmount4, qry_dbo_MissingResidents.CustomAmount5, qry_dbo_MissingResidents.PhotoPath, qry_dbo_MissingResidents.X12Setup
FROM qry_dbo_MissingResidents;

Open in new window

Author

Commented:
I've attached a JPEG on the relationships view as well.
Relationships.JPG

Author

Commented:
I see from another web site that this seems to have been an issue with the missing table being the first one on the list.

I renamed the statis table that I wanted to append to, and voila, it's there in the append dialog box list!  

It also seems to be that the Service Pack 3 (for Access 2003) that I have installed may be doing more damage than good.  It's recommended to roll-back to Service Pack 2.  Anyone hear of this before?
Most Valuable Expert 2014

Commented:
I've heard of this happening before. I think it can happen if you have a hidden table with the same name.

Go up under Tools --> Options and on the View tab select Hiiden and System Objects.

Then go through and make sure none of the other tabs have something with the same name as dbo_tblAdmission. Then try it again.

If that doesn't work, copy the code below and place it in the SQL view and see if it chokes.
INSERT INTO dbo_tblAdmission (CompanyNo, ResidentID, Lastname, Firstname, MI, ResidentAddress, ResidentCity,
ResidentState, ResidentZip, ResidentCounty, ResidentPhone, ResidentWorkPhone, Birthday,
Age, Sex, SSN, Medicare, [Medicaid#], BirthPlace, Religion, Language, MaritalStatus, Race,
[Military Service], UsCitizen, ReferredBy, RP_lastname, RP_Firstname, RP_MI, RP_address,
RP_city, RP_state, RP_zip, RP_attention, RP_phone, [RP_phone(work)], Relationship,
Diagnosis1, Diagnosis2, Diagnosis3, Diagnosis4, Diagnosis5, Diagnosis6, Diagnosis7,
Diagnosis8, Diagnosis9, Diagnosis10, Diagnosis11, Diagnosis12, Diagnosis13, Diagnosis14,
Diagnosis15, Diagnosis16, Diagnosis17, Diagnosis18, Diagnosis19, Diagnosis20, Dcode1,
Dcode2, Dcode3, Dcode4, Dcode5, Dcode6, Dcode7, Dcode8, Dcode9, Dcode10, Dcode11, Dcode12,
Dcode13, Dcode14, Dcode15, Dcode16, Dcode17, Dcode18, Dcode19, Dcode20, DxCode1, DxCode2,
DxCode3, DxCode4, DxCode5, DxCode6, DxCode7, DxCode8, DxCode9, DxCode10, DxCode11,
DxCode12, DxCode13, DxCode14, DxCode15, DxCode16, DxCode17, DxCode18, DxCode19, DxCode20,
AuthorizationNo, AuthorizationDate, MedicareDate, MedicaidDate, AdmissionCode,
PhysicianName, Alternate, Dentist, Pharmacy, EyeDoctor, Podiatrist, Psychiatrist,
Mortuary, Church, Hospital, Ambulance, OriginalAdmissionDate, InsName, InsAddress,
InsCity, InsState, InsZip, InsPrimaryPhoneNo, InsSecondaryPhoneNo, InsCertificate,
InsGroupNo, InsGroupName, InsContractNo, InsInsuredName, InsInsuredRelationship,
LastDischargeDate, LastDischargeStatus, LastDischargeDestination, EmergencyContactName,
EmergencyContactAddress, EmergencyContactCity, EmergencyContactState, EmergencyContactZip,
EmergencyContactPhone, EmergencyContactWorkNo, EmergencyContactRelationship,
SecondContactName, SecondContactAddress, SecondContactCity, SecondContactState,
SecondContactZip, SecondContactPhone, SecondContactWorkNo, SecondContactRelationship,
ThirdContactName, ThirdContactAddress, ThirdContactCity, ThirdContactState,
ThirdContactZip, ThirdContactPhone, ThirdContactWorkNo, ThirdContactRelationship,
Comments, Allergies, AdvanceDirectives, [Medicare(B)], [Medicare(B)Date], CustomItem1,
CustomItem2, CustomItem3, CustomItem4, CustomItem5, CustomItem6, CustomItem7, CustomItem8,
CustomItem9, CustomDate1, CustomDate2, CustomAmount1, CustomAmount2, CustomAmount3,
CustomAmount4, CustomAmount5, PhotoPath, X12Setup )
SELECT CompanyNo, ResidentID, Lastname, Firstname, MI, ResidentAddress, ResidentCity,
ResidentState, ResidentZip, ResidentCounty, ResidentPhone, ResidentWorkPhone, Birthday,
Age, Sex, SSN, Medicare, [Medicaid#], BirthPlace, Religion, Language, MaritalStatus, Race,
[Military Service], UsCitizen, ReferredBy, RP_lastname, RP_Firstname, RP_MI, RP_address,
RP_city, RP_state, RP_zip, RP_attention, RP_phone, [RP_phone(work)], Relationship,
Diagnosis1, Diagnosis2, Diagnosis3, Diagnosis4, Diagnosis5, Diagnosis6, Diagnosis7,
Diagnosis8, Diagnosis9, Diagnosis10, Diagnosis11, Diagnosis12, Diagnosis13, Diagnosis14,
Diagnosis15, Diagnosis16, Diagnosis17, Diagnosis18, Diagnosis19, Diagnosis20, Dcode1,
Dcode2, Dcode3, Dcode4, Dcode5, Dcode6, Dcode7, Dcode8, Dcode9, Dcode10, Dcode11, Dcode12,
Dcode13, Dcode14, Dcode15, Dcode16, Dcode17, Dcode18, Dcode19, Dcode20, DxCode1, DxCode2,
DxCode3, DxCode4, DxCode5, DxCode6, DxCode7, DxCode8, DxCode9, DxCode10, DxCode11,
DxCode12, DxCode13, DxCode14, DxCode15, DxCode16, DxCode17, DxCode18, DxCode19, DxCode20,
AuthorizationNo, AuthorizationDate, MedicareDate, MedicaidDate, AdmissionCode,
PhysicianName, Alternate, Dentist, Pharmacy, EyeDoctor, Podiatrist, Psychiatrist,
Mortuary, Church, Hospital, Ambulance, OriginalAdmissionDate, InsName, InsAddress,
InsCity, InsState, InsZip, InsPrimaryPhoneNo, InsSecondaryPhoneNo, InsCertificate,
InsGroupNo, InsGroupName, InsContractNo, InsInsuredName, InsInsuredRelationship,
LastDischargeDate, LastDischargeStatus, LastDischargeDestination, EmergencyContactName,
EmergencyContactAddress, EmergencyContactCity, EmergencyContactState, EmergencyContactZip,
EmergencyContactPhone, EmergencyContactWorkNo, EmergencyContactRelationship,
SecondContactName, SecondContactAddress, SecondContactCity, SecondContactState,
SecondContactZip, SecondContactPhone, SecondContactWorkNo, SecondContactRelationship,
ThirdContactName, ThirdContactAddress, ThirdContactCity, ThirdContactState,
ThirdContactZip, ThirdContactPhone, ThirdContactWorkNo, ThirdContactRelationship,
Comments, Allergies, AdvanceDirectives, [Medicare(B)], [Medicare(B)Date], CustomItem1,
CustomItem2, CustomItem3, CustomItem4, CustomItem5, CustomItem6, CustomItem7, CustomItem8,
CustomItem9, CustomDate1, CustomDate2, CustomAmount1, CustomAmount2, CustomAmount3,
CustomAmount4, CustomAmount5, PhotoPath, X12Setup 
FROM qry_dbo_MissingResidents;

Open in new window

Most Valuable Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your responses jimpen.  You're right, I didn't create that table, but the info you shared should prove helpful down the road!  :)
Most Valuable Expert 2014

Commented:
That works. Have a good day.

Commented:
I have seen this when a table was there and have answered a similar question. If the table doesn't show up, just type in the table name and then everything is OK.

Jim

Author

Commented:
Thanks Jim.

The "backwards" way was not I was looking for.  I was looking for the how to get this to work correctly, and why it wasn't.  Good thing too...it was a more serious issue with SP3 than I had known about!
Most Valuable Expert 2014

Commented:
Glad to be of assistance. May all your days get brighter and brighter.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.