Problem with Relationships and a Query Not Showing all records 1

[jamsmom]

Hi there! I have a number of tables in my database. The main table is called Clients with a ClientID set as the primary key (autonumber). I have an number of tables such as Mobility, Communications and Assistive. Each of these tables has the ClientID field in them (set as number) and a ID field of their own (set with autonumber). In the relationships box I have each of them connecting to the main table via the ClientID field. Seemed to work great. Here's my problem. I created a query that shows all four tables. I pull down ONLY the clientID field from the Clients table and pull ALL field (except for the ClientID field) from the other three tables. It seemed to work great but what I just discovered is that it is not bringing me back complete data. For example, the Assistive table only has 29 records in it, with the communication table having 78 and the mobility table having 70. When I place all three in the query it only shows me the 29 entries from the Assistive table. It doesn't seem to matter which two or more I put in the query - it will only actually bring me back records from one of the tables. This is a HUGE problem! I'm completely stumped and have to have it working tomorrow!

 

[scriverb]

Please post your queries SQL so we may see what is going on. What I am interested in is what type of join your have created in your relationship which ultimately is showing up in your query.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jamsmom]

Here's the SQL code from in the query:

SELECT [first]+" "+[last] AS name, Clients.ClientID, Clients.First, Clients.Last, Mobility.TotallyIndependent, Mobility.OccassionalAssistance, Mobility.AlwaysAssistance, Mobility.IndependentFlatOnly, Mobility.WalksWith, Mobility.TiresEasy, Mobility.WheelchairAll, Mobility.WheelchairDistance, Mobility.TransferIndependent, Mobility.TransferAssistance, Mobility.TransferAssistanceType, Mobility.NoTransfer, Mobility.Harness, Mobility.HarnessSize, Mobility.SpecificInfoMobility, Mobility.MobilityID, AssistiveDevices.Glasses, AssistiveDevices.ContactLenses, AssistiveDevices.ProstheticDevice, AssistiveDevices.Wheelchair, AssistiveDevices.HearingAid, AssistiveDevices.OrthopedicDevices, AssistiveDevices.Dentures, AssistiveDevices.[Crutches/Cane], AssistiveDevices.SpecificInfo, AssistiveDevices.DeviceID, Communication.Speakfluently, Communication.Understood, Communication.Nonverbal, Communication.SignLanguage, Communication.NoCommunication, Communication.GoodAuditoryProcessing, Communication.CommunicationBoard, Communication.Communicationboarddetail, Communication.StepDirections, Communication.CommunicationSituations, Communication.CommunicationID, Communication.Reads, Communication.Writes, Communication.Speaks, Communication.UnderstandSpeech, Communication.UnderstandDirection, Communication.Gestures
FROM ((Clients INNER JOIN AssistiveDevices ON Clients.ClientID = AssistiveDevices.ClientID) INNER JOIN Communication ON Clients.ClientID = Communication.ClientID) INNER JOIN Mobility ON Clients.ClientID = Mobility.ClientID;

 

[scriverb]

Try changing your joins to left joins:

FROM ((Clients LEFT JOIN AssistiveDevices ON Clients.ClientID = AssistiveDevices.ClientID) LEFT JOIN Communication ON Clients.ClientID = Communication.ClientID) LEFT JOIN Mobility ON Clients.ClientID = Mobility.ClientID;

Let me know how this works.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jamsmom]

That did it! Amazing! Why did that do it? What is the difference?

 

[scriverb]

Inner joins between two tables mean that you will only get a row if there is a matching record in BOTH tables. The LEFT JOIN gives you a row for ever record on the left side of the join and displays data if it is there in a matching record on the right side of the join. So, if anything is missing in any of the tables on the right side of your INNER joins then you weren't going to get that row displayed. The left join fixes that issue.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jamsmom]

Thank you so MUCH! My users are thrilled to have it working again. Thanks for the lesson - I will definitely remember that tip!