I have created a projects database (see image of tables and relationships attached) where a project site can have multiple staff with a role or type for each. (Roles cannot be duplicated.) The form for entering the staff and roles is working fine. What I need now is a query that gets the related staff data (address, email, etc.) and matches it to the particular staff on the project site. Since a project site can have no staff, one staff, or many staff, how do I allow for all of these variables in the query? I created a crosstab query (see image of design attached) that gets the roles or types. I was then going to join this to the Staff table to get the related data but it seems that I would have to join each staff to a separate Staff table (i.e. tblStaff_1, tblStaff_2, etc.). However, because there is no limit as to how many staff can be on a project site, this doesn't seem to be the most efficient way to achieve this?

Any help would be appreciated.

Thanks.

If a role can exist only once for a site, the limit to the number of staff at a site is the number of available roles.

I would not do this with a crosstab. That just complicates the query. What is wrong with a list of staff members sorted by role or member name within site?

Pat, Thanks for your reply.

The list is not "wrong" it's just not what they want. They want all staff for a project site on one row (i.e. they do not want the project site to be repeated for every staff member). Eventually, they want to export this query to Excel to use the data for mail merges, etc.

This is why I resorted to the crosstab query because it was the only way I knew how to get each role as a column heading. Now, I'm struggling with how to bring the other staff data onto the row as well.

Any other thoughts?

Thanks.

Mail merges will be easier from Access where the data is normalized.

To achieve the list format you are looking for, create a query that selects all the columns you want for the final output and concatenates them separated by a carriage return (vbCr). This should allow them to end up in the same column of the spreadsheet but show on separate rows. Then write VBA code to take this query and write out as many columns as you need for each site to a quoted, comma delimited .csv file that can be imported into Excel.

If my idea about the vbCr doesn't pan out, you'll need to manually create four sets of records so that name appears in the first row, address in the second, City/State/Zip in the third, and phone number in the fourth.

I agree about the mail merges but...

I was thinking that I had to some way combine crosstab queries but just wasn't sure how. I will follow your instructions.

Thanks again.

I have now read your edited post; unfortunately, I don't know how to write VBA code. In your post prior to the edited version, you mentioned combining several crosstab queries. Do I combine them using a union query? (Sorry, but I don't have any experience with those.) My understanding is that union queries require the same number of fields. So, if my first crosstab query, which has the roles as column headings has eight fields, all the other queries I want to combine must have eight fields, right?

This gets further complicated by the fact that they want to be able to continually add roles as the database grows. So, the first crosstab query's fields will change based on any new roles that have been assigned.

Thanks again.

I abandoned the idea of unioning the crosstab queries because each site will have a different set of columns. Union queries REQUIRE that columns be consistant.

The only way to make the Union query work would be to fix the columns of the crosstab and you cannot do that across sites because each site has diferent staff members. I suppose you could do one site at a time. Make the site a parameter (you do not want to create a specific query for each site) and define it specifically by right-clicking on the gray area of the QBE view and selecting the parameter property. Do this for EVERY query in the set. Crosstabs are much more sensitive about parameters than other query types.

Not sure I understand...

Once I set up the parameter crosstab queries, will they have to choose each site individually in order to run it? Does this process still involve the union query? There are currently 112 project sites with staff and this will only grow.