Querying multiple initials fields for report

D

Don

I have a table with seven fields, one for each task completed during a
sales process. After completing one of the seven tasks, the user puts
his or her initials into the corresponding field. I would like to run a
query and then a report which tells me how many times each person has
completed each task. This way I can run a complete report to know which
employees need more experience at each task. Also, once I have the
query, I can hopefully have a version of the report that allows each
employee to enter his or her initials and get a "report card" of their
accomplishments.

Below is the recordset from an attempt at a query (not at all what I
want). It includes an Initials field from an Employee Table and the
seven Task fields from another table with two records:

Initials Task1 Task2 Task3 Task4 Task5 Task6 Task7

ABC
ABC DKK ABC XYZ ASS XYZ DKK
ABC FDR JFK NWA MEE POW MIA PWE
ASS
ASS DKK ABC XYZ ASS XYZ DKK
ASS FDR JFK NWA MEE POW MIA PWE
DKK
DKK DKK ABC XYZ ASS XYZ DKK
DKK FDR JFK NWA MEE POW MIA PWE
FDR
FDR DKK ABC XYZ ASS XYZ DKK
FDR FDR JFK NWA MEE POW MIA PWE
JFK
JFK DKK ABC XYZ ASS XYZ DKK
JFK FDR JFK NWA MEE POW MIA PWE
MEE
MEE DKK ABC XYZ ASS XYZ DKK
MEE FDR JFK NWA MEE POW MIA PWE
MIA
MIA DKK ABC XYZ ASS XYZ DKK
MIA FDR JFK NWA MEE POW MIA PWE
NWA
NWA DKK ABC XYZ ASS XYZ DKK
NWA FDR JFK NWA MEE POW MIA PWE
POW
POW DKK ABC XYZ ASS XYZ DKK
POW FDR JFK NWA MEE POW MIA PWE
PWE
PWE DKK ABC XYZ ASS XYZ DKK
PWE FDR JFK NWA MEE POW MIA PWE
XYZ
XYZ DKK ABC XYZ ASS XYZ DKK
XYZ FDR JFK NWA MEE POW MIA PWE
 
G

Guest

It seems to me you are using this as a spreadsheet and not like a database.

I think your table structure needs to be like this --
Sales --
SaleID - Autonumber - primary key
Product -

SaleAction --
ActionID - Autonumber - primary key
SaleID - integer - foreign key
ActionDate - DateTime
EmpID - integer - foreign key
SaleTask - integer - foreign key

Task --
SaleTask - Autonumber - primary key
Task - text

Employee --
EmpID - Autonumber - primary key
LName - text
FName - text
MI - text
etc..

Set one-to-many relation for the following tables --
Employee - EmpID --- SaleAction - EmpID
Task - SaleTask --- SaleAction - SaleTask
Sales - SaleID --- SaleAction - SaleID
 
J

John Spencer

As noted elsewhere in this thread, you really need to redesign your table
structure.

In the meantime, you can build a UNION query to normalize the data for the
tasks. And save the union query. Then you can run your queries against that
table. Union queries can only be built in the SQL window and not in the
query grid window

SELECT "Task1" as TaskType, Task1 as PerformedBy
FROM YourTable
UNION ALL
SELECT "Task2" as TaskType, Task2 as PerformedBy
FROM YourTable
UNION ALL
....
UNION ALL
SELECT "Task7" as TaskType, Task7 as PerformedBy
FROM YourTable
Save that as qTasksByPerson

Then you can use a totals query with that as the source to get your various
counts

SELECT PerformedBy, TaskType, Count(PerformedBy) as NumTimes
FROM qTasksByPerson
GROUP BY PerformedBy, TaskType
 
D

Don

Thank you for your advice. I plan to redesign as you suggested.

In the meantime, I've built the UNION query as you advised, using the
field name as TaskType and the field data as PerformedBy. I am getting
the following error when I try to save or run the UNION query: Syntax
error in query. Incomplete query clause.

Any idea what might be causing that?

Thanks in advance,
Don
 
G

Guest

Post your SQL statement.

Don said:
Thank you for your advice. I plan to redesign as you suggested.

In the meantime, I've built the UNION query as you advised, using the
field name as TaskType and the field data as PerformedBy. I am getting
the following error when I try to save or run the UNION query: Syntax
error in query. Incomplete query clause.

Any idea what might be causing that?

Thanks in advance,
Don
Click to expand...
 
D

Don

SELECT "CruisedInitials" as [TaskType], [CruisedInitials] as
[PerformedBy]
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PrescriptionInitials" as TaskType, PrescriptionInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "FlaggedInitials" as TaskType, FlaggedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "SkidTrailsInitials" as TaskType, SkidTrailsInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "MarkedInitials" as TaskType, MarkedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PreHarvestPhotoInitials" as TaskType, PreHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PostHarvestPhotoInitials" as TaskType, PostHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
 
G

Guest

You do not have it ended correctly.

End it like this --
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PostHarvestPhotoInitials" as TaskType, PostHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table];

Remove last UNION ALL and add a semicolon to the last line.

Don said:
SELECT "CruisedInitials" as [TaskType], [CruisedInitials] as
[PerformedBy]
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PrescriptionInitials" as TaskType, PrescriptionInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "FlaggedInitials" as TaskType, FlaggedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "SkidTrailsInitials" as TaskType, SkidTrailsInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "MarkedInitials" as TaskType, MarkedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PreHarvestPhotoInitials" as TaskType, PreHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PostHarvestPhotoInitials" as TaskType, PostHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL


KARL said:
Post your SQL statement.
Click to expand...
Click to expand...
 
D

Don

You're awesome. Thanks so much.

KARL said:
You do not have it ended correctly.

End it like this --
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PostHarvestPhotoInitials" as TaskType, PostHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table];

Remove last UNION ALL and add a semicolon to the last line.

Don said:
SELECT "CruisedInitials" as [TaskType], [CruisedInitials] as
[PerformedBy]
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PrescriptionInitials" as TaskType, PrescriptionInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "FlaggedInitials" as TaskType, FlaggedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "SkidTrailsInitials" as TaskType, SkidTrailsInitials as
PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "MarkedInitials" as TaskType, MarkedInitials as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PreHarvestPhotoInitials" as TaskType, PreHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL
SELECT "PostHarvestPhotoInitials" as TaskType, PostHarvestPhotoInitials
as PerformedBy
FROM [Timber Sales Tracking Table]
UNION ALL


KARL said:
Post your SQL statement.

:

Thank you for your advice. I plan to redesign as you suggested.

In the meantime, I've built the UNION query as you advised, using the
field name as TaskType and the field data as PerformedBy. I am getting
the following error when I try to save or run the UNION query: Syntax
error in query. Incomplete query clause.

Any idea what might be causing that?

Thanks in advance,
Don


John Spencer wrote:
As noted elsewhere in this thread, you really need to redesign your table
structure.

In the meantime, you can build a UNION query to normalize the data for the
tasks. And save the union query. Then you can run your queries against that
table. Union queries can only be built in the SQL window and not in the
query grid window

SELECT "Task1" as TaskType, Task1 as PerformedBy
FROM YourTable
UNION ALL
SELECT "Task2" as TaskType, Task2 as PerformedBy
FROM YourTable
UNION ALL
...
UNION ALL
SELECT "Task7" as TaskType, Task7 as PerformedBy
FROM YourTable
Save that as qTasksByPerson

Then you can use a totals query with that as the source to get your various
counts

SELECT PerformedBy, TaskType, Count(PerformedBy) as NumTimes
FROM qTasksByPerson
GROUP BY PerformedBy, TaskType



I have a table with seven fields, one for each task completed during a
sales process. After completing one of the seven tasks, the user puts
his or her initials into the corresponding field. I would like to run a
query and then a report which tells me how many times each person has
completed each task. This way I can run a complete report to know which
employees need more experience at each task. Also, once I have the
query, I can hopefully have a version of the report that allows each
employee to enter his or her initials and get a "report card" of their
accomplishments.

Below is the recordset from an attempt at a query (not at all what I
want). It includes an Initials field from an Employee Table and the
seven Task fields from another table with two records:

Initials Task1 Task2 Task3 Task4 Task5 Task6 Task7

ABC
ABC DKK ABC XYZ ASS XYZ DKK
ABC FDR JFK NWA MEE POW MIA PWE
ASS
ASS DKK ABC XYZ ASS XYZ DKK
ASS FDR JFK NWA MEE POW MIA PWE
DKK
DKK DKK ABC XYZ ASS XYZ DKK
DKK FDR JFK NWA MEE POW MIA PWE
FDR
FDR DKK ABC XYZ ASS XYZ DKK
FDR FDR JFK NWA MEE POW MIA PWE
JFK
JFK DKK ABC XYZ ASS XYZ DKK
JFK FDR JFK NWA MEE POW MIA PWE
MEE
MEE DKK ABC XYZ ASS XYZ DKK
MEE FDR JFK NWA MEE POW MIA PWE
MIA
MIA DKK ABC XYZ ASS XYZ DKK
MIA FDR JFK NWA MEE POW MIA PWE
NWA
NWA DKK ABC XYZ ASS XYZ DKK
NWA FDR JFK NWA MEE POW MIA PWE
POW
POW DKK ABC XYZ ASS XYZ DKK
POW FDR JFK NWA MEE POW MIA PWE
PWE
PWE DKK ABC XYZ ASS XYZ DKK
PWE FDR JFK NWA MEE POW MIA PWE
XYZ
XYZ DKK ABC XYZ ASS XYZ DKK
XYZ FDR JFK NWA MEE POW MIA PWE
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report of the number of times that someone's initials are in a specific field 1
Report Grouping, Access 2000 & 2003 2
Proper table design for monthly tracking of activities 3
Problem with concatenating multiple fields and records 7
FWT Newsletter - Weekly - September 13, 2004 1

Top