query does not display all records

[MStadnik]

I have a basic query that should return all records in the database (I only
have 9 records thus far for testing purposes). It asks for data from fields
that each record has data for. Problem is that I'm only getting 8 of the
records returned when I run the query. Thinking it might be a problem with my
original query, I re-created it using the wizard - there is no criteria
selected for any of the fields. The ONLY thing that I can see as a potential
problem is that the record that is not being returned has the same last name
as one of the other records - they have different IDs (primary keys),
different first names, etc. I would hope that having the same last name is
not the problem as this is likely to come up over and over again as
"families" tend to utilize the program. Does anyone have any clue as to what
is causing this problem... is there any other info you need to help me? I'm
using Access 2007 if that makes a difference. Thanks!

MStadnik

 

[MStadnik]

Hi Chris,
Thanks for responding. Here is the SQL code: SELECT
Participants.ParticipantID, Participants.FirstName, Participants.MI,
Participants.LastName, Participants.DateofAdmission,
Participants.DateofDischarge, Participants.ReasonforDischarge,
Participants.ReasonforDischargeDesc, Participants.DateofFileDestruction,
FundingCaseMgmt.PrimaryFundingSource
FROM Participants INNER JOIN FundingCaseMgmt ON Participants.[ParticipantID]
= FundingCaseMgmt.[ParticipantID];

I'm guessing it has to do with the "select" - this is the default value I
got when designing the query... am I supposed to tell it to "select all"? If
so, how do I put that into the statement since I did try that and it made no
difference? Thanks.

MStadnik

 

[MStadnik]

Ah... I fouind the problem - the participant ID number was in all the
records, BUT there is also a field to select the participant - in a couple of
subforms that info was missing - including the "funding" form that is used in
this query. Once I fixed that, the record displayed when I ran the query.
Thanks for your help!

Your select clause is good. No problem there. What's limiting the final
number of records is the join on the two tables. It's only displaying the
records with matching ParticipantIDs in both tables. One of your tables only
has 8 of the same ParticipantIDs, while the other table has 9. Put the
missing ParticipantID in a record in the table that only has 8 and the
resulting query will have 9 records.

Chris
Microsoft MVP

Hi Chris,
Thanks for responding. Here is the SQL code: SELECT
Participants.ParticipantID, Participants.FirstName, Participants.MI,
Participants.LastName, Participants.DateofAdmission,
Participants.DateofDischarge, Participants.ReasonforDischarge,
Participants.ReasonforDischargeDesc, Participants.DateofFileDestruction,
FundingCaseMgmt.PrimaryFundingSource
FROM Participants INNER JOIN FundingCaseMgmt ON Participants.[ParticipantID]
= FundingCaseMgmt.[ParticipantID];

I'm guessing it has to do with the "select" - this is the default value I
got when designing the query... am I supposed to tell it to "select all"? If
so, how do I put that into the statement since I did try that and it made no
difference? Thanks.

MStadnik

It could be a number of things causing it. We can help you faster if we can
see the query instead of guessing. Please post the query's SQL code.

[quoted text clipped - 17 lines]

MStadnik