I added tables to a split Database and when I open and save any query it looses all the records

[Duncan Pucher]

I split a database and the queries worked. I later added tables and found that if I open and save an existing query that it can't find any records. I'm not sure what is happening and I am not sure it is related to the process of adding tables. I used the below method to add tables.

1. Go to the tab labeled "External Data" on the ribbon.
2. Select "Access." The "Get External Data - Access Database" dialog will be displayed.
3. Click [Browse]. The "File Open" dialog will be displayed.
4. Locate the path for your back-end database and select the file. The file name should appear in the text box below.
5. Click [Open]. You will be returned to the "Get External Data - Access Database" dialog.
6. On the "Get External Data - Access Database" dialog, click the option titled: "Link to the data source by creating a linked table."
7. Click [OK].
8. In the next dialog, select the tables you want to link, then click [OK].

If I open any query such as "ActiveResidentqry" and change anything then save and reopen, it shows no data. I have to use an old copy of the query to restore it. Could anyone show me what I am doing to make this happen?

Thank you,

[June7]

What exactly do you change?

I opened that query. Changed "Donald" to "D", closed, reopened - data is there.

I see some room numbers have been duplicated in RoomNumbersTbl.

[Duncan Pucher]

June,

Thanks for looking at the table. I meant to say if you change a field. What I want to do is add the new table "ClinicalCategoriestbl" to the ActiveResidentqry so I can create a new report. When I add an item or make any field change the SQL changes and somehow filters out all records. Can you see what is going on?

Thanks for pointing out the duplicate rooms. I will delete them.

[June7]

Problem is using INNER JOIN on table links. Change links to RIGHT JOIN (show all records from Residenttbl…)

[Duncan Pucher]

June,

It worked. Thank you. I don't understand JOINS at this point but plan to learn. I wanted to know if both references to JOINs were changed or only one. It seems to work both ways. Below is the change I made in bold font. Do I change both occurences or only one?

SELECT Residenttbl.RecNum, Residenttbl.CareReviews_FK, Residenttbl.Lastname, Residenttbl.Firstname, Residenttbl.RoomNum_FK, Residenttbl.AdmitDate, Residenttbl.[100thDateDate], Residenttbl.Physician_FK, Residenttbl.DOB, Residenttbl.Payer_FK, Residenttbl.DiabeticStatus, Residenttbl.SafetyItems, Residenttbl.PrimaryDiagnosis, Residenttbl.PrimaryComorbidities, Residenttbl.MajorSurgicalProcedure, Residenttbl.SCStartDate, Residenttbl.SCEndDate, Residenttbl.SCReason, Residenttbl.ShiftsSC_FK, Residenttbl.ACStartDate, Residenttbl.ACEndDate, Residenttbl.ACReason, Residenttbl.ShiftAC_FK, Residenttbl.ActiveInactive, RoomNumberstbl.Room, Residenttbl.ClinicalCategory_FK
FROM RoomNumberstbl RIGHT JOIN (ClinicalCategoriestbl RIGHT JOIN Residenttbl ON ClinicalCategoriestbl.ClinicalCategory_PK = Residenttbl.ClinicalCategory_FK) ON RoomNumberstbl.RoomNumber_PK = Residenttbl.RoomNum_FK
ORDER BY RoomNumberstbl.Room;

[June7]

I changed both but if one satisfies, then fine but if you added a third lookup table, issue might recur.

[Duncan Pucher]

June,

I appreciate the help. Thank you,