Use Date to Show Month Only

[SamBell]

Is there a way to use a date field to put the month name as text in a
separate column in the same table?

 

[Beetle]

Format([YourDateField], "mmmm")

 

[SamBell]

That doesn't seem to be working, and I need the month name to be text.

Sam

Format([YourDateField], "mmmm")
--
_________

Sean Bailey

Is there a way to use a date field to put the month name as text in a
separate column in the same table?

 

[Douglas J. Steele]

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function as
Sean showed. Use the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That doesn't seem to be working, and I need the month name to be text.

Sam

Format([YourDateField], "mmmm")
--
_________

Sean Bailey

Is there a way to use a date field to put the month name as text in a
separate column in the same table?

 

[SamBell]

The Query is showing the month names, but I need them as text so I can group
them. Is there a way to convert them to text?

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function as
Sean showed. Use the query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That doesn't seem to be working, and I need the month name to be text.

Sam

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as text in a
separate column in the same table?

 

[Douglas J. Steele]

The format function returns a string, even if all its showing is numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Query is showing the month names, but I need them as text so I can
group
them. Is there a way to convert them to text?

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function as
Sean showed. Use the query wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That doesn't seem to be working, and I need the month name to be text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as text in
a
separate column in the same table?

 

[SamBell]

Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer Service, and
Quality of Product

The first two fields are entered directly into a form, the last four fields
are entered by checking or not checking a box which is then assigned a value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to figure
out a way to group by month.

Sam

The format function returns a string, even if all its showing is numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Query is showing the month names, but I need them as text so I can
group
them. Is there a way to convert them to text?

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function as
Sean showed. Use the query wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to be text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as text in
a
separate column in the same table?

 

[Douglas J. Steele]

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table. You'd
drag the six fields from the table into the grid. For the second field (the
Date field), you'd change what's in the box from the name of the field to
the formula shown. You'd convert the query to a Totals query (if you're
using Access 2003 or earlier, this can be done by selecting Totals from the
View menu: afraid I can't remember how it's done in Access 2007) Doing that
will add a new row "Total" to the grid. You'd leave the default Group By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a reserved
word, and should never be used for your own purposes. For a comprehensive
list of names to avoid (plus a link to a free utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer Service, and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to figure
out a way to group by month.

Sam

The format function returns a string, even if all its showing is numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Query is showing the month names, but I need them as text so I can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function
as
Sean showed. Use the query wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as text
in
a
separate column in the same table?

 

[John W. Vinson]

Is there a way to use a date field to put the month name as text in a
separate column in the same table?

On a Form, yes, but you should not need to ever do this. If the month name can
be derived from the date field, it should be - otherwise you'll end up with
#11/13/2008# stored in the datefield and "April" stored in the month name
field!

If you're using table datasheets to view and edit data.... don't. That's not
their purpose and they don't work well for that purpose. Use a Form instead;
on a Form you can put a textbox with a Format property of "mmmm", bound to the
date field. I'd suggest setting its Enabled property to No, Locked to Yes so
that the user can't use it to change the date (they'll use another control
showing the whole date should they need to do so).

 

[SamBell]

I have a query like that already, the problem I am having is that it is still
grouping the dates by exact date and not by month, even though that is what
is shown. That is why I was looking for a way to convert the month name to a
text field; that way "October" would just be "October", rather than "10/2/08"
and "10/31/08" both showing "October"

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table. You'd
drag the six fields from the table into the grid. For the second field (the
Date field), you'd change what's in the box from the name of the field to
the formula shown. You'd convert the query to a Totals query (if you're
using Access 2003 or earlier, this can be done by selecting Totals from the
View menu: afraid I can't remember how it's done in Access 2007) Doing that
will add a new row "Total" to the grid. You'd leave the default Group By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a reserved
word, and should never be used for your own purposes. For a comprehensive
list of names to avoid (plus a link to a free utility that will check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer Service, and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to figure
out a way to group by month.

Sam

The format function returns a string, even if all its showing is numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Query is showing the month names, but I need them as text so I can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format function
as
Sean showed. Use the query wherever you would otherwise have used the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as text
in
a
separate column in the same table?

 

[Douglas J. Steele]

Let's see the SQL of your query.

The query I've shown cannot possibly exhibit the behaviour you're
describing. You must have both the Date field and the Format statement in
your query, as opposed to only the Format statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query like that already, the problem I am having is that it is
still
grouping the dates by exact date and not by month, even though that is
what
is shown. That is why I was looking for a way to convert the month name to
a
text field; that way "October" would just be "October", rather than
"10/2/08"
and "10/31/08" both showing "October"

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table. You'd
drag the six fields from the table into the grid. For the second field
(the
Date field), you'd change what's in the box from the name of the field to
the formula shown. You'd convert the query to a Totals query (if you're
using Access 2003 or earlier, this can be done by selecting Totals from
the
View menu: afraid I can't remember how it's done in Access 2007) Doing
that
will add a new row "Total" to the grid. You'd leave the default Group By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a reserved
word, and should never be used for your own purposes. For a comprehensive
list of names to avoid (plus a link to a free utility that will check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer Service,
and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue
fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for
each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to
figure
out a way to group by month.

Sam

:

The format function returns a string, even if all its showing is
numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Query is showing the month names, but I need them as text so I
can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format
function
as
Sean showed. Use the query wherever you would otherwise have used
the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as
text
in
a
separate column in the same table?

 

[SamBell]

SELECT [Worksheet 2 starting July 2008].[Department of Origin], [Worksheet 2
starting July 2008].Date, 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 [Quality of Product] INNER JOIN ([Customer Service] INNER JOIN
(Accuracy INNER JOIN (Timeliness INNER JOIN [Worksheet 2 starting July 2008]
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness) 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]
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin], [Worksheet
2 starting July 2008].Date
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin], [Worksheet
2 starting July 2008].Date;

