Help me to use ID as a pivot!!!

[Pokecell]

I work on an enterprise which we send people to do field work, & i am doing a database in Access 2013, there i have a table with all the data of our employees defined by an ID, & i have also a table with all the drivers that take em defined by another ID for them, the problem is when i am trying to make a query...
I made a table with the ID of the drivers, the ID of our employees that are on that trip with him, the departure date & the arrival date; and when i try to make my query i want it to display: the both dates, & Name of the driver, his ID, & the truck he is using (all this info is on the driver table), and also to diplay the name, the ID & the rank of the employees going with him (all this info is on the employee table), the problem comes that since i am using the ID to search for every employee info, when i put that more than 1 employee is going in that trip i get this error on the top of table: Expr1000, Expr1001; instead of ID, Name, etc... & no data is displayed.
I hope you can help me Thank you a lot

 

[JHB]

Show some sample data (Excel) + the query + how you want then result.
Or post you database with some sample data, (zip it because you haven't post 10 post yet).

 

[stopher]

I'm guessing your requirements here so I reiterate JHB's advice.

When I first read this I thought cross-tab query would be the way to go. But I think the better approach is to use "columns" in page setup. This showing lists of uneven lengths in columns.

A simple report:

So create a normal report showing the driver as the group header and the employees as the detail. You will need to make the report as narrow as possible because it is going to be repeated columnwise over the page for as many column as will fit on the page.

Set the “New Row or Col” for the Driver group footer to “After section”. This ensures that a new column is started after each group.

Then go to page setup and set the columns to the number of columns you want to see. The “Column Layout” should be set to “Down then Across”.

The attached database has an example ( rptListOfTeams_Simple). Make sure you display it in Print Preview to see the correct effect.

A more complex report using a sub report:

The simple version is limited in that it the columns do not continue down the page i.e. only six groups are shown on a page and the next six are on the next page. We multiple rows of groups by using sub reports. (See rptListOfTeams_usingSubrpt in Print Preview). The approach is slightly different in that the main report goes “Across then down” and is only the unique drivers. The subreport then provides the detail for each driver.

hth
Chris