How do I count the values in a column?

[Rashar Sharro via AccessMonster.com]

Hi,

I have a query called qryCustomers.
I have a combo box called cboShip_Month. When the user selects Jan in the
combo box, a 1 is placed in my table under the Ship_Month column for each
customer; for Feb, a 2 will be placed, for Mar, a 3 will be placed etc...

What I would like to do is summarize how many months were selected for each
of the following months. I tried using the Count function, but the results
returned are blank.

Here is my script:

SELECT Count(qryCustomers.Ship_Month) AS Jan, Count(qryCustomers.Ship_Month)
AS Feb
FROM qryCustomers
HAVING (((Count(qryCustomers.Ship_Month))=1) AND ((Count(qryCustomers.
Ship_Month))=2));

Thanks in advance.

 

[Jeff Boyce]

Why are you restricting the query to those situations where the ShipMonth is
"1" (your query mentioned "Feb"), and to those instances when there are
exactly two records (Count(...)=2)?

Regards

Jeff Boyce
<Access MVP>

 

[Rashar Sharro via AccessMonster.com]

The query is restricted to this situation because I'm dealing with one column
for now. So I want to tally up the number of Jan months, and tally up Feb
months sepeartely. Ultimately, I should have 2 separte columns 1, Jan, and
the other Feb, and then tally up those...but for now, I want to see if there
is away to sum the months from a single column...

Thanks,

Robert

Why are you restricting the query to those situations where the ShipMonth is
"1" (your query mentioned "Feb"), and to those instances when there are
exactly two records (Count(...)=2)?

Regards

Jeff Boyce

Hi,

[quoted text clipped - 18 lines]

Thanks in advance.

 

[Jeff Boyce]

I am unclear on your underlying data structure, which forms the basis for
any query or form you'd use.

I believe your query, as originally posted, is returning zero rows because
none of your data meets the criteria you are setting.

If I understand your post (below) correctly, you are considering using
different columns for different months -- if so, don't!

If you are using a combo box to pick a month (as your initial post
indicated), how will you handle next year's data, when the same months will
be picked again?

(An alternate approach would be to record a date in your table, and use the
Month() and Year() functions against that date field.)

Regards

Jeff Boyce
<Access MVP>

The query is restricted to this situation because I'm dealing with one
column
for now. So I want to tally up the number of Jan months, and tally up Feb
months sepeartely. Ultimately, I should have 2 separte columns 1, Jan, and
the other Feb, and then tally up those...but for now, I want to see if
there
is away to sum the months from a single column...

Thanks,

Robert

Why are you restricting the query to those situations where the ShipMonth
is
"1" (your query mentioned "Feb"), and to those instances when there are
exactly two records (Count(...)=2)?

Regards

Jeff Boyce

Hi,

[quoted text clipped - 18 lines]

Thanks in advance.

 

[Rashar Sharro via AccessMonster.com]

Ok,

I have a query that produces the following data:

Ship_Month
1
2
1
3
1
2
3
1
2
2

So, as mentioned before, 1 represents Jan, 2 Feb, 3 Mar. You are correct, I
am pulling the data from a combo box.

What I want to do, is count the number of Jan months that were selected from
the combo box, and place that total in another column.

Would I not be able to make an alias column such as Jan:Ship_Month, and pass
the total COUNT for Jan in that column?

I hope this is more clear.

Thanks for your help.

I am unclear on your underlying data structure, which forms the basis for
any query or form you'd use.

I believe your query, as originally posted, is returning zero rows because
none of your data meets the criteria you are setting.

If I understand your post (below) correctly, you are considering using
different columns for different months -- if so, don't!

If you are using a combo box to pick a month (as your initial post
indicated), how will you handle next year's data, when the same months will
be picked again?

(An alternate approach would be to record a date in your table, and use the
Month() and Year() functions against that date field.)

Regards

Jeff Boyce

The query is restricted to this situation because I'm dealing with one
column

[quoted text clipped - 23 lines]

 

[Jeff Boyce]

Another point of clarification.

You are not getting your data from your combo box. Your combo box is
putting data in a table. Your query should be looking at a table or a
query.

Take a look at a "totals" query, Group By ShipMonth, and Count ShipMonth.

Regards

Jeff Boyce
<Access MVP>

Ok,

I have a query that produces the following data:

Ship_Month
1
2
1
3
1
2
3
1
2
2

So, as mentioned before, 1 represents Jan, 2 Feb, 3 Mar. You are correct,
I
am pulling the data from a combo box.

What I want to do, is count the number of Jan months that were selected
from
the combo box, and place that total in another column.

Would I not be able to make an alias column such as Jan:Ship_Month, and
pass
the total COUNT for Jan in that column?

I hope this is more clear.

Thanks for your help.

I am unclear on your underlying data structure, which forms the basis for
any query or form you'd use.

I believe your query, as originally posted, is returning zero rows because
none of your data meets the criteria you are setting.

If I understand your post (below) correctly, you are considering using
different columns for different months -- if so, don't!

If you are using a combo box to pick a month (as your initial post
indicated), how will you handle next year's data, when the same months
will
be picked again?

(An alternate approach would be to record a date in your table, and use
the
Month() and Year() functions against that date field.)

Regards

Jeff Boyce

The query is restricted to this situation because I'm dealing with one
column

[quoted text clipped - 23 lines]

Thanks in advance.

 

[John Spencer (MVP)]

Pardon me for butting in, but this should be a simple totals query. Using your
posted query as a sample.

SELECT qryCustomers.Ship_Month as MonthNumber,
Count(qryCustomers.Ship_Month) AS MonthCount
FROM qryCustomers
WHERE qryCustomers.Ship_Month in (1,2)
GROUP BY qryCustomers.Ship_Month

That should return
MonthNumber MonthCount
1 4
2 4

And if you remove the WHERE clause it will return all the months represented in
the table.

Ok,

I have a query that produces the following data:

Ship_Month
1
2
1
3
1
2
3
1
2
2

So, as mentioned before, 1 represents Jan, 2 Feb, 3 Mar. You are correct, I
am pulling the data from a combo box.

What I want to do, is count the number of Jan months that were selected from
the combo box, and place that total in another column.

Would I not be able to make an alias column such as Jan:Ship_Month, and pass
the total COUNT for Jan in that column?

I hope this is more clear.

Thanks for your help.

I am unclear on your underlying data structure, which forms the basis for
any query or form you'd use.

I believe your query, as originally posted, is returning zero rows because
none of your data meets the criteria you are setting.

If I understand your post (below) correctly, you are considering using
different columns for different months -- if so, don't!

If you are using a combo box to pick a month (as your initial post
indicated), how will you handle next year's data, when the same months will
be picked again?

(An alternate approach would be to record a date in your table, and use the
Month() and Year() functions against that date field.)

Regards

Jeff Boyce

The query is restricted to this situation because I'm dealing with one
column

[quoted text clipped - 23 lines]

Thanks in advance.