Count the number of times two items appear in a table

**ResearchRN** · 02-09-2010, 05:57 PM

I have a table in my brain tumor database (Access 2003) that records the various surgical technologies that are used in each surgical case (tblPituitarySurgTech in attached example). Some surgeries involve the use of a surgical microscope. Some use a endoscope. Some use a surgical navigation system called "Stealth". I can write a query that totals the use of each item (qryPituitarySurgTech in the attached example). That 's easy. What I can't figure out is how to count, for example, the number of cases in which both a microscope and an endscope are used. It seems like this should be a simple thing to do. I just can't figure it out.

**ConneXionLost** · 02-09-2010, 11:22 PM

Hi ResearchRN,

Welcome to the forum!

The result is a single cell:

Code:

SELECT Count(tblPituitarySurgTech.surgTech) AS [Endoscope or Microscope]
FROM tblPituitarySurgTech
WHERE (((tblPituitarySurgTech.surgTech)="endoscope" Or (tblPituitarySurgTech.surgTech)="surgical microscope"));

Cheers,

**ResearchRN** · 02-10-2010, 12:04 PM

Originally Posted by ConneXionLost

Hi ResearchRN,

Welcome to the forum!

The result is a single cell:

Code:

SELECT Count(tblPituitarySurgTech.surgTech) AS [Endoscope or Microscope]
FROM tblPituitarySurgTech
WHERE (((tblPituitarySurgTech.surgTech)="endoscope" Or (tblPituitarySurgTech.surgTech)="surgical microscope"));

Cheers,

Thanks for the welcome and your reply. However it's not quite what I'm looking for. The query appears to sum the number of times "endoscope" or " surgical microscope" appears in the table. What I need to know is how to find the number of cases in which both an endoscope and a microscope were used in the same case. For example, case number 36 used only an endoscope. Case number 37 used only a microscope. However case numbers 7, 114, and 178 used both. Just scanning the rest of the table it would appear that the total number of cases that used both would be 19 or so.

Just to give you some background, nowadays these cases are done using an endoscope to gain access to the tumor and the microscope to remove it. In the past, they were done mostly by "eyeball". Then the microscope was added, then the endoscope. Now a lot of these cases are done using intra-operative MRI scanning. Lately they've been looking at 3D endoscopes in high def! The question we're trying to answer, among other things, is whether all these technologies really help and if so in what combination. It's the combination part I'm having trouble with.

Thanks very much for your help.

**ConneXionLost** · 02-11-2010, 02:41 AM

Sorry, my fault for not reading carefully.

Try this:

Code:

SELECT Count(tblPituitarySurgTech.surgTech) AS [Endoscope and Microscope]
FROM tblPituitarySurgTech INNER JOIN tblPituitarySurgTech AS tblPituitarySurgTech_1 ON tblPituitarySurgTech.caseNumber = tblPituitarySurgTech_1.caseNumber
WHERE (((tblPituitarySurgTech.surgTech)="endoscope") AND ((tblPituitarySurgTech_1.surgTech)="surgical microscope"));

If you want to see the case numbers, then use:

Code:

SELECT tblPituitarySurgTech.caseNumber, Count(tblPituitarySurgTech.surgTech) AS [Endoscope and Microscope]
FROM tblPituitarySurgTech INNER JOIN tblPituitarySurgTech AS tblPituitarySurgTech_1 ON tblPituitarySurgTech.caseNumber = tblPituitarySurgTech_1.caseNumber
WHERE (((tblPituitarySurgTech.surgTech)="endoscope") AND ((tblPituitarySurgTech_1.surgTech)="surgical microscope"))
GROUP BY tblPituitarySurgTech.caseNumber;

Cheers