Create a report that looks at three different fields for sorting.

[BZeyger]

Hello,

I have created a detailed project report in Access VBA.
Basicly, I am intrested in sorting the info by the employee assigned.
The way the report is currently set up, I have a data field emploee1 as a
header.

However, I have 3 employee fields.

Employee1
Employee2
Employee3

Any employee can be assigned to any employee field. A job usually has no
more than three different employee working on it.

How can I sort the report by employee? The name can appear in any of the
three fields?

 

[Fred]

No offense, but between using terms (without defining them) which have
meaning only to you, and mis-using some other Access terms, it was hard to
tell exactly what you are asking or what you have there.


Sorting is inherently hierarchal; you sort by the first attribute, and then,
at the next level down, within groups of people who have the first attribute
the same, you sort by a second attribute etc. And, in most data, attribute =
field. I'd use this paragraph to sort out exactly what you want to do.

So, in your report design,

So, decides which field is your top/first attribute, then your second. In
your report design view, hith the "sorting and grouping" incon, and enter
those in the box accordingly.

 

[John Spencer MVP]

Best way would be to redesign your table structure. You should have a minimum
of three tables.
Projects
Employees
ProjectEmployees

The project employees table would have one record for each employee working on
a project.

If you are stuck with the current structure you will need to use a union query
as the source for your report. Without knowing the structure of your current
table (tables?), it is hard to give you a concrete example.

UNION queries cannot be built using the design view, but must be built in the
SQL view. HEre is a sample that returns one record for Project and each Employee.

SELECT Project, Employee1 as Employee
FROM YourTable
WHERE Employee1 is Not Null
UNION ALL
SELECT Project, Employee2 as Employee
FROM YourTable
WHERE Employee2 is Not Null
UNION ALL
SELECT Project, Employee3 as Employee
FROM YourTable
WHERE Employee3 is Not Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County