Combine data in fields within one table

[kevintex]

Greetings,
I'm trying to combine data in fields that I currently have in multiple
fields. Allow me to elaborate.
Lead employees are assigned a case (with a unique ID number) and may
or may not have other support employees working with them. The DB
record thus shows a lead employee in one field, the unique number in
another and finally the possibility of up to four employees (shown in
four distinct fields) as team members.
I need these four employees to show up in a new query that shows them
in a distinct record separate from the lead employee. This is a
superlist, if you will, that shows all the employees (lead as well as
support employees) and the unique ID(the case they were assigned).

Original Query:
Lead | CaseID | Support 1 | Support 2 | Support 3 | Support
4 |
Joe | A001 | Bill | Bob | Jane
| |
Jane | A002 | Joe | Bill
| | |
Joe | A003 | Bill |
| | |
Joe | A004 | |
| | |

Desired Final Query Result (superlist):
Employee | Case ID |
Joe | A001 |
Bill | A001 |
Bob | A001 |
Jane | A001 |
Jane | A002 |
Joe | A002 |
Bill | A002 |
Joe | A003 |
Bill | A003 |
Joe | A004 |

Is this possible? Any ideas on how to tackle this?

Thanks,
Kevin

 

[Jeff Boyce]

Kevin

What you've described sounds a bit like ... a spreadsheet! Adding columns
to add more support folks may be how you have to do it with a spreadsheet,
but in a relational database like Access, you build a one-to-many table and
add rows, not columns.

What you want to do is quite easy, but only if your data is in a form Access
can readily use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kevintex]

Jeff,

Thanks for the reply. I'm not quite following your explanation.
All the data in my query is currently in Access and pulled from
multiple tables. Also, I don't think it's very efficient for Access
to build a separate table each time someone wants a report.
I am by no means an Access veteran, but what is a one-to-many table?
I thought you could only have a one-to-many query.
I appreciate your help and please pardon my ignorance of Access.

Thanks,
Kevin

 

[Jeff Boyce]

Kevin

I can copy a Word table into Access. That doesn't mean the data will be
organized in the way Access expects to make best use of the features and
functions. If "normalized" doesn't make any sense, take a look at Jeff
Conrad's explanations:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

I don't believe I mentioned anything about creating a "separate table each
time someone wants a report." But it is VERY reasonable to create a new
query to serve as the source for each report.

A table isn't "one-to-many" ... two tables could be in a "one-to-many"
relationship. For example, if you place an order on-line, your order (date,
time, salesperson, you) goes into a "one" ("parent") table. Each item you
order, however, goes into a "many" ("child") table, since the order item
record only needs the Item, Qty, Color, Size, perhaps the UnitPrice, and a
pointer back to which Order "owns" the item.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[hongluen via AccessMonster.com]

I am a beginner. I think there are better ways to accomplish what you want.
Anyway, I believe you can have 5 diffferent queries to select not null
records:
Qry_Lead (criteria: Lead is not null) gives 4 records
Qry_Support1 (criteria: Support1 is not null) gives 3 records
....
Qry_Support4 (criteria: Support4 is not null) gives 0 records
In each of these queries, you only got the Name of employee and CaseID.

Then, append them into another temp table. This should give you your
desirable list.

Kevin

I can copy a Word table into Access. That doesn't mean the data will be
organized in the way Access expects to make best use of the features and
functions. If "normalized" doesn't make any sense, take a look at Jeff
Conrad's explanations:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

I don't believe I mentioned anything about creating a "separate table each
time someone wants a report." But it is VERY reasonable to create a new
query to serve as the source for each report.

A table isn't "one-to-many" ... two tables could be in a "one-to-many"
relationship. For example, if you place an order on-line, your order (date,
time, salesperson, you) goes into a "one" ("parent") table. Each item you
order, however, goes into a "many" ("child") table, since the order item
record only needs the Item, Qty, Color, Size, perhaps the UnitPrice, and a
pointer back to which Order "owns" the item.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

[quoted text clipped - 8 lines]

Thanks,
Kevin