Query/Report

[LG]

Currently I have a table that processors enter data in. They may enter many
different batches with in a day that have Bath_id attached and different
platforms.
How do I run a query to find out how many claims each person processed per
day by platform and then total of all?

 

[Jerry Whittle]

Please supply the Table name, the field name that identifies the
processors/person, and the field name that identifies the platform. Also some
sample data and what you want that data to look like in the query/report.

 

[LG]

Tables are TBL_ALL and TBL_Employee. The employee table is linked by Proc_id
and did a cancatnation for the name to show rather than ID.
Fields are processor, Batch_id, Date, Platform, (other fields that are not
needed)
Processor Batch ID Date Platform
Martin cm070709-1 07/07/09 QL
Martin cm070709-2 07/07/09 QL
Martin cm070709-3 07/07/09 RX
Martin cm070709-4 07/07/09 QL
Data I would like to see is:
The processor by date and how many of each for each different platform and
then a total for all platforms together for each person.

 

[Jerry Whittle]

To keep it short and sweet, I just used TBL_ALL. I'll let you include the
TBL_Employee link.

SELECT TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform,
Count(TBL_ALL.[Batch ID]) AS [CountOfBatch ID]
FROM TBL_ALL
GROUP BY TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform;

The above SQL statement will give you the number by Processor, Date, and
Platform. However it won't give you the overall total. But you can use this
query as the basis for a report then Sort and Group on the Processor by Date.
That should give you daily and overall totals.

 

[LG]

Thanks I will try it.

To keep it short and sweet, I just used TBL_ALL. I'll let you include the
TBL_Employee link.

SELECT TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform,
Count(TBL_ALL.[Batch ID]) AS [CountOfBatch ID]
FROM TBL_ALL
GROUP BY TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform;

The above SQL statement will give you the number by Processor, Date, and
Platform. However it won't give you the overall total. But you can use this
query as the basis for a report then Sort and Group on the Processor by Date.
That should give you daily and overall totals.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Tables are TBL_ALL and TBL_Employee. The employee table is linked by Proc_id
and did a cancatnation for the name to show rather than ID.
Fields are processor, Batch_id, Date, Platform, (other fields that are not
needed)
Processor Batch ID Date Platform
Martin cm070709-1 07/07/09 QL
Martin cm070709-2 07/07/09 QL
Martin cm070709-3 07/07/09 RX
Martin cm070709-4 07/07/09 QL
Data I would like to see is:
The processor by date and how many of each for each different platform and
then a total for all platforms together for each person.

 

[LG]

I just tried this and instead of the processor since I have to do some work
on that coming from the emplyee table I just used the QCP_ID which is in the
Tbl_all.
The error that shows is you tried to execute a query that does no include
the spedified expression 'QCP_ID' as part of an aggregate function. What
would I do to fix this?

To keep it short and sweet, I just used TBL_ALL. I'll let you include the
TBL_Employee link.

SELECT TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform,
Count(TBL_ALL.[Batch ID]) AS [CountOfBatch ID]
FROM TBL_ALL
GROUP BY TBL_ALL.Processor,
TBL_ALL.Date,
TBL_ALL.Platform;

The above SQL statement will give you the number by Processor, Date, and
Platform. However it won't give you the overall total. But you can use this
query as the basis for a report then Sort and Group on the Processor by Date.
That should give you daily and overall totals.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Tables are TBL_ALL and TBL_Employee. The employee table is linked by Proc_id
and did a cancatnation for the name to show rather than ID.
Fields are processor, Batch_id, Date, Platform, (other fields that are not
needed)
Processor Batch ID Date Platform
Martin cm070709-1 07/07/09 QL
Martin cm070709-2 07/07/09 QL
Martin cm070709-3 07/07/09 RX
Martin cm070709-4 07/07/09 QL
Data I would like to see is:
The processor by date and how many of each for each different platform and
then a total for all platforms together for each person.