Can I get a query to do this? (1 Viewer)

[eggs3]

I have this table. Field 1 is date. Field 2 is "Location X". Field 3 is "Location Y". Field 4 is "Location Z"...you get the picture, only I have about 10 locations to work with.

When entering data into the table, in field 1 you put the date. Then into fields 2-4 (in this abreviated example) you enter the name of the person assigned to that location.

What I want to know is: how can I design a query to get me a report for a single person that has the dates and locations they are assigned to?

Any suggestions would be great! I'm a beginner on Access and can't figure this query out...

 

[Rich]

Isn't there a design problem here you only need one field called Location with the actual locations stored within that one field?

 

[Pat Hartman]

It is a many-to-many relationship. You have people and you have locations. A person can be assigned to many locations and a location can be assigned to many people. This structure takes three tables to implement.
tblPerson
PersonID (primary key)
FirstName
LastName
etc.

tblLocation
LocationID (primary key)
LocationName
etc.

tblAssignments
PersonID (primary key part1)
LocationID (primary key part2)
AssignedDt

You'll need two forms and at least one of them needs a subform.

I would make a main form to maintain locations and a main form to maintain people with a subform to assign to locations.

Once you restructure your data, querying it will be very simple.

Select tblPerson.PersonID, tblPerson.LastName, tblPerson.FirstName, tblAssignment.AssignmentDt, tblAssignment.LocationID
From tblPerson Inner Join tblAssignment on tblPerson.PersonId = tblAssignment.PersonID;

If you also want location description, you can get it by adding tblLocation to the query with another join.