simple query not working

[richaluft]

Hi:
Trying to set up a simple query, and can't figure out why its not
working. Hope someone can help me think a little more clearly.
Trying to link two tables:
1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
Join is 'SvcCode'
Query design is to show ClientID, DateofSvc, SvcCode, and
SvcDescription.
SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
tblCodes.SvcCode, tblCodes.Description
FROM tblSERVICES INNER JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode
WHERE (((SERVICES.[ClientID#])=964));
For some reason that I can't figure out, this query only gives ONE
result, even though it should show three services for this particular
client.
Suggestions for correction are appreciated!
Richard

 

[Allen Browne]

Temporarily remove tblCodes from the query.
Does it show 3 rows for client 964 now?
The 2 rows that did not show before -- is the SvcCode blank?

If so, you need to use an outer join in your query. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[richaluft]

Temporarily remove tblCodes from the query.
Does it show 3 rows for client 964 now?
The 2 rows that did not show before -- is the SvcCode blank?

If so, you need to use an outer join in your query. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Trying to set up a simple query, and can't figure out why its not
working. Hope someone can help me think a little more clearly.
Trying to link two tables:
1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
Join is 'SvcCode'
Query design is to show ClientID, DateofSvc, SvcCode, and
SvcDescription.
SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
tblCodes.SvcCode, tblCodes.Description
FROM tblSERVICES INNER JOIN tblCodes
ON tblSERVICES.SvcCode = tblCodes.SvcCode
WHERE (((SERVICES.[ClientID#])=964));
For some reason that I can't figure out, this query only gives ONE
result, even though it should show three services for this particular
client.

Alan:
Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
I get NO Codes showing ( but I do show 3 events)
If I includetblServices.SvcCode, then of course I get all (3) items
returned.
I've tried outer joins before, but they return the same problem.
For example, join as follows:
"tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode" yields three rows, only one of which shows the
SvcCode!
If I do a right join, then I only get a single row returned, complete
with SvcCode.

Any other suggestions?

 

[Allen Browne]

Your original post says that tblServices has a field named SvcCode.

I suggest you locate the 2 other rows that should be showing for client 964,
and look at what is in SvcCode.

Either tblServices.SvcCode is blank in those 2 rows, or else it contains
invalid codes (i.e. codes that don't match any of the entries on
tblCodes.SvcCode.)

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

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

Temporarily remove tblCodes from the query.
Does it show 3 rows for client 964 now?
The 2 rows that did not show before -- is the SvcCode blank?

If so, you need to use an outer join in your query. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Trying to set up a simple query, and can't figure out why its not
working. Hope someone can help me think a little more clearly.
Trying to link two tables:
1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
Join is 'SvcCode'
Query design is to show ClientID, DateofSvc, SvcCode, and
SvcDescription.
SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
tblCodes.SvcCode, tblCodes.Description
FROM tblSERVICES INNER JOIN tblCodes
ON tblSERVICES.SvcCode = tblCodes.SvcCode
WHERE (((SERVICES.[ClientID#])=964));
For some reason that I can't figure out, this query only gives ONE
result, even though it should show three services for this particular
client.

Alan:
Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
I get NO Codes showing ( but I do show 3 events)
If I includetblServices.SvcCode, then of course I get all (3) items
returned.
I've tried outer joins before, but they return the same problem.
For example, join as follows:
"tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode" yields three rows, only one of which shows the
SvcCode!
If I do a right join, then I only get a single row returned, complete
with SvcCode.

Any other suggestions?

 

[richaluft]

Your original post says that tblServices has a field named SvcCode.

I suggest you locate the 2 other rows that should be showing for client 964,
and look at what is in SvcCode.

Either tblServices.SvcCode is blank in those 2 rows, or else it contains
invalid codes (i.e. codes that don't match any of the entries on
tblCodes.SvcCode.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Temporarily remove tblCodes from the query.
Does it show 3 rows for client 964 now?
The 2 rows that did not show before -- is the SvcCode blank?
If so, you need to use an outer join in your query. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Trying to set up a simple query, and can't figure out why its not
working. Hope someone can help me think a little more clearly.
Trying to link two tables:
1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode'
2)tblCodes, with fields 'SvcCode', and 'SvcDescription'
Join is 'SvcCode'
Query design is to show ClientID, DateofSvc, SvcCode, and
SvcDescription.
SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc,
tblCodes.SvcCode, tblCodes.Description
FROM tblSERVICES INNER JOIN tblCodes
ON tblSERVICES.SvcCode = tblCodes.SvcCode
WHERE (((SERVICES.[ClientID#])=964));
For some reason that I can't figure out, this query only gives ONE
result, even though it should show three services for this particular
client.

Alan:
Since I'm using tblCodes.SvcCode, if I remove tblCodes from the query
I get NO Codes showing ( but I do show 3 events)
If I includetblServices.SvcCode, then of course I get all (3) items
returned.
I've tried outer joins before, but they return the same problem.
For example, join as follows:
"tblSERVICES LEFT JOIN tblCodes ON tblSERVICES.SvcCode =
tblCodes.SvcCode" yields three rows, only one of which shows the
SvcCode!
If I do a right join, then I only get a single row returned, complete
with SvcCode.

Any other suggestions?

Allen:
Such a simple explanation!
I forgot to update TblCodes with some new coding entries about 2 yrs
ago, and never had reason to use these codes.
Thanks again,
Richard