Across/Horizontal Sort 1

[sagobogger]

I'm transitioning to Access from something else and I'm sure I'm just missing the right Access term for this, but I'm having difficulty with doing the type of report that has a sort for the rows, and a sort for the columns.

Suppose a data set has these fields (with example values):

Salesperson = John
Vehicle category = Truck
Model = F250
Number sold = 3

I need a report that summarizes the vehicle sales with one row per salesperson, and one column per vehicle category, like:

Salesperson Car SUV Truck Van
Andy 4 3 5 0
Lucy 1 8 9 1


TIA

 

[dhookom]

You are describing a crosstab query. Salesperson is the Row Heading, Vehicle Category is the column heading, and Sum of number sold is the value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sagobogger]

OK, that's a great start - thank you!

I tried this with Northwind,

SupplierID as Row Heading
CategoryID as Column Heading
Sum of UnitsInStock as Value

It works but my columns are 1, 2, 3, etc. How do I get the value of CategoryID as the column header?

 

[dhookom]

1,2,3 are your categoryID values. MS use the ultra-horrible lookup fields in the table design of the Products (and other) tables.

If you wish to view the category name/title, you must add the table containing this value to your query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sagobogger]

Perfect, thanks again