How to make total columns for ONE field. HELP.

[Lejano08]

Hello

I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.
I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this

Place 1 Place 2 Place 3 Place 4
13............36.........3........77

So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?

Please help I've been googling for hours and can't seem to find a query that will look like this. One row. Just totals.

 

[plog]

Most likely you want a Cross-Tab query: https://support.office.com/en-ca/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-CA&ad=CA

If you could provide more information about the table its built on, we can help you more specifically. Give us some sample data from your table (include table and field names) and then what you expect your final results to look like based on that initial sample data.

 

[Brianwarnock]

This is the classic Sum(IIF ...in each column

Eg sum(iif(field1="A",1,0))

Thus if there is a match then 1 is added if not 0, you will need to convert to your requirements but that's the approach

Brian

 

[Lejano08]

Thanks plog. I made a cross tab query , but I had to have a row heading. I used appointment date. So now there are 28 rows (one for each date). The table is made up information from a form about a patient transportation request (to get a ride to their appointment). We have appointment date, time, patient address info, destination address info, etc.
We have a dropdown of the common places patients travel to, as well as an "OTHER" choice if they wish to go to a hospital not on the list. (When other is chosen, another box pops up for them to type in their destination address).
I only want to see the total number of instances where Hospital 1 was chosen, Hospital 2 was chosen,... and number of instances where OTHER was chosen. I do not wish to see this according to date or anything. Just totals for those choices.

The crosstab looks something like this

||||||| Hosp 1 Hosp 2 Hosp 3 Hosp 4 OTHER
6/25 ........1...........................................
6/26 ...............2............4.......................
6/27.......2.........1...........1.........1.........5
6/28 ......3........1..........5..........6..........1

However, I'm not interested in sorting this by date. I just want the Total number of times someone picked each place. So there would only be one row (four rows are shown here, because of the four dates). Crosstab queries require you to have a row heading and i don't want one. Is there a way around this?

 

[Lejano08]

In design view, everything looks like this:

Field: Appt Date
Table: Patient
Total: Group By
Crosstab: Row Heading

Field: Destination
Table: "
Total: "
Crosstab: Column Heading

Field: "
Table: "
Total: Count
Crosstab: Value

 

[plog]

Instead of your Date field for the Row Heading, use this:

RowValue: 1

 

[Lejano08]

Oh my God.
Marry me.

I mean thank you!

Now, is there any way to flip this exact setup into a chart?
I think with PivotCharts you have to do a pivotTable first, and I think it still makes you choose a row heading, which I don't want.

 

[plog]

If you want a chart, you should build a normal query and use that as the data source not a cross-tab.

 

[Brianwarnock]

Try the way I suggested in post3