Query to consolidate information from multiple fields

  • Thread starter Thread starter Kerry Sokalsky
  • Start date Start date
K

Kerry Sokalsky

I have a table with a date field and four fields for
employee names. The table tracks which employees worked
on each day. How can I devise a query or report to list
the days that Jim Smith worked when Jim Smith could be in
employee field 1 for Day X, employee field 2 for Day Y
and employee field 3 for Day Z?

e.g.:

Rec Date Empl1 Empl2 Empl3 Empl4
1 Jan 1 Jim Dave Tom Jane
2 Jan 2 Dave Tim Jack Jim
3 Jan 3 Tom Mike Dave Steve
4 Jan 4 Mike Jim Roger Angela

How can I get a query to return Jan 1, Jan 2 and Jan 4
for the days that Jim worked?

Thanks.
 
Dear Kerry:

Sorry, Kerry, but your table design is what's at fault. You've got a
spreadsheet here instead of a database. In order to be a database,
you need to change it to have only one Employee in each row:

Rec Date NN Emp
1 Jan 1 1 Jim
2 Jan 1 2 Dave
3 Jan 1 3 Tom
4 Jan 1 4 Jane
5 Jan 2 1 Dave
6 Jan 2 2 Tim

and so on. On a table (or query dataset, which I'm coming to) like
I'm coming to you can probably easily see how to accomplish what you
want.

You can transform this "bad design" (and your situation is actually a
very classic case) using a Normalizing Union Query (Normalization is
something that you may want to study, as it is the fundamental rules
for database design, and covers this situation explicitly):

SELECT [Date], 1 AS NN, Empl1 FROM YourTable
UNION ALL
SELECT [Date], 2, Empl2 FROM YourTable
UNION ALL
SELECT [Date], 3, Empl3 FROM YourTable
UNION ALL
SELECT [Date], 4, Empl4 FROM YourTable

Paste in this code (substituting the actual table name where I have
YourTable) and save this as EmplUnion (or whatever name you like).
Then run your requested query on this instead of your table. Try it,
I think you'll like it.

I suggest this is a temporary solution until you can get the database
redesigned round proper normalization rules.

I have a table with a date field and four fields for
employee names. The table tracks which employees worked
on each day. How can I devise a query or report to list
the days that Jim Smith worked when Jim Smith could be in
employee field 1 for Day X, employee field 2 for Day Y
and employee field 3 for Day Z?

e.g.:

Rec Date Empl1 Empl2 Empl3 Empl4
1 Jan 1 Jim Dave Tom Jane
2 Jan 2 Dave Tim Jack Jim
3 Jan 3 Tom Mike Dave Steve
4 Jan 4 Mike Jim Roger Angela

How can I get a query to return Jan 1, Jan 2 and Jan 4
for the days that Jim worked?

Thanks.
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Thanks Tom - that's exactly what I needed. Time to read
up on Union queries.
-----Original Message-----
Dear Kerry:

Sorry, Kerry, but your table design is what's at fault. You've got a
spreadsheet here instead of a database. In order to be a database,
you need to change it to have only one Employee in each row:

Rec Date NN Emp
1 Jan 1 1 Jim
2 Jan 1 2 Dave
3 Jan 1 3 Tom
4 Jan 1 4 Jane
5 Jan 2 1 Dave
6 Jan 2 2 Tim

and so on. On a table (or query dataset, which I'm coming to) like
I'm coming to you can probably easily see how to accomplish what you
want.

You can transform this "bad design" (and your situation is actually a
very classic case) using a Normalizing Union Query (Normalization is
something that you may want to study, as it is the fundamental rules
for database design, and covers this situation explicitly):

SELECT [Date], 1 AS NN, Empl1 FROM YourTable
UNION ALL
SELECT [Date], 2, Empl2 FROM YourTable
UNION ALL
SELECT [Date], 3, Empl3 FROM YourTable
UNION ALL
SELECT [Date], 4, Empl4 FROM YourTable

Paste in this code (substituting the actual table name where I have
YourTable) and save this as EmplUnion (or whatever name you like).
Then run your requested query on this instead of your table. Try it,
I think you'll like it.

I suggest this is a temporary solution until you can get the database
redesigned round proper normalization rules.

I have a table with a date field and four fields for
employee names. The table tracks which employees worked
on each day. How can I devise a query or report to list
the days that Jim Smith worked when Jim Smith could be in
employee field 1 for Day X, employee field 2 for Day Y
and employee field 3 for Day Z?

e.g.:

Rec Date Empl1 Empl2 Empl3 Empl4
1 Jan 1 Jim Dave Tom Jane
2 Jan 2 Dave Tim Jack Jim
3 Jan 3 Tom Mike Dave Steve
4 Jan 4 Mike Jim Roger Angela

How can I get a query to return Jan 1, Jan 2 and Jan 4
for the days that Jim worked?

Thanks.
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
 
Back
Top