Can I do this with queries?

[hzeigler4]

I have a table of all staff members. A staff member can work at more than one location. The currecnt staff table I was given contains a record for every location they work. For instance:

Name Location
Heather Office A
Heather Office B
Heather Office C
Sarah Office A
Tom Office B
Tom Office D


What I need is to to only have one record for each staff member but that record will have a column for each location. There are a possibility of 50 locations so i really didnt think I could use a crosstab.

So if I could figure out how to do this it would look like:

Name Location1 Location2 Location3 Location4
Heather Office A Office B Office C
Sarah Office A
Tom Office B Office D


Each staff member will have no more than 5 locations and no less than one location. So what I did first was created a table with the fields:

Name
Location 1
Location 2
Location 3
Location 4
Location 5

I was thinking I could then somehow append and update the locations for each staff member but this is a little beyond my expertise. Any suggestions on how I could easily do this? Help????

 

[Galaxiom]

The existing structure with a record for each staff member and location is the right way to structure the database. Changing this to single records with a field for each location is a step in the wrong direction.

Do not change the table structure. A database is not a spreadsheet and thinking of it as one is a mistake. Tables store the data. Reports and forms display the data.

Presumably what you actually need is a report showing the information arranged as you explained. Try asking how to do this instead.

 

[hzeigler4]

I actually need to import my existing staff data into another system. That is why I am trying to get in that format. Only so that it will import correctly into my new system. Otherwise the staff will be duplicated
where as my software package allows the user to see a staff member and then all their locations listed under their name.

 

[Access Hero]

You'll need to write a concatenate function. Read this and then post with any follow-up questions you may have:
http://allenbrowne.com/func-concat.html