Use Date to Show Month Only

S

SamBell

I have changed my field name to "Date of Issue," but I must not be adjusting
your "where" and "order by" clauses correctly because I am getting error
messages. What would the "where" and "order by" clauses look like given my
current SQL:

SELECT [Worksheet 2 starting July 2008].[Department of Origin], Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July 2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July 2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm");

Sam
 
D

Douglas J. Steele

SELECT [Worksheet 2 starting July 2008].[Department of Origin], Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July 2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July 2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

If that doesn't work, what are the actual error messages?
 
S

SamBell

"You tried to execute a query that does not include the specified expression
'Month([Date of Issue])' as part of an aggregate function."

Sam

Douglas J. Steele said:
SELECT [Worksheet 2 starting July 2008].[Department of Origin], Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July 2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July 2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

If that doesn't work, what are the actual error messages?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SamBell said:
I have changed my field name to "Date of Issue," but I must not be
adjusting
your "where" and "order by" clauses correctly because I am getting error
messages. What would the "where" and "order by" clauses look like given my
current SQL:

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm");

Sam
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Hmm. You may have to include that numeric month field in your query as well.

SELECT [Worksheet 2 starting July 2008].[Department of Origin], Format([Date
of Issue],"mmmm") AS [NameOfMonth],Month([Date of Issue]) AS MonthNumber
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July 2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July 2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm"), Month([Date of Issue])
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

Note that I renamed your Alias for Format([Date of Issue], "mmmm"). Month is
a reserved word as well...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SamBell said:
"You tried to execute a query that does not include the specified
expression
'Month([Date of Issue])' as part of an aggregate function."

Sam

Douglas J. Steele said:
SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

If that doesn't work, what are the actual error messages?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SamBell said:
I have changed my field name to "Date of Issue," but I must not be
adjusting
your "where" and "order by" clauses correctly because I am getting
error
messages. What would the "where" and "order by" clauses look like given
my
current SQL:

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm");

Sam
Click to expand...
Click to expand...
Click to expand...
 
S

SamBell

I changed "month" to "NameOfMonth," but now it is prompting for a parameter.

Douglas J. Steele said:
Hmm. You may have to include that numeric month field in your query as well.

SELECT [Worksheet 2 starting July 2008].[Department of Origin], Format([Date
of Issue],"mmmm") AS [NameOfMonth],Month([Date of Issue]) AS MonthNumber
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July 2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July 2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm"), Month([Date of Issue])
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

Note that I renamed your Alias for Format([Date of Issue], "mmmm"). Month is
a reserved word as well...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SamBell said:
"You tried to execute a query that does not include the specified
expression
'Month([Date of Issue])' as part of an aggregate function."

Sam

Douglas J. Steele said:
SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

If that doesn't work, what are the actual error messages?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have changed my field name to "Date of Issue," but I must not be
adjusting
your "where" and "order by" clauses correctly because I am getting
error
messages. What would the "where" and "order by" clauses look like given
my
current SQL:

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm");

Sam
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What's it prompting for? (The popup box should have the details in it)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


SamBell said:
I changed "month" to "NameOfMonth," but now it is prompting for a
parameter.

Douglas J. Steele said:
Hmm. You may have to include that numeric month field in your query as
well.

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [NameOfMonth],Month([Date of Issue]) AS MonthNumber
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm"), Month([Date of Issue])
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

Note that I renamed your Alias for Format([Date of Issue], "mmmm"). Month
is
a reserved word as well...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SamBell said:
"You tried to execute a query that does not include the specified
expression
'Month([Date of Issue])' as part of an aggregate function."

Sam

:

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
WHERE [Date of Issue] BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date of Issue])

If that doesn't work, what are the actual error messages?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have changed my field name to "Date of Issue," but I must not be
adjusting
your "where" and "order by" clauses correctly because I am getting
error
messages. What would the "where" and "order by" clauses look like
given
my
current SQL:

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date
of Issue],"mmmm") AS [Month],
Sum(Timeliness.Timeliness) AS Timeliness,
Sum(Accuracy.Accuracy) AS Accuracy,
Sum([Customer Service].[Customer Service]) AS [Customer Service],
Sum([Quality of Product].[Quality of Product]) AS [Quality of
Product]
FROM Timeliness
INNER JOIN ([Quality of Product]
INNER JOIN ([Customer Service]
INNER JOIN (Accuracy
INNER JOIN [Worksheet 2 starting July 2008]
ON Accuracy.[Yes/No] = [Worksheet 2 starting July 2008].Accuracy)
ON [Customer Service].[Yes/No] = [Worksheet 2 starting July
2008].[Customer
Service])
ON [Quality of Product].[Yes/No] = [Worksheet 2 starting July
2008].[Quality
of Product])
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date of Issue],"mmmm");

Sam
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autofill Date in Word 15.33 for Mac 0
How to change date format to 7 working days a week in ms project 2010? 0
Project Status Date 1
Excel does not accept text format! 0
Dcount -Date Criteria - Won´t find it - 0
Consolidation Table from Different Excel Tabs with Different Format 1
Microsoft Project 2016 Monthly Cost Report/ Table Creation Help 0
Date/Time to Display only Month 7

Top