Multiple Values used for column heading in crosstab query? (1 Viewer)

[cjyoung]

I have a list of teams and other info as row headings and a list of people as a column heading. I want to show the persons birthday with their name but don't want to concatenate. Is this possible?

 

[Ranman256]

you should show teams as col headings. (fewer)

make a query that pulls these 3 columns.
start the QUERY WIZARD to make CROSSTAB query, using the query above.
set:
people names as rows
teams as col
birthday as first value.

 

[cjyoung]

Thank you for your reply, however that won't work because the teams have more fields that need to be shown too. I need it to look like this (but with much more data of course).

 

[plog]

I want to show the persons birthday with their name but don't want to concatenate.

No, not in an Access query. An Excel Pivot table will allow you that layout.

Also, what's concatenation ever done to you? Why don't you want to use it?

 

[cjyoung]

Because I want to be able to filter/sort by name or birthday which could probably happen before the concatenation but I've now got my data linked to a pivot table in excel. Is there a way to get the rows to still show even if there is no value for that row?

 

[plog]

You would need a datasource that has all the infromation you want to report on. Then you would use that table as the main source in the query and LEFT JOIN your existing datasource(s) to it

Suppose you had a TotalSalesBySalePerson query and it was based on the Sales table. However, Sally was on vacation for the reporting period so she isn't showing up, but you want her to. You would bring in your SalesPeople table and LEFT JOIN the Sales table to it by SalesPerson. Then you use the SalesPerson name from SalesPeople instyead of the Sales table. Since she is in the SalesPeople table she will make it through and land in the query even though she didn't have any data in the Sales table.

 

[cjyoung]

In the excel pivot table?? So sorry my brain is fried. I really didn't think it would be so difficult to come up with the grid I was hoping for.

 

[plog]

In the query.

 

[CJ_London]

not tried it but you can probably create two cross tabs queries, one with your team data and with with your dob, then union them together. However you are mixing datatypes in the columns (dates and text) which might mess things up so perhaps format DOB as a text field - and don't forget to include some 'blank' fields for the team data on the DOB crosstab. And I expect you may need to specify the column headings

edit: and include an orderby column 0 for the dob xtab and 1 for the teams xtab

SELECT *
FROM
(SELECT 1 as Orderby, * FROM xtbTeams
UNION SELECT 0, * FROM xtbDOB) AS Xtb
ORDER BY Orderby, TeamNum