Invalid Query Results (1 Viewer)

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
Okay, I admit I've had a long day and that my eyes and brain need a break. But -
If Table1 has 2 entries each for Cust_Id (600145 & 600136), Payer (MLTSS) and Therapy (Structured Day) : {Total entries = 4} and
If Table 2 has 2 entries each for Cust_Id (600145 & 600136), Payer (MLTSS) and Therapy (Structured Day) : {Total entries = 4}
How could Query 1 return 8 records if the tables are joined on cust_id, Therapy and Payer?
See attached please.
 

Attachments

  • InvalidQueryResults.accdb
    688 KB · Views: 45

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:49
Joined
Feb 28, 2001
Messages
27,488
In your tables you have:

Table 1: two records each for 600145 and for 600136, each with MLTSS and Structured Day
Table 2: two records each for 600145 and for 600136, each with MLTSS and Structured Day

But you don't have an internal record ID to show you what happened. You ran afoul of non-unique entries, and Access ACE engine - like other set-oriented SQL engines - is a COMBINATIONS machine. Your result is four records each because you have other fields you didn't use that would have split the overlap. If you ALSO joined on the Original_UnitsBilled, you would have seen the difference. ALSO, I'm not sure WHY you have table 2 in that mix anyway, since the query doesn't return anything from table 2. BUT if you had included Table2.Original_UnitsBilled NEXT TO Table1.Original_UnitsBilled, you would have seen the combination engine doing its thing.

Basically, what you showed us is correct behavior given the SQL you specified.
 

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
Hi Doc_Man,

I just included every other field available and still got 8 records.
Also, I can't join Original_UnitsBilled to the other table because it doesn't exist there.
I've never seen this happen before when the joins are correct. Is this a bug in Access?

Thanks,
TS
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
8,963
Okay, I admit I've had a long day and that my eyes and brain need a break. But -
If Table1 has 2 entries each for Cust_Id (600145 & 600136), Payer (MLTSS) and Therapy (Structured Day) : {Total entries = 4} and
If Table 2 has 2 entries each for Cust_Id (600145 & 600136), Payer (MLTSS) and Therapy (Structured Day) : {Total entries = 4}
How could Query 1 return 8 records if the tables are joined on cust_id, Therapy and Payer?
See attached please.

What result are you expecting?
 

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
If each table has 4 records that have the same Cust_id, Payer and Therapy (Structured Day) - and the tables are linked on Cust_Id, Payer and Therapy then I would expect 4 records to be returned.
 

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
In other words, I would expect this:
PayerClientNameClient_IDTherapySumOfNumber_UnitsBilledBilled_ByRes_OutDate_Of_ServiceOriginal_UnitsBilled
MLTSSConn, Roy600145Structured Day-6Yashica WalkerResidential01-Dec-238
MLTSSConn, Roy600145Structured Day-6Yashica WalkerResidential01-Dec-2310
MLTSSRuez, Carl600136Structured Day35Jesusa OrganistaOutpatient01-Dec-2317
MLTSSRuez, Carl600136Structured Day35Qwana WilliamsOutpatient01-Dec-2318
 

XPS35

Active member
Local time
Today, 09:49
Joined
Jul 19, 2022
Messages
163
Every four of the records in table 1 that has matching records in table 2 has two matching records there. Every combination is shown.
Add DISTINCT to your query to eliminate duplicates:
Code:
SELECT DISTINCT Table1.Payer, Table1.ClientName,...............
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
8,963
You're going to get 4 for every distinct combination and the ID # changes..
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
8,963
and then think about table redesign!

Ideally any time I am joining tables, I'm ALMOST always joining on one to many. Many to many is one of those things much mused and pontificated about in Access forums but very rarely required in real life, IMO. I've worked for >10 major corporations and have yet to come across more than 1 or 2 of these...ever.

80% of the times you'll want to use Distinct indicate either an area of query improvement exists, or table design improvement. Hopefully query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:49
Joined
Feb 28, 2001
Messages
27,488
In other words, I would expect this:
PayerClientNameClient_IDTherapySumOfNumber_UnitsBilledBilled_ByRes_OutDate_Of_ServiceOriginal_UnitsBilled
MLTSSConn, Roy600145Structured Day-6Yashica WalkerResidential01-Dec-238
MLTSSConn, Roy600145Structured Day-6Yashica WalkerResidential01-Dec-2310
MLTSSRuez, Carl600136Structured Day35Jesusa OrganistaOutpatient01-Dec-2317
MLTSSRuez, Carl600136Structured Day35Qwana WilliamsOutpatient01-Dec-2318

As I said earlier, the fly in the ointment is that you didn't use Original_UnitsBilled as a way to further correlate the query. Based on the theory that if it appears in a JOIN ... ON sequence it must be a key field in that table, you have TWO records in one table and TWO records in the other table that match all three of your named (implied) keys: Payer, Client_ID, and Therapy. Yet they have two different values for Original_UnitsBilled, so to Access they are distinct records. If you had included a single-field key such as an autonumber, then included each record's autonumber key, you would have seen pairings.

Let me state it another way. What Access does is it looks at the first record in table 2 to match the "ON" fields of table 1. It returns a result. Now it checks for another record in table 2 that matches the "ON" fields of table 1. AND IT FINDS ONE! But then it runs out of matches. SO... it looks at the next record in table 1 and finds table 2's matching fields... AND IT FINDS TWO MORE to be returned. Then it checks for the NEXT client ID that matches the PAYER and Therapy and it finds a match in table 1. So... like the above, it finds a total of FOUR COMBINATIONS that match the second pair of records. That is how you get eight records.

So the next question is this: WHY DID YOU BOTHER to include table 2? You specified Payer and Therapy. If you had simply selected from table 1 by itself and used Payer and Therapy in a WHERE clause, there would have been no duplication.

Think of it THIS way: If you have a one-to-many relationship, you write a query to return the fields from table 1 and EVERY MATCHING RECORD from table 2. Well, this is no different except that in the one-many case, you have only one record on the table 1 side of the query. Here, you had TWO records on the table 1 side. Does this help?
 

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
Hi Doc_Man -always appreciate your help. I used Table2 because there were two fields in it which I used in the query. However, when I created the test db and pasted the SQL into it those fields were accidently deleted. Sorry about that.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:49
Joined
Feb 28, 2001
Messages
27,488
Hi Doc_Man -always appreciate your help. I used Table2 because there were two fields in it which I used in the query. However, when I created the test db and pasted the SQL into it those fields were accidently deleted. Sorry about that.

No problem (for me). But do you understand the point I was making? Your query did EXACTLY what Access ALWAYS does. It is not a malfunction of Access, but a design/data issue.
 

TheSearcher

Registered User.
Local time
Today, 03:49
Joined
Jul 21, 2011
Messages
304
Hi Doc_Man - Sorry for the late reply. Yes I understand exactly what you stated. I appreciate your clarity.
 

Users who are viewing this thread

Top Bottom