Losing Records

[Martin]

I have several tables. One is a list of Physicians, the others are detailing
things like their offices, specialties, etc. The Physician table is in a
one-to-many relationship with each of the supporting tables. A given
physician may or may not have data in the supporting tables, but will always
be in the Physician table.

In Design view, when I add a supporting table that does not contain
record(s) for a given physician, the query drops that physician entirely. I
would like it to continue to pull the information from the other supporting
tables, just leave the data blank from the table that has no data for that
physician.

I have tried changing the Join Type between the Physician table and the
supporting table that I am adding to the query, but it does not have any
affect on the results of the query. I have tried removing Referential
Integrity on the join as well with no affect.

Can someone give me some ideas on how to solve this?

Thanks.

 

[Allen Browne]

I think you were talking about changing the Join Type in the Relationships
window? All that does is set the default join type for new queries.

Open your query, and double-click the line joining the 2 tables in the upper
pane of query design. Choose the option that says:
All records from Physicans, and any matches from ...

Also, be careful if you add any criteria under the fields from the related
table. Since the related fields will be null when there is no match, any
criteria will exclude them unless you explicitly include the nulls. For
example, if the criteria is:

3

you would nee to use:
Is Null Or > 3

More info:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Jerry Whittle]

Very strange as changing the Join Type should do the trick. Show us the SQL.
Open the query in design view. Next go to View, SQL View and copy and past it
here. Information on primary keys and relationships would be a nice touch too.

 

[Martin]

I was changing Join Type in Relationship which makes senes that there was no
affect on the query(as you suggest).

I tried changing the Join Type in the query itself, but only for the
supporting table that seemed to be causing the loss of records. That change
caused an error message about ambiguous outer joins. So I changed all the
Join Types for all the supporting tables in the query with the same result.

Then I changed the Join Type between the supporting table(s) and tables that
provide descriptions to those tables [I forgot to include these in my
original message]. First I tried Type 2, but the amibiguous join error came
up. Then I changed to the Type 3. Now the query seems to be working.

It seems that this extra set of tables was causing most of the problem.

Thanks for the help.

 

[Acutie]

I am currently having the same problem, except that all tables include data
on all (in this case) patient IDs. I can't fathom why a query would drop
records?

Hawley

 

[Acutie]

SELECT [Patient Info].ID, [Patient Info].Gender, [Patient Info].Genotype,
[Patient Info].[Recruit Date], [Patient Info].[Recruit Location], [Patient
Info].[Stage of Infection] AS [Patient Info_Stage of Infection],
Stages.[Stage of Infection] AS [Stages_Stage of Infection], [Patient
Info].Notes, [Patient Progression].Visit, [Patient Progression].Date
FROM (Stages INNER JOIN [Patient Info] ON Stages.Abbreviation = [Patient
Info].[Stage of Infection]) INNER JOIN [Patient Progression] ON [Patient
Info].ID = [Patient Progression].[Pt ID];

 

[Jerry Whittle]

Hi,

You have an inner join between the two tables. If you don't have a matching
record in both tables data from either table will not show up. In query
design mode double click on the line between the two tables until a dialog
box shows up. Select one of the two other options and see what happens.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SELECT [Patient Info].ID, [Patient Info].Gender, [Patient Info].Genotype,
[Patient Info].[Recruit Date], [Patient Info].[Recruit Location], [Patient
Info].[Stage of Infection] AS [Patient Info_Stage of Infection],
Stages.[Stage of Infection] AS [Stages_Stage of Infection], [Patient
Info].Notes, [Patient Progression].Visit, [Patient Progression].Date
FROM (Stages INNER JOIN [Patient Info] ON Stages.Abbreviation = [Patient
Info].[Stage of Infection]) INNER JOIN [Patient Progression] ON [Patient
Info].ID = [Patient Progression].[Pt ID];

Very strange as changing the Join Type should do the trick. Show us the SQL.
Open the query in design view. Next go to View, SQL View and copy and past it
here. Information on primary keys and relationships would be a nice touch too.
--

 

[Allen Browne]

As Jerry said, you need to use outer joins.

See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SELECT [Patient Info].ID, [Patient Info].Gender, [Patient Info].Genotype,
[Patient Info].[Recruit Date], [Patient Info].[Recruit Location], [Patient
Info].[Stage of Infection] AS [Patient Info_Stage of Infection],
Stages.[Stage of Infection] AS [Stages_Stage of Infection], [Patient
Info].Notes, [Patient Progression].Visit, [Patient Progression].Date
FROM (Stages INNER JOIN [Patient Info] ON Stages.Abbreviation = [Patient
Info].[Stage of Infection]) INNER JOIN [Patient Progression] ON [Patient
Info].ID = [Patient Progression].[Pt ID];

Very strange as changing the Join Type should do the trick. Show us the
SQL.
Open the query in design view. Next go to View, SQL View and copy and
past it
here. Information on primary keys and relationships would be a nice touch
too.