Multiple counts, same field, different values

[DavidBonsall]

I am havign a very miserable Sunday afternoon!

I have a table [table1] of patient samples. The fields are [Patient], [Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.

I can find out how many "Cells" I have for each patient on a particular
date, using the following query:

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));

But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query returns
exactly the same table ie. with a "TotalCells" column but not a "TotalPlasma"
column. Would you, could you, please explain why?

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));

Kindest Regards

David

 

[Douglas J. Steele]

SELECT [table1].[Patient], [table1].[date], Sum(IIf([table1].Sample =
"Cells", 1, 0)) AS
TotalCells, Sum(IIf([table1].Sample = "Plasma", 1, 0)) AS TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date]

 

[louisjohnphillips]

I am havign a very miserable Sunday afternoon!

I have a table [table1] of patient samples. The fields are [Patient], [Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.

I can find out how many "Cells" I have for each patient on a particular
date, using the following query:

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));

But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query returns
exactly the same table ie. with a "TotalCells" column but not a "TotalPlasma"
column. Would you, could you, please explain why?

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));

Kindest Regards

David

The column headings in a union query always come from the first query
in the union. Therefore, the column heading of "TotalPlasma" never
appears but its results are listed under the heading "TotalCells".

If the desired output is one line per patient per day, you might try:

SELECT Patient, [Date],
sum( instr( Sample, 'Cells' ) ) as TotalCells,
sum( instr( Sample, 'Plasma' ) ) as TotalPlasma
from table1
group by Patient, [Date];

The instr function will return either a 0 or a 1 depending if the row
contains that type of Sample.

As an aside, perhaps the "Date" column should be renamed to something
more descriptive. An obvious choice is "DateCollected".

 

[DavidBonsall]

Hallelujah!

Thank you very much. Just out of interest (and only if you can be bothered
to explain). Why did my method only return one column, (other than "because
it was wrong").

Kind Regards

David

SELECT [table1].[Patient], [table1].[date], Sum(IIf([table1].Sample =
"Cells", 1, 0)) AS
TotalCells, Sum(IIf([table1].Sample = "Plasma", 1, 0)) AS TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am havign a very miserable Sunday afternoon!

I have a table [table1] of patient samples. The fields are [Patient],
[Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.

I can find out how many "Cells" I have for each patient on a particular
date, using the following query:

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));

But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query
returns
exactly the same table ie. with a "TotalCells" column but not a
"TotalPlasma"
column. Would you, could you, please explain why?

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample)
AS
TotalPlasmaFROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));

Kindest Regards

David

 

[louisjohnphillips]

Hallelujah!

Thank you very much. Just out of interest (and only if you can be bothered
to explain). Why did my method only return one column, (other than "because
it was wrong").

Kind Regards

David

SELECT [table1].[Patient], [table1].[date], Sum(IIf([table1].Sample =
"Cells", 1, 0)) AS
TotalCells, Sum(IIf([table1].Sample = "Plasma", 1, 0)) AS TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date]

I am havign a very miserable Sunday afternoon!
I have a table [table1] of patient samples. The fields are [Patient],
[Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.
I can find out how many "Cells" I have for each patient on a particular
date, using the following query:
SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));
But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query
returns
exactly the same table ie. with a "TotalCells" column but not a
"TotalPlasma"
column. Would you, could you, please explain why?
SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample)
AS
TotalPlasmaFROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));
Kindest Regards
David- Hide quoted text -

- Show quoted text -

In a union, multiple rows that have the same data are collapsed into a
single row.

Thus, your results returned a single row for each patient. It did not
distinguish whether the patient had a "Cells" sample or a "Plasma"
sample. If the patient had both, it was reported only once but under
the heading of "TotalCells".

 

[DavidBonsall]

Hi,

I have stuck with the IIf() instead of Instr() as it allows me to add
additional criteria using AND. However, both bytes of advice worked
beautifully.

My table doesn't actually include the heading [date]. This was a
simplification.

Kind Regards

David

I am havign a very miserable Sunday afternoon!

I have a table [table1] of patient samples. The fields are [Patient], [Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.

I can find out how many "Cells" I have for each patient on a particular
date, using the following query:

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));

But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query returns
exactly the same table ie. with a "TotalCells" column but not a "TotalPlasma"
column. Would you, could you, please explain why?

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));

Kindest Regards

David

The column headings in a union query always come from the first query
in the union. Therefore, the column heading of "TotalPlasma" never
appears but its results are listed under the heading "TotalCells".

If the desired output is one line per patient per day, you might try:

SELECT Patient, [Date],
sum( instr( Sample, 'Cells' ) ) as TotalCells,
sum( instr( Sample, 'Plasma' ) ) as TotalPlasma
from table1
group by Patient, [Date];

The instr function will return either a 0 or a 1 depending if the row
contains that type of Sample.

