simple query not working

  • Thread starter Thread starter richaluft
  • Start date Start date
R

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
 
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
 
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.
Click to expand...
Click to expand...

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?
 
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.
Click to expand...
Click to expand...

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?
Click to expand...
 
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.
Click to expand...
Click to expand...
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.
Click to expand...
Any other suggestions?
Click to expand...
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cross checking fields to find problem entries 9
Current record OR last EndDate 8
Sum of Query not reporting zero values 3
Updatable Query (trying to edit records) 8
Update another table with a Max record query 2
Here's the Query I'm having trouble with. 2
WHERE clause 5
Sum in Report Errors 3

Back
Top