Access Crosstab with more than one value

[tbac]

I have a table where each record has a 'Country'field and a 'Product' field. I want to create a spreadsheet like table with each country across the top and the products along the side. A crosstab query can do that but I would like more than one value for each combination

 

[THWatson]

tbac
I'm not sure what you mean by "wanting more than one value for each combination". However, here is one approach to getting your Products down the side and each Country across the top.

Create a crosstab query. Use Product for the Row Heading, and Group By. Use Country for the Column Heading and Group By. Use County again for the Value, and then Count.

View the Query Properties, and then enter the Column Headings you would like to see. This will construct the query in Fixed Column Headings. For example, enter, in the Column Headings property, "Canada";"England";"Australia";"France". As many countries as you wish.

Hope that helps.

Tom

 

[AccessExcelHell]

THWatson,

I have a similar question/issue. When you create a CrossTab Query - you can only get 1 field's values - displayed/calculated. So if i want to know the sum of amount, and count of amount per Counrty for example - the CrossTab will only provide 1.

ex..
Product Countrty
COunt Sum Count Sum

USA UK Japan
Milk 300 $6000 200 $4000 100 $3000
Eggs 1 $2.5 3 $7.5 5 $12.5
etc

the Crosstab will only give the Count or the Sum per Country not both.

Any Ideas?
Thanks

 

[THWatson]

AccessExcelHell
I just made a little table with 3 fields - Country, Product, Amount (currency). Then I entered data in the fields.

For my Crosstab query, I used the following...
Row Heading is Country, Group By
Column Heading is Product, Group By
Value is Amount, Sum
Row Heading is Product, Count

That seems to be what you want.

Tom