Query Counts by Days not Months

[LeslieJ via AccessMonster.com]

Hi,

Below is the SQL for a query that I have that sums how many documents were
issued to a department for a specific month. The users select the month and
year from a form via combo boxes (that way they cannot select more than one
month for a date range). Then I have hidden text boxes that get updated
after the combo boxes are chosen with the afterupdate procedure:
Me.txtStartDate = DateSerial(Year(Date), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Year(Date), Me.cboMonth.Column(0) + 1, 0)

The query works as expected, and it shows each day of the month that a
department received a document and sums how many were received on that day.

I was curious to know if there is a way to have the query sum the totals for
the whole month, and not split it up by day?

So instead of seeing:
Date of Initial Issue| Department Abbr| Count
18 Jul 08 | Dept1 | 1
18 Jul 08 | Dept2 | 1
19 Jul 08 | Dept1 | 1

We would see:
Department Abbr| Count
Dept1 | 2
Dept2 | 1

Thanks for your help in advance.

SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution Information].
[Document Version ID]
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
HAVING ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And [Forms]![frmDan'sMonthly]!
[txtEndDate]))
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[KARL DEWEY]

Try this --
SELECT [Document Holder Information].[Department Abbreviation],
Count([Document Holder Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
GROUP BY [Document Holder Information].[Department Abbreviation]
HAVING ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And [Forms]![frmDan'sMonthly]!
[txtEndDate]))
ORDER BY [Document Holder Information].[Department Abbreviation];

--
KARL DEWEY
Build a little - Test a little

Hi,

Below is the SQL for a query that I have that sums how many documents were
issued to a department for a specific month. The users select the month and
year from a form via combo boxes (that way they cannot select more than one
month for a date range). Then I have hidden text boxes that get updated
after the combo boxes are chosen with the afterupdate procedure:
Me.txtStartDate = DateSerial(Year(Date), Me.cboMonth.Column(0), 1)
Me.txtEndDate = DateSerial(Year(Date), Me.cboMonth.Column(0) + 1, 0)

The query works as expected, and it shows each day of the month that a
department received a document and sums how many were received on that day.

I was curious to know if there is a way to have the query sum the totals for
the whole month, and not split it up by day?

So instead of seeing:
Date of Initial Issue| Department Abbr| Count
18 Jul 08 | Dept1 | 1
18 Jul 08 | Dept2 | 1
19 Jul 08 | Dept1 | 1

We would see:
Department Abbr| Count
Dept1 | 2
Dept2 | 1

Thanks for your help in advance.

SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution Information].
[Document Version ID]
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
HAVING ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And [Forms]![frmDan'sMonthly]!
[txtEndDate]))
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[LeslieJ via AccessMonster.com]

Hi Karl,

Thanks for your help.

I tried the SQL that you left for me. I get a Syntax Error in Join Operation
and it highlights the following [Document Distribution Information].
[Department Holders Abbreviations] in the following excerpt:
ON [Document_
Distribution Information].[Department Holders Abbreviations] = [Document_
Holder Information].[Department Abbreviation]) ON

Any thoughts?

Thanks!

Try this --
SELECT [Document Holder Information].[Department Abbreviation],
Count([Document Holder Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
GROUP BY [Document Holder Information].[Department Abbreviation]
HAVING ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And [Forms]![frmDan'sMonthly]!
[txtEndDate]))
ORDER BY [Document Holder Information].[Department Abbreviation];

Hi,

[quoted text clipped - 41 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[KARL DEWEY]

Your post --
ON [Document_
Distribution Information].[Department Holders Abbreviations] = [Document_
Holder Information].[Department Abbreviation]) ON
is showing underscores in the table names. Is that a typo in
posting or in the SQL?
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Thanks for your help.

I tried the SQL that you left for me. I get a Syntax Error in Join Operation
and it highlights the following [Document Distribution Information].
[Department Holders Abbreviations] in the following excerpt:
ON [Document_
Distribution Information].[Department Holders Abbreviations] = [Document_
Holder Information].[Department Abbreviation]) ON

Any thoughts?

Thanks!

Try this --
SELECT [Document Holder Information].[Department Abbreviation],
Count([Document Holder Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
GROUP BY [Document Holder Information].[Department Abbreviation]
HAVING ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And [Forms]![frmDan'sMonthly]!
[txtEndDate]))
ORDER BY [Document Holder Information].[Department Abbreviation];

Hi,

[quoted text clipped - 41 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[LeslieJ via AccessMonster.com]

I put those in so that you knew it was just a break that the word wrapping
created and that I didn't put extra spaces where they shouldn't be (it was
put in another post to do that with other code - sorry). It really just
looks like:

Your post --
ON [Document_
Distribution Information].[Department Holders Abbreviations] = [Document_
Holder Information].[Department Abbreviation]) ON
is showing underscores in the table names. Is that a typo in
posting or in the SQL?

Hi Karl,

[quoted text clipped - 31 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[KARL DEWEY]

Try this --
SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
WHERE ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And
[Forms]![frmDan'sMonthly]![txtEndDate]))
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

--
KARL DEWEY
Build a little - Test a little

I put those in so that you knew it was just a break that the word wrapping
created and that I didn't put extra spaces where they shouldn't be (it was
put in another post to do that with other code - sorry). It really just
looks like:

Your post --
ON [Document_
Distribution Information].[Department Holders Abbreviations] = [Document_
Holder Information].[Department Abbreviation]) ON
is showing underscores in the table names. Is that a typo in
posting or in the SQL?

Hi Karl,

[quoted text clipped - 31 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[LeslieJ via AccessMonster.com]

Hi Karl, I'm sorry I was sort of taken away from this for awhile.

I tried the SQL that you posted, however, it still is separating by day and
not grouping by month.

Does anyone have any insight to this or am I asking of Access something that
can't be done?

Try this --
SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
WHERE ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And
[Forms]![frmDan'sMonthly]![txtEndDate]))
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

I put those in so that you knew it was just a break that the word wrapping
created and that I didn't put extra spaces where they shouldn't be (it was

[quoted text clipped - 14 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[جورج اسطÙانوس جريس]

Hi Karl, I'm sorry I was sort of taken away from this for awhile.

I tried the SQL that you posted, however, it still is separating by day
and
not grouping by month.

Does anyone have any insight to this or am I asking of Access something
that
can't be done?

Try this --
SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
WHERE ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And
[Forms]![frmDan'sMonthly]![txtEndDate]))
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

I put those in so that you knew it was just a break that the word
wrapping
created and that I didn't put extra spaces where they shouldn't be (it
was

[quoted text clipped - 14 lines]

ORDER BY [Document Version Information].[Date of Initial Issue],
[Document
Holder Information].[Department Abbreviation];

 

[John Smith]

An aggregate query has to group by all of the columns that you are selecting.
If you don't want to group by date then remove [Date of Initial Issue] from
the SELECT, GROUP BY and ORDER BY clauses.

If you were reporting on more than one month you could still do it but you
would need to format the date to remove the day, e.g. Format$([Date of Initial
Issue], 'yyyy/mm') rather than removing the field.

HTH
John
##################################
Don't Print - Save trees

Hi Karl, I'm sorry I was sort of taken away from this for awhile.

I tried the SQL that you posted, however, it still is separating by day and
not grouping by month.

Does anyone have any insight to this or am I asking of Access something that
can't be done?

Try this --
SELECT [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation], Count([Document Holder
Information].[Department Holder Identifier]) AS [Count]
FROM [Document Version Information] INNER JOIN ([Document Distribution
Information] INNER JOIN [Document Holder Information] ON [Document
Distribution Information].[Department Holders Abbreviations] = [Document
Holder Information].[Department Abbreviation]) ON [Document Version
Information].[Version Identifier] = [Document Distribution
Information].[Document Version ID]
WHERE ((([Document Version Information].[Date of Initial Issue]) Between
[Forms]![frmDan'sMonthly]![txtStartDate] And
[Forms]![frmDan'sMonthly]![txtEndDate]))
GROUP BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation]
ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

I put those in so that you knew it was just a break that the word wrapping
created and that I didn't put extra spaces where they shouldn't be (it was

[quoted text clipped - 14 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];

 

[LeslieJ via AccessMonster.com]

Thank you so much John, I truly appreciate it!

An aggregate query has to group by all of the columns that you are selecting.
If you don't want to group by date then remove [Date of Initial Issue] from
the SELECT, GROUP BY and ORDER BY clauses.

If you were reporting on more than one month you could still do it but you
would need to format the date to remove the day, e.g. Format$([Date of Initial
Issue], 'yyyy/mm') rather than removing the field.

HTH
John
##################################
Don't Print - Save trees

Hi Karl, I'm sorry I was sort of taken away from this for awhile.

[quoted text clipped - 27 lines]

ORDER BY [Document Version Information].[Date of Initial Issue], [Document
Holder Information].[Department Abbreviation];