Joins in query

[Danny]

Greetings,

Given my tables tblSuspension and tblSuspStat I tried to create a query to show open and close suspension cases. SUSPSTAT in tblSUSPENSION is a numeric data type (1 & 2) whereas STATUS in tbl SUSPSTAT is a text data type (close & open). It was requested to display the text value (instead of the numeric values) in the report. There are a total of 150 cases both open & close.
When I run the query based on tblSuspension, I get 150 records, but when I include the tblSuspstat and run the query, I get only 85 records.

Attached is the screenshot to show the joins Can you please point me to the right direction as to why I do not get the consistent result?

TIA

Regards,

 

[theDBguy]

Hi. It tells me you have some cases in limbo, neither open nor close, probably empty.

 

[isladogs]

Try using an outer join (option 2) to get all records from the 1 side of the join.

If that doesn't help, change the query to SQL view and post the code

BTW have you blanked out most of the fields in tblSuspension?

 

[pbaldy]

There are likely records that don't have a matching value. Change the join to the third option and you should get all records, and you can see what values aren't matching.

 

[Danny]

Thank you everyone for your input.
TheDBguy, you are correct in that few cases were empty (status = blank). This explains why I get fewer records every time I added a new table to the join.
Also, I tried changing the join to the 2nd and 3rd option, but got the following error:
“The SQL statement could not be executed because it contains ambiguous order joins…”
I got the same error when I tried to change the query ot SQL view as well.

Isladogs: Yes, I did blank out most of the fields in tblSuspension

TIA

Regards,

 

[isladogs]

“The SQL statement could not be executed because it contains ambiguous order joins…”
I got the same error when I tried to change the query ot SQL view as well.

You must have other tables as well in your query as you need at least 3 tables to get that error.
Have a look at this article about different types of query join including the causes of the ambiguous joins issue: http://www.mendipdatasystems.co.uk/query-join-types/4594517491

If you are still stuck after that, post the query in design view and/or SQL view if it doesn't error

 

[Danny]

Collin, thanks for the useful link. Actually, I’ve only the two tables I’ll try your suggestions and post back if I get stuck.

Regards,

 

[isladogs]

Really!!!?
How can a join on 2 tables be ambiguous?
Have you done something silly like this?

Can you show a screenshot of the query in design view and, if possible, upload the code in SQL view

 

[Danny]

Colin,
In the attached screenshot:
1. When the default (1st) option is selected, the query run fine & I was able to access SQL view.
2. When I selected the 2nd or the 3rd options, the query didn't run & I was unable to access the SQL view. Instead, I got the error.

Please let me know if the attached screenshot helps.

TIA

Regards,

 

[isladogs]

How very strange.
Its showing the standard ambiguous joins message on one join and suggesting you do one join before the non existent other join!

You've either found an obscure bug in Access (very unlikely) or there's something odd about the 2 tables.
Are the datatype of the two linked fields identical?
Does the SUSPSTAT involve a lookup field at table level? (bad idea)
Is there anything unusual about all the fields whose names you've hidden?

If none of those help you solve it, I doubt we can solve it unless we see a copy of this part of your database with some representative data to illustrate the issue. It doesn't need to be real data.

 

[Danny]

Are the datatype of the two linked fields identical?

tblSUSPENSION
Field Name: SUSPSTAT
Data Type: Number
Value: 1,2

tbsSUSPSTAT
Field Name: STATUS
Data Type: Short Text
Value: open, closed

Does the SUSPSTAT involve a lookup field at table level? (bad idea)

Someone else designed the DB (no documentation). How can I check to see if this is the case?

TIA

Regards,

 

[isladogs]

So you are trying to join a number field and a text field.
How can that work?

For you to have any success, there is almost certainly a lookup on SUSPSTAT.
Do you see a drop down on that field when you look at the table? Yes =lookup.
Look at the field in table design view. Does it have something in the row source property on the lookup tab?

EDIT.
You actually mentioned the different datatypes in post #1.
But you are joining by the ID field not the STATUS field.

 

[Danny]

Unless I'm looking in the wrong place, attached is what I see while in table design view. I also included datatsheet view for tblSUSPSTAT.

TIA

Regards,

 

[isladogs]

You will only see the row source property if the display control is combobox or listbox. Change it temporarily and see if a row source is present. If so, you can almost certainly delete it.

However the STATUS field being text is irrelevant.
Your query links two number fields ID and SUSPSTAT.

 

[Danny]

I changed it to list box and nothing showing for row source. And, row Source Type: Table/Query.

TIA

Regards,

 

[isladogs]

Good. Now change it back to textbox
Now we need to see something more to solve this problem for you.
As discussed earlier, please can you upload something for us to look at

 

[Danny]

Colin,

Thank you. I can ask for permission from my supervisor and try to put something together for you to take a look.

Is there instruction and/or sample db to show how to post a sample db?

Regards,

 

[pbaldy]

https://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[isladogs]

I can ask for permission from my supervisor and try to put something together for you to take a look.

Is there instruction and/or sample db to show how to post a sample db?

OK - it won't necessarily be me that looks at your database. In fact several people may offer opinions which is better.

To upload:

1. Make a backup copy of your database.
2. Remove all items not relevant to the current issue.
3. Remove most records from any remaining tables but leave enough to ensure the issues can be observed.
4. Debug your database & check it compiles. If not, fix all compile errors
5. Compact
6. Zip & upload

When you do so, explain clearly what we need to do to observe the issues

EDIT:
Somehow I missed the link that Paul posted though I looked in various places before replying :banghead:

 

[Danny]

Hi,

After reading the 'Query Join Types' link provided in this forum, I revised my query to get the expected result.

Following is what I did:

1. deleted the default join
2. dragged SUSPSTAT from left table to the ID field in the right table
3. edited the join property and selected the second option
4. run my query to get the expected result

Thank you everyone for your feedback.

Regards,