Append Query Doesn't List Table in the Drop-Down
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)
4. I then refined the query (in order to remove a like-named-field) - (qry_dboMissingResidentsTo
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
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)
4. I then refined the query (in order to remove a like-named-field) - (qry_dboMissingResidentsTo
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
ASKER
The SQL View from the qry_dbo_MissingResidentsTo
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;
ASKER
I've attached a JPEG on the relationships view as well.
Relationships.JPG
Relationships.JPG
ASKER
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?
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?
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.
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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! :)
That works. Have a good day.
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
Jim
ASKER
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!
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!
Glad to be of assistance. May all your days get brighter and brighter.
Click it and it should bring up a bunch of text. Can you copy it an paste it here?