As an aside, perhaps the "Date" column should be renamed to something
more descriptive. An obvious choice is "DateCollected".

 

[Ken Sheridan]

David:

You could have used a union query but you'd distinguish the 'cells' from the
'plasma' samples by putting the two terms as constants in a column in each
half of the union operation, so for each patient two rows would be returned
rather than a single row with counts of both sample types, e.g.

SELECT Patient, [date], "Cells" AS SampleType, COUNT(*) AS SampleCount
FROM table1
WHERE Sample = "Cells"
GROUP BY Patient, [date]
UNION ALL
SELECT Patient, [date], "Plasma" AS SampleType, COUNT(*) AS SampleCount
FROM table1
WHERE Sample = "Plasma"
GROUP BY Patient, [date]
ORDER BY Patient, [date], SampleType;

A few points to note:

1. A UNION ALL operation can be used here rather than a UNION operation as
there will be no duplication of rows returned. A UNION operation suppresses
duplicate rows, but is less efficient, so a UNION ALL operation should be
used where no suppression of duplicates is required.

2. A WHERE clause should be used rather than a HAVING clause. The latter
operates after the grouping, the former before it. A HAVING clause is
usually used to restrict the results on the basis of an aggregated value over
the group, e.g. the sum of sales grouped by product might be returned, but
restricted to the total sales being more than 1000 currency units (HAVING
SUM(SaleAmount) > 1000) to identify the 'best sellers'. When creating a
'totals' query in design view select WHERE as the 'total' in the second
column to create a WHERE clause rather than a HAVING clause. If the column
is also being aggregated add it twice to the design grid.

3. An ORDER BY clause operates on the whole result set of a union query, so
the above would list each patient's rows together, ordered by date and then
by sample type. If you are using a query as the RecordSource of a report,
however, don't use an ORDER BY clause, but instead use the reports internal
sorting and grouping mechanism.

4. I'd recommend you don't use 'date' as a column name. It’s the name of a
built in function so is best avoided for object names. Use something like
SampleDate. If you do use date as a column name always wrap it in brackets,
[date], when referencing it.

5. I'd suggest something more explicitly descriptive than 'table1' as a
table name, something such as PatientSamples. It won't work any better, but
it makes keeping track of things easier when you have a number of tables in
the database.

Ken Sheridan
Stafford, England

Hallelujah!

Thank you very much. Just out of interest (and only if you can be bothered
to explain). Why did my method only return one column, (other than "because
it was wrong").

Kind Regards

David

SELECT [table1].[Patient], [table1].[date], Sum(IIf([table1].Sample =
"Cells", 1, 0)) AS
TotalCells, Sum(IIf([table1].Sample = "Plasma", 1, 0)) AS TotalPlasma
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am havign a very miserable Sunday afternoon!

I have a table [table1] of patient samples. The fields are [Patient],
[Date]
and [Sample] The samples taken on the same date can be either "Plasma" or
"Cells" etc. I would like to know how many "Plasma" samples and "Cell"
samples (etc) I have for each patient on each date.

I can find out how many "Cells" I have for each patient on a particular
date, using the following query:

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"));

But when I turn this into a union query to display the total number of
Plasma samples (same table, same field, different value). The query
returns
exactly the same table ie. with a "TotalCells" column but not a
"TotalPlasma"
column. Would you, could you, please explain why?

SELECT [table1].[Patient], [table1].[date], Count([table1].Sample) AS
TotalCells
FROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Cells"))
UNION SELECT [table1].[Patient], [table1].[date], Count([table1].Sample)
AS
TotalPlasmaFROM [table1]
GROUP BY [table1].[Patient], [table1].[date], [table1].Sample
HAVING ((([table1].Sample)="Plasma"));

Kindest Regards

David

 

[Ken Sheridan]

David:

In fact you could just group by Sample instead of using the constants:

SELECT Patient, [date], Sample, COUNT(*) AS SampleCount
FROM table1
WHERE Sample = "Cells"
GROUP BY Patient, [date],Sample
UNION ALL
SELECT Patient, [date], Sample, COUNT(*) AS SampleCount
FROM table1
WHERE Sample = "Plasma"
GROUP BY Patient, [date], Sample
ORDER BY Patient, [date], Sample;

Using constants is more useful when each half of the union operation uses a
different table, as you can then identify which table each row comes from,
e.g. if returning a list of suppliers and customers combined from tables
Suppliers and Customers.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Even better, without any union operation:

SELECT Patient, [date], Sample, COUNT(*) AS SampleCount
FROM table1
WHERE Sample IN("Cells","Plasma")
GROUP BY Patient, [date],Sample;

The WHERE clause is only necessary if the Sample column contains other
values than 'cells' or 'plasma'.

Ken Sheridan
Stafford, England