[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access Query - Using Inner Join Query Doubles the Number of Matching Records

Posted on 2013-10-29
11
Medium Priority
?
608 Views
Last Modified: 2013-11-01
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28262542.html

I tried using Distinct, but didn't change results.

Database Relationship:
tblCHCS_Dispensed.Drug_IEN to tblNDCRelationships.IEN
tblCHCS_Dispensed.MEPRS to subqryMEPRS_Identification.MEPRS

Tables:
tblNDCRelationships (4000 records)
  IEN
  UM_Cost

tblCHCS_Dispensed (100,000 records)
  Drug_IEN  
  QTY
  MEPRS – Location Field

subqryMEPRS_Identification (300 records)
  DEPARTMENT
  MEPRS

This query makes a table that is used to compute the TopN.

SELECT DISTINCT subqryMEPRS_Identification.DMIS, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, Count(tblCHCS_Dispensed.RX_NUM) AS CountOfRX_NUM, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) AS SubTotal INTO tblOutput
FROM (tblCHCS_Dispensed INNER JOIN tblNDCRelationships ON tblCHCS_Dispensed.DRUG_IEN = tblNDCRelationships.IEN) INNER JOIN subqryMEPRS_Identification ON tblCHCS_Dispensed.MEPRS = subqryMEPRS_Identification.MEPRS
GROUP BY subqryMEPRS_Identification.DMIS, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, tblCHCS_Dispensed.[MMM-YY]
HAVING (((subqryMEPRS_Identification.DMIS) In ("6117")) AND ((tblCHCS_Dispensed.[MMM-YY]) = "Sep-13"))
ORDER BY subqryMEPRS_Identification.DMIS, Sum([tblCHCS_Dispensed.QTY]*[tblNDCRelationships.UM_Cost]) DESC;
0
Comment
Question by:Imoutwest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 7

Author Comment

by:Imoutwest
ID: 39609851
I may have discover the cause, but not how to fix it.  

The DMIS 6117 listed here has two matching Clinics (although there is only one record it list it twice, once for each clinic.)
0
 
LVL 31

Expert Comment

by:hnasr
ID: 39610199
As  Imoutwest suggested, check to see if left table has no duplicate selected field in place of primary key.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39611086
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.DMIS, tblNDCRelationships.IEN, tblNDCRelationships.DRUG_LIST, First(Clinic) AS FirstClinic, Count(tblCHCS_Dispensed.RX_NUM) AS CountOfRX_NUM, etc...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Imoutwest
ID: 39611886
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 I am able through an Inner Join provide the Clinic information.  The problem lies in the fact several clinics can share a MEPRS; so when I’m pulling the data over it list the record twice (once for each clinic although it only exist once in the tblCHCS_Dispensed table.  

The query listed doesn’t include the Clinic, nothing changes if I add it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39611939
At what point do the duplicates show up?

Are the duplicates in subqryMEPRS_Identification or do they appear after subqryMEPRS_Identification is joined to the rest of the query?

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.
0
 
LVL 7

Author Comment

by:Imoutwest
ID: 39611954
Hello again, I think my problem is the subsryMEPRS_Identification (see below).

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.DEPARTMENT, First(tblMEPRS_Identification.CLINIC) AS FirstOfCLINIC, tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DEPARTMENT, tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
HAVING (((tblMEPRS_Identification.DMIS) In ("0125","1480","1485","1489","1646","1649","6116","6117","6919","0395","0247","5270","5427","7385","7395")))
ORDER BY tblMEPRS_Identification.DEPARTMENT;
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39611996
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.DEPARTMENT, tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
FROM tblMEPRS_Identification
GROUP BY tblMEPRS_Identification.DEPARTMENT, tblMEPRS_Identification.DMIS, tblMEPRS_Identification.MEPRS
HAVING (((tblMEPRS_Identification.DMIS) In ("0125","1480","1485","1489","1646","1649","6116","6117","6919","0395","0247","5270","5427","7385","7395")))
ORDER BY tblMEPRS_Identification.DEPARTMENT;


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;

Open in new window


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;

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39612006
The idea with my above suggestions is that by removing columns/fields that are not being reported you might make it possible to select distinct records containing only the fields that you need.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39612024
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.
0
 
LVL 7

Author Closing Comment

by:Imoutwest
ID: 39617005
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39617031
You're welcome!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question