group by month

[Steve Y]

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

 

[Pat Hartman \(MVP\)]

You can use fields in calculations that are not actually displayed in the
result set. So, do your calculations with the unformatted dates (please
tell me you are using a standard date/time field rather than a text field)
and format the date field to month/year for summarization. NEVER format
your dates when using them for calculations. Format them in controls on
forms/reports. Formatting a date/time field turns it into a string and a
string is just a series of letters/numbers that have no intrinsic meaning.
Dates are stored internally as double precision numbers and Access knows how
to work with fields you define as "date/time".

 

[Todos Menos [MSFT]]

Access doesn't support group by

move to SQL Server

 

[John W. Vinson]

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]

 

[Steve Y]

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]

 

[Michael Gramelspacher]

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]

Maybe like this using Northwind:

SELECT
FORMAT(DATEADD("m",DATEDIFF("m",1,ShippedDate),1),"mmyyyy") AS [Month
Year],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) AS [Total Days Difference],
COUNT(Orders.ShippedDate) AS [Monthly Total],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) / COUNT(ShippedDate) AS [Average
Days To Ship]
FROM Orders
WHERE (((Orders.ShippedDate) IS NOT NULL))
GROUP BY DATEADD("m",DATEDIFF("m",1,ShippedDate),1);

 

[Steve Y]

I am sorry, but I do not understand this type of programming very well. Is
this visual basic or SWL? Where am I suppose to enter this? Sorry to be a
pain.

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]

Maybe like this using Northwind:

SELECT
FORMAT(DATEADD("m",DATEDIFF("m",1,ShippedDate),1),"mmyyyy") AS [Month
Year],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) AS [Total Days Difference],
COUNT(Orders.ShippedDate) AS [Monthly Total],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) / COUNT(ShippedDate) AS [Average
Days To Ship]
FROM Orders
WHERE (((Orders.ShippedDate) IS NOT NULL))
GROUP BY DATEADD("m",DATEDIFF("m",1,ShippedDate),1);

 

[Michael Gramelspacher]

I am sorry, but I do not understand this type of programming very well. Is
this visual basic or SWL? Where am I suppose to enter this? Sorry to be a
pain.

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

:

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]

Maybe like this using Northwind:

SELECT
FORMAT(DATEADD("m",DATEDIFF("m",1,ShippedDate),1),"mmyyyy") AS [Month
Year],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) AS [Total Days Difference],
COUNT(Orders.ShippedDate) AS [Monthly Total],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) / COUNT(ShippedDate) AS [Average
Days To Ship]
FROM Orders

That is query using SQL.

Northwind is a sample database that ships with Access. On my computer it is
in this folder: C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

Open Northwind.mdb and in the database window select Queries. Then select
New Query and use Design View. Close the Show Table window without
selecting any tables. On the left end of the toolbar select SQL. Now copy
my SQL into the window. If there are no problems with line wrapping, you
should be able to run the query.

This query is only for demonstration and to give you something similar to
go by to solve your problem.

 

[Steve Y]

Thank you for your time. It worked.

I am sorry, but I do not understand this type of programming very well. Is
this visual basic or SWL? Where am I suppose to enter this? Sorry to be a
pain.

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

:

I want to run a query that compares ship dates to due dates in order to track
performance. I have approximately 250 ship dates per month. Many different
days. I want to query to do the following: Calculate an average number of
days early or late for each month. So I will need the query to analyce each
individual pair of ship and due dates but give a total for a month. My
problem is that the dates need to be in the "mmddyyyy" format for the
calculation, but need to be in the "mmyyyy" format for the average for the
month. Please help.

The format of a date field is completely irrelevant to any calculations. THe
format only affects the *display*, not what's stored! A date value is stored
internally as a Double number, a count of days and fractions of a day since
midnight, December 30, 1899. You can use the actual date field as a search
term, and *also* have a calculated field or two such fields for the grouping.
I'd actually recommend using two fields, Year([datefield]) and
Month([datefield]) and group by them; or alternatively, a text field
Format([datefield], "yyyymm") so that it will sort chronologically.

But I have to ask: do you want to display the individual day's values? or the
monthly average? or both?

John W. Vinson [MVP]


Maybe like this using Northwind:

SELECT
FORMAT(DATEADD("m",DATEDIFF("m",1,ShippedDate),1),"mmyyyy") AS [Month
Year],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) AS [Total Days Difference],
COUNT(Orders.ShippedDate) AS [Monthly Total],
SUM(DATEDIFF("d",OrderDate,ShippedDate)) / COUNT(ShippedDate) AS [Average
Days To Ship]
FROM Orders

That is query using SQL.

Northwind is a sample database that ships with Access. On my computer it is
in this folder: C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

Open Northwind.mdb and in the database window select Queries. Then select
New Query and use Design View. Close the Show Table window without
selecting any tables. On the left end of the toolbar select SQL. Now copy
my SQL into the window. If there are no problems with line wrapping, you
should be able to run the query.

This query is only for demonstration and to give you something similar to
go by to solve your problem.

 

[John W. Vinson]

I appreciate your input on this and really I am only concerned with seeing
the monthly average.

Create a Query based on the table. Put in two calculated fields by typing

ShipYear: Year([shipdate])

in one vacant Field cell, and

ShipMonth: Month([shipdate])

in the next one over.

Include whatever field you want to average, and whatever other fields you want
to group by (customers maybe? I don't know your data).

Make it a Totals query and group by ShipYear and ShipMonth (and anything else
that you want to group by), and Average whatever it is that you want to
average.

John W. Vinson [MVP]