MS Access - Count Number of Cases 3

[CBlackmo]

I have two tables: Case Information and Procedure Type. The Case Information table contains the Case # and the Procedure table contains the different types of procedures. One case # can have multiple procedures. So, I want to count the case # only once even if it has 3 procedures associated with it. I've read the other help but haven't seen one that has information coming from two tables.

 

[PHV]

What have you tried so far and where are you stuck in your SQL code ?
As JetSQL lacks the COUNT(DISTINCT colname) aggregate function, a common work around is to count the number of rows returned by a SELECT DISTINCT subquery.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I'm guessing that's there's information in the Procedure Table that you need in your result set other wise you could just count the case numbers from the Case Table?

What is the criteria/information that's needed from the Procedures table?

If there are multiple procedures for each case, which one of the procedure records do you want to return?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[CBlackmo]

SQL Statement:
SELECT Cath.Date_of_Cath, Cath.CaseNumber, Procedures.Procedure_Name
FROM Cath INNER JOIN Procedures ON Cath.Cath_ID = Procedures.Cath_ID

The result I want is to have one case number no matter how many procedures were done. I'm not looking for a specific procedure, just a count of cases.

case 205 Left heart cath
case 206 PTCA
case 207 Left heart cath

Another example would be with equipment tied to the case number. If the patient had 4 stents, then I do want to see all 4 stents but count the case number only one time.

case 210 Stent A, Stent B, Stent C
case 211 Stent D, Stent A
case 212 Stent B

3 cases utilizing 6 stents or 2 cases utilizing two of type Stent B.

thanks for your help

 

[dhookom]

Why do you even include the Procedures table in all you want is a count of Cath records? Try:

SELECT Count(*) As NumOf
FROM Cath;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CBlackmo]

I include the second table (either procedure or equipment)because we either need to look at a specific procedure or a specific piece of equipment that could both be listed more than one time per case number. When I run the query it shows me the case number every time the specific item from the second table is returned. Thanks

 

[dhookom]

I must have missed the entire "specific procedure..." from your earlier posts.

SELECT Count(*) As NumOf
FROM Cath
WHERE Cath_ID IN 
  (SELECT Cath_ID
   FROM Procedures
   WHERE ...some criteria...);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CBlackmo]

Thanks for your help (Everyone), I think I've got it from here on.