combining same values from two fields

[cherishxrocks]

I would like to make a report to show how many employees and which employees are attending to which colleges/universities.

In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.

There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?

For instance, this is my raw data:

Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC

I want my report to show:

Colleges -
University of HI
Bob
Sandy
Clare
John

Honolulu CC
Bob
Sandy

Kapiolani CC
Clare

Windard CC
John

Please help. Thanks in advance.

 

[cherishxrocks]

Anyone help?

 

[CJ_London]

Your table structure is very inefficient so requires a much more complex query to resolve.

A better structure would be to have 3 tables

tblEmployee
EmployeeID autonumber PK
EmployeeName Text
...
...

tblColleges
CollegeID autonumber PK
CollegeName text

tblAttendance
AttendanceID autonumber PK
EmployeeID Long FK
CollegeID Long FK

However given your current structure you will need a query along the following lines - change names to suit your table:

SELECT EmployeeName FROM myTable WHERE College1='University of HI' OR College2='University of HI'

 

[cherishxrocks]

Hi CJ_London,

Thanks for the reply.
College 1 and College 2 are two fields that are in my Employee table. Actually all my fields are in just my Employee table.

Should I set it up the way you said it?

Because I'm currently using the sample "Faculty" template on Microsoft Access 2010, I'm not too sure how to connect it together. & where do I put the code?

 

[CJ_London]

Should I set it up the way you said it?

If this is a long term project or an exercise in good database design then I would say yes, otherwise the solution I provided will work with what you have.

I'm not too sure how to connect it together

You create joins between the FK's and respective PK's i.e. join
tbleEmployees.EmployeeID to tblAttendance.EmployeeID and tblAttendance.CollegeID to tblColleges.CollegeID.

where do I put the code

The code is written as a query, so as written with your current table structure you would put it in a query - change the names to match the names of your fields and table, open query designer, change view to sql view and paste the code into the window. You can then change back to the designer view to see what this looks like.