Imoutwest
asked on
Access Query - Using Inner Join Query Doubles the Number of Matching Records
Good afternoon, this is a follow on question to a Top N query that I was working on (and solved here). However, I can't identify what I have wrong with the query because it doubles the number of matching records (i.e. should be 12 (CountOfRx) and returns 24). Which in turn doubles the SubTotal $.
Original Question:
https://www.experts-exchange.com/questions/28262542/How-Top-N-works-when-joining-multiple-tables-List-the-Top-5-Costs-for-Each-Department.html
I tried using Distinct, but didn't change results.
Database Relationship:
tblCHCS_Dispensed.Drug_IEN
tblCHCS_Dispensed.MEPRS to subqryMEPRS_Identification
Tables:
tblNDCRelationships (4000 records)
IEN
UM_Cost
tblCHCS_Dispensed (100,000 records)
Drug_IEN
QTY
MEPRS – Location Field
subqryMEPRS_Identification
DEPARTMENT
MEPRS
This query makes a table that is used to compute the TopN.
SELECT DISTINCT subqryMEPRS_Identification
FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN
GROUP BY subqryMEPRS_Identification
HAVING (((subqryMEPRS_Identificat
ORDER BY subqryMEPRS_Identification
Original Question:
https://www.experts-exchange.com/questions/28262542/How-Top-N-works-when-joining-multiple-tables-List-the-Top-5-Costs-for-Each-Department.html
I tried using Distinct, but didn't change results.
Database Relationship:
tblCHCS_Dispensed.Drug_IEN
tblCHCS_Dispensed.MEPRS to subqryMEPRS_Identification
Tables:
tblNDCRelationships (4000 records)
IEN
UM_Cost
tblCHCS_Dispensed (100,000 records)
Drug_IEN
QTY
MEPRS – Location Field
subqryMEPRS_Identification
DEPARTMENT
MEPRS
This query makes a table that is used to compute the TopN.
SELECT DISTINCT subqryMEPRS_Identification
FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN
GROUP BY subqryMEPRS_Identification
HAVING (((subqryMEPRS_Identificat
ORDER BY subqryMEPRS_Identification
As Imoutwest suggested, check to see if left table has no duplicate selected field in place of primary key.
hnasr,
Imoutwest is the author of this question.
Imoutwest,
--->> has two matching Clinics
Does it matter which clinic is reported? Ie: are the field values in the duplicate records identical with the exception of the clinic?
From what you have posted, I can't tell which table or query clinic comes from, but you might be able to resolve it by specifying 'first', 'last' (or some other aggregate function) for clinic (see the bolded text):
SELECT DISTINCT subqryMEPRS_Identification
Imoutwest is the author of this question.
Imoutwest,
--->> has two matching Clinics
Does it matter which clinic is reported? Ie: are the field values in the duplicate records identical with the exception of the clinic?
From what you have posted, I can't tell which table or query clinic comes from, but you might be able to resolve it by specifying 'first', 'last' (or some other aggregate function) for clinic (see the bolded text):
SELECT DISTINCT subqryMEPRS_Identification
ASKER
mbizup, I know that I haven’t explained this well; so I’ll try again.
The largest table has the MEPRS code only; through the subqryMEPRS_Identification
The query listed doesn’t include the Clinic, nothing changes if I add it.
The largest table has the MEPRS code only; through the subqryMEPRS_Identification
The query listed doesn’t include the Clinic, nothing changes if I add it.
At what point do the duplicates show up?
Are the duplicates in subqryMEPRS_Identification
Can you post a sample with the relevant tables and queries, and a few records illustrating this? The actual data is not important and can be replaced with junk if it is sensitive -- just something to show the tables/relationships and problem.
Are the duplicates in subqryMEPRS_Identification
Can you post a sample with the relevant tables and queries, and a few records illustrating this? The actual data is not important and can be replaced with junk if it is sensitive -- just something to show the tables/relationships and problem.
ASKER
Hello again, I think my problem is the subsryMEPRS_Identification
if i add FIRST to this query before the other queries run, I am receving the data I want. The question this brings up is how do I determine if I am viewing the CORRECT data for the query.
SELECT tblMEPRS_Identification.DE
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DE
HAVING (((tblMEPRS_Identification
ORDER BY tblMEPRS_Identification.DE
if i add FIRST to this query before the other queries run, I am receving the data I want. The question this brings up is how do I determine if I am viewing the CORRECT data for the query.
SELECT tblMEPRS_Identification.DE
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DE
HAVING (((tblMEPRS_Identification
ORDER BY tblMEPRS_Identification.DE
Do you need the clinic info at all?
What happens if you leave off the clinic and use the DISTINCT keyword?
SELECT DISTINCT tblMEPRS_Identification.DE
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DE
HAVING (((tblMEPRS_Identification
ORDER BY tblMEPRS_Identification.DE
Or this?
It also looks like your outer query is not using Department at all, so can you omit that?
What happens if you leave off the clinic and use the DISTINCT keyword?
SELECT DISTINCT tblMEPRS_Identification.DE
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DE
HAVING (((tblMEPRS_Identification
ORDER BY tblMEPRS_Identification.DE
Or this?
SELECT DISTINCT tblMEPRS_Identification.DEPARTMENT, tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
FROM tblMEPRS_Identification
tblMEPRS_Identification.MEPRS
WHERE (((tblMEPRS_Identification.DMIS) In ("0125","1480","1485","1489","1646","1649","6116","6117","6919","0395","0247","5270","5427","7385","7395")))
ORDER BY tblMEPRS_Identification.DEPARTMENT;It also looks like your outer query is not using Department at all, so can you omit that?
SELECT DISTINCT tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
FROM tblMEPRS_Identification
WHERE (((tblMEPRS_Identification.DMIS) In ("0125","1480","1485","1489","1646","1649","6116","6117","6919","0395","0247","5270","5427","7385","7395")))
ORDER BY tblMEPRS_Identification.DEPARTMENT;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry for the back to back posts...
<<The question this brings up is how do I determine if I am viewing the CORRECT data for the query. >>
The alternative would be to identify ONE clinic that meets your reporting needs. I arbitrarily suggested the FIRST() aggregate function, but you can also use a WHERE clause to specify allowable clinics or use a variety of other methods to limit the output to a single clinic. The choice really depends on what YOU need to report.
However, the way this thread seems to be heading I'm thinking that the best approach would be to remove any fields from the subquery that you are not reporting, which will in turn hopefully let you drill down to a single record per group.
<<The question this brings up is how do I determine if I am viewing the CORRECT data for the query. >>
The alternative would be to identify ONE clinic that meets your reporting needs. I arbitrarily suggested the FIRST() aggregate function, but you can also use a WHERE clause to specify allowable clinics or use a variety of other methods to limit the output to a single clinic. The choice really depends on what YOU need to report.
However, the way this thread seems to be heading I'm thinking that the best approach would be to remove any fields from the subquery that you are not reporting, which will in turn hopefully let you drill down to a single record per group.
ASKER
mbizup,
Thank you for your assistance on this topic, I realize now that it is a factor of having fields in the query that aren't needed, awarding the points to you, I just need to create dynamic sql statements and limit what I need to pull, thanks, imoutwest
Thank you for your assistance on this topic, I realize now that it is a factor of having fields in the query that aren't needed, awarding the points to you, I just need to create dynamic sql statements and limit what I need to pull, thanks, imoutwest
You're welcome!
ASKER
The DMIS 6117 listed here has two matching Clinics (although there is only one record it list it twice, once for each clinic.)