Sorting a report using values in multiple fields

[EBox]

Hi:

I have a table that captures the names of workers in 3 fields, "Staff1", "Staff2" and "Staff3" for each record.

I would like to create a report which sorts by staff name, but I'm not sure how to pull up all records for a staff member when they could be listed in any of the aforementioned 3 fields.

So, if my records look like

Record Staff1 Staff2 Staff3
1 Jim John Frank
2 Frank
3 John Mike
4 Mike Jim John

My report should look be able to identify (sorted in alphabetical order):

Frank: 2
Jim: 2
John: 3
Mike: 2

Can anyone please help?

Thanks,
EBox

 

[dhookom]

First, normalize your table structure. Does the number next to the staff name represent the number of times their name appears in all fields?

If you can't normalize then use a union query like:

== quniStaff ====
SELECT Staff1 As Staff
FROM tblNoNameGiven
WHERE Staff1 is not null
UNION ALL
SELECT Staff2
FROM tblNoNameGiven
WHERE Staff2 is not null
UNION ALL
SELECT Staff3
FROM tblNoNameGiven
WHERE Staff3 is not null;

Then create a totals query like

== qgrpStaff =========
SELECT Staff, Count(*) as NumOf
FROM quniStaff
GROUP BY Staff;

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]

 

[EBox]

Thank you. This worked wonderfully. However, I am trying to also include the date range for the query that this union query references. I set up prompts for [StartDate] and [EndDate] and wanted to provide the totals query results in the context of this date range on a report, so I could identify a header that shows "Between "&[StartDate] & " " & [EndDate].

How can I pull those values into the totals query you identified above?

Thanks,
EBox

 

[dhookom]

I can't see any date fields in your table which makes it impossible to suggest how to filter by date.

Also, consider using controls on forms for all filtering values. IMHO parameter prompts are not appropriate in any decent user interface.

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]

 

[EBox]

In my table I just have one date field, [DateofService]. I set up a prompt in an overlying query with the prompts [StartDate] and [EndDate], (in m/d/yy format) so the user can identify the dates.

If parameter prompts are not recommended, what alternatives are there? (Sorry, I'm a bit new at this).

Thanks,
EBox

 

[dhookom]

I would create a form (if you don't have a good one open) named frmDates and add two text boxes txtStartDate and txtEndDate. Open this form prior to opening your report. Enter the appropriate dates and then open your report.

== quniStaff ====
SELECT DateofService, Staff1 As Staff
FROM tblNoNameGiven
WHERE Staff1 is not null
UNION ALL
SELECT DateofService, Staff2
FROM tblNoNameGiven
WHERE Staff2 is not null
UNION ALL
SELECT DateofService, Staff3
FROM tblNoNameGiven
WHERE Staff3 is not null;

Then change your totals query to:

== qgrpStaff =========
SELECT Staff, Count(*) as NumOf
FROM quniStaff
WHERE DateOfService Between Forms!frmDates!txtStartDate and Forms!frmDates!txtEndDate
GROUP BY Staff;

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]