Let's see the SQL of your query.

The query I've shown cannot possibly exhibit the behaviour you're
describing. You must have both the Date field and the Format statement in
your query, as opposed to only the Format statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query like that already, the problem I am having is that it is
still
grouping the dates by exact date and not by month, even though that is
what
is shown. That is why I was looking for a way to convert the month name to
a
text field; that way "October" would just be "October", rather than
"10/2/08"
and "10/31/08" both showing "October"

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table. You'd
drag the six fields from the table into the grid. For the second field
(the
Date field), you'd change what's in the box from the name of the field to
the formula shown. You'd convert the query to a Totals query (if you're
using Access 2003 or earlier, this can be done by selecting Totals from
the
View menu: afraid I can't remember how it's done in Access 2007) Doing
that
will add a new row "Total" to the grid. You'd leave the default Group By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a reserved
word, and should never be used for your own purposes. For a comprehensive
list of names to avoid (plus a link to a free utility that will check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer Service,
and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue
fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for
each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to
figure
out a way to group by month.

Sam

:

The format function returns a string, even if all its showing is
numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Query is showing the month names, but I need them as text so I
can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format
function
as
Sean showed. Use the query wherever you would otherwise have used
the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as
text
in
a
separate column in the same table?

 

[Douglas J. Steele]

You're not using the Format function! Take a look at my query again to see
the difference.

You really should consider renaming your date field as well. Date is a
reserved word, and using it for your own purposes can lead to problems. For
a comprehensive list of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT [Worksheet 2 starting July 2008].[Department of Origin], [Worksheet
2
starting July 2008].Date, 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 [Quality of Product] INNER JOIN ([Customer Service] INNER JOIN
(Accuracy INNER JOIN (Timeliness INNER JOIN [Worksheet 2 starting July
2008]
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness) 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]
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
[Worksheet
2 starting July 2008].Date
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
[Worksheet
2 starting July 2008].Date;

Let's see the SQL of your query.

The query I've shown cannot possibly exhibit the behaviour you're
describing. You must have both the Date field and the Format statement in
your query, as opposed to only the Format statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a query like that already, the problem I am having is that it is
still
grouping the dates by exact date and not by month, even though that is
what
is shown. That is why I was looking for a way to convert the month name
to
a
text field; that way "October" would just be "October", rather than
"10/2/08"
and "10/31/08" both showing "October"

:

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table.
You'd
drag the six fields from the table into the grid. For the second field
(the
Date field), you'd change what's in the box from the name of the field
to
the formula shown. You'd convert the query to a Totals query (if
you're
using Access 2003 or earlier, this can be done by selecting Totals
from
the
View menu: afraid I can't remember how it's done in Access 2007) Doing
that
will add a new row "Total" to the grid. You'd leave the default Group
By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a
reserved
word, and should never be used for your own purposes. For a
comprehensive
list of names to avoid (plus a link to a free utility that will check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer
Service,
and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned
a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue
fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for
each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to
figure
out a way to group by month.

Sam

:

The format function returns a string, even if all its showing is
numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Query is showing the month names, but I need them as text so
I
can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format
function
as
Sean showed. Use the query wherever you would otherwise have
used
the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to
be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as
text
in
a
separate column in the same table?

 

[SamBell]

I'm not sure that I understand how/why it works, but it definitely works.
Thanks for your help.

Sam

You're not using the Format function! Take a look at my query again to see
the difference.

You really should consider renaming your date field as well. Date is a
reserved word, and using it for your own purposes can lead to problems. For
a comprehensive list of names to avoid (as well as a link to a free utility
that will check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT [Worksheet 2 starting July 2008].[Department of Origin], [Worksheet
2
starting July 2008].Date, 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 [Quality of Product] INNER JOIN ([Customer Service] INNER JOIN
(Accuracy INNER JOIN (Timeliness INNER JOIN [Worksheet 2 starting July
2008]
ON Timeliness.[Yes/No] = [Worksheet 2 starting July 2008].Timeliness) 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]
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
[Worksheet
2 starting July 2008].Date
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
[Worksheet
2 starting July 2008].Date;

Let's see the SQL of your query.

The query I've shown cannot possibly exhibit the behaviour you're
describing. You must have both the Date field and the Format statement in
your query, as opposed to only the Format statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a query like that already, the problem I am having is that it is
still
grouping the dates by exact date and not by month, even though that is
what
is shown. That is why I was looking for a way to convert the month name
to
a
text field; that way "October" would just be "October", rather than
"10/2/08"
and "10/31/08" both showing "October"

:

The SQL of the query would be something like:

SELECT Department, Format([Date], "mmmm"),
Sum(Timeliness) As TotalTimeliness,
Sum(Accuracy) As TotalAccuracy,
Sum([Customer Service]) As TotalCustomerService,
Sum([Quality of Product) As TotalQualityOfProduct
FROM MyTable
GROUP BY Department, Format([Date], "mmmm")

To build such a query, you'd create a new query and add the table.
You'd
drag the six fields from the table into the grid. For the second field
(the
Date field), you'd change what's in the box from the name of the field
to
the formula shown. You'd convert the query to a Totals query (if
you're
using Access 2003 or earlier, this can be done by selecting Totals
from
the
View menu: afraid I can't remember how it's done in Access 2007) Doing
that
will add a new row "Total" to the grid. You'd leave the default Group
By
under the first two fields, and change it to Sum under the other four
fields.

Incidentally, you really should rename the Date field. Date is a
reserved
word, and should never be used for your own purposes. For a
comprehensive
list of names to avoid (plus a link to a free utility that will check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Here is what I have and my attempted goal:

Field Names: Department, Date, Timeliness, Accuracy, Customer
Service,
and
Quality of Product

The first two fields are entered directly into a form, the last four
fields
are entered by checking or not checking a box which is then assigned
a
value
of yes=1 and no=0.

I need to create a report that shows the sum of each of the Issue
fields
(Timeliness, Accuracy, Customer Service and Quality of Product) for
each
month and have it separated with only one department per page.

I'm still learning my way through Access and have not been able to
figure
out a way to group by month.

Sam

:

The format function returns a string, even if all its showing is
numeric
data.

What problem are you having grouping?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Query is showing the month names, but I need them as text so
I
can
group
them. Is there a way to convert them to text?

:

The month should not be stored in the table: it's redundant.

Create a query, and add a computed field that uses the Format
function
as
Sean showed. Use the query wherever you would otherwise have
used
the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That doesn't seem to be working, and I need the month name to
be
text.

Sam

:

Format([YourDateField], "mmmm")
--
_________

Sean Bailey


:

Is there a way to use a date field to put the month name as
text
in
a
separate column in the same table?

 

[SamBell]

Now that my Query is working I have two follow up questions:

1) I have created a report using the information from the query, how do I
get the months to appear in month order rather than alphabetically?

2) Is there a way to set up a date range so that when I run this query next
year it will only show results for 2009?

 

[Douglas J. Steele]

What's the current SQL of your query?

 

[SamBell]

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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],"mmmm");

 

[Douglas J. Steele]

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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 [Worksheet 2 starting July 2008].[Date] BETWEEN DateSerial(Year(Date),
1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date])

Note that I've added a WHERE clause (which limits the output to the current
year) and an ORDER BY clause (which sorts the months by month number, not
month name)

I see you've chosen to ignore my advice about renaming your field from Date.
I really believe you're asking for trouble using Date as a field name!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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],"mmmm");

What's the current SQL of your query?

 

[SamBell]

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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],"mmmm");

 

[SamBell]

Ignore my last post, my computer glitched on me. Thanks for your help.

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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 [Worksheet 2 starting July 2008].[Date] BETWEEN DateSerial(Year(Date),
1, 1)
AND DateSerial(Year(Date), 12, 31)
GROUP BY [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"mmmm")
ORDER BY [Worksheet 2 starting July 2008].[Department of Origin],
Month([Date])

Note that I've added a WHERE clause (which limits the output to the current
year) and an ORDER BY clause (which sorts the months by month number, not
month name)

I see you've chosen to ignore my advice about renaming your field from Date.
I really believe you're asking for trouble using Date as a field name!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

SELECT [Worksheet 2 starting July 2008].[Department of Origin],
Format([Date],"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],"mmmm");

What's the current SQL of your query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Now that my Query is working I have two follow up questions:

1) I have created a report using the information from the query, how do
I
get the months to appear in month order rather than alphabetically?

2) Is there a way to set up a date range so that when I run this query
next
year it will only show results for 2009?