Group by month then count rows

[Matilda]

Dear All,

I need to write a query (as opposed to a report) that totals records for
each month. My table has concatenated date/time fields.

I can count the total records using count(*), but all attempts at grouping
run up against a syntax error.

This is what I have thus far that will run:

SELECT tblUpdatesDistinct.[a_table ActArrTime], Format([a_table
ActArrTime],"mm") AS ArrMnth
FROM tblUpdatesDistinct;

Can't seem to find this problem discussed anywhere - maybe because it is so
simple and obvious I am the only person alive brave enough to ask the
question!

Any help appreciated,

Regards,

Matilda

 

[Duane Hookom]

Is this what you are looking for?

SELECT Month([a_table ActArrTime]) AS ArrMnth, Count(*) as NumOf
FROM tblUpdatesDistinct
GROUP BY Month([a_table ActArrTime]);

Very unusual field names.

 

[Matilda]

Thanks, Duane. Yes it works ... and I can see my stupid syntax errors now.

Many thanks.

What unusual field names ?? )

a_table supplies one value and b_table supplies the other - they are tables
from an extract which is submitted at time intervals containing transactions
to that period of time. Each new submission contains old transactions (some
of which are edited for data quality in the meantime), and the new ones. The
fieldnames are the same in all the tables. My task is to compare submissions,
and group and count by month over a whole year. Does that make sense? If you
have a better naming convention would love to hear it.

The sql statement very much appreciated - can get on now.

Many thanks,

Matilda

Is this what you are looking for?

SELECT Month([a_table ActArrTime]) AS ArrMnth, Count(*) as NumOf
FROM tblUpdatesDistinct
GROUP BY Month([a_table ActArrTime]);

Very unusual field names.

--
Duane Hookom
Microsoft Access MVP

Dear All,

I need to write a query (as opposed to a report) that totals records for
each month. My table has concatenated date/time fields.

I can count the total records using count(*), but all attempts at grouping
run up against a syntax error.

This is what I have thus far that will run:

SELECT tblUpdatesDistinct.[a_table ActArrTime], Format([a_table
ActArrTime],"mm") AS ArrMnth
FROM tblUpdatesDistinct;

Can't seem to find this problem discussed anywhere - maybe because it is so
simple and obvious I am the only person alive brave enough to ask the
question!

Any help appreciated,

Regards,

Matilda

 

[Duane Hookom]

I noticed "table" in a field name. That's what caused my comment. I also
never put spaces in any object names.

--
Duane Hookom
Microsoft Access MVP

Thanks, Duane. Yes it works ... and I can see my stupid syntax errors now.

Many thanks.

What unusual field names ?? )

a_table supplies one value and b_table supplies the other - they are tables
from an extract which is submitted at time intervals containing transactions
to that period of time. Each new submission contains old transactions (some
of which are edited for data quality in the meantime), and the new ones. The
fieldnames are the same in all the tables. My task is to compare submissions,
and group and count by month over a whole year. Does that make sense? If you
have a better naming convention would love to hear it.

The sql statement very much appreciated - can get on now.

Many thanks,

Matilda

Is this what you are looking for?

SELECT Month([a_table ActArrTime]) AS ArrMnth, Count(*) as NumOf
FROM tblUpdatesDistinct
GROUP BY Month([a_table ActArrTime]);

Very unusual field names.

--
Duane Hookom
Microsoft Access MVP

Dear All,

I need to write a query (as opposed to a report) that totals records for
each month. My table has concatenated date/time fields.

I can count the total records using count(*), but all attempts at grouping
run up against a syntax error.

This is what I have thus far that will run:

SELECT tblUpdatesDistinct.[a_table ActArrTime], Format([a_table
ActArrTime],"mm") AS ArrMnth
FROM tblUpdatesDistinct;

Can't seem to find this problem discussed anywhere - maybe because it is so
simple and obvious I am the only person alive brave enough to ask the
question!

Any help appreciated,

Regards,

Matilda

 

[Matilda]

Noted.

Well said.

I noticed "table" in a field name. That's what caused my comment. I also
never put spaces in any object names.

--
Duane Hookom
Microsoft Access MVP

Thanks, Duane. Yes it works ... and I can see my stupid syntax errors now.

Many thanks.

What unusual field names ?? )

a_table supplies one value and b_table supplies the other - they are tables
from an extract which is submitted at time intervals containing transactions
to that period of time. Each new submission contains old transactions (some
of which are edited for data quality in the meantime), and the new ones. The
fieldnames are the same in all the tables. My task is to compare submissions,
and group and count by month over a whole year. Does that make sense? If you
have a better naming convention would love to hear it.

The sql statement very much appreciated - can get on now.

Many thanks,

Matilda

Is this what you are looking for?

SELECT Month([a_table ActArrTime]) AS ArrMnth, Count(*) as NumOf
FROM tblUpdatesDistinct
GROUP BY Month([a_table ActArrTime]);

Very unusual field names.

--
Duane Hookom
Microsoft Access MVP


:

Dear All,

I need to write a query (as opposed to a report) that totals records for
each month. My table has concatenated date/time fields.

I can count the total records using count(*), but all attempts at grouping
run up against a syntax error.

This is what I have thus far that will run:

SELECT tblUpdatesDistinct.[a_table ActArrTime], Format([a_table
ActArrTime],"mm") AS ArrMnth
FROM tblUpdatesDistinct;

Can't seem to find this problem discussed anywhere - maybe because it is so
simple and obvious I am the only person alive brave enough to ask the
question!

Any help appreciated,

Regards,

Matilda