Group By Date issue - date format is "general"

[Guest]

Am grouping various types of Work Hours by Date Worked. Problem is when two
records share same date, for example, but not same time, the result set
returns 2 rows instead of one. How do you group by date regardless of time?
Date format has to be "general", as diffdate (minutes) is a necessary
calculation in one of the forms. Thanks for any info. -Chris

INSERT INTO LaborMaint2
SELECT DISTINCTROW LaborMaintTemp.[Clock Number] AS [Clock Number],
LaborMaintTemp.Name AS Name, LaborMaintTemp.[Date Worked] AS [Date Worked],
Sum(LaborMaintTemp.[Breakdown Hrs Worked]) AS [Breakdown Hrs Worked],
Sum(LaborMaintTemp.[PM Hrs Worked]) AS [PM Hrs Worked],
Sum(LaborMaintTemp.[Round Hrs Worked]) AS [Round Hrs Worked],
Sum(LaborMaintTemp.[Project Hrs Worked]) AS [Project Hrs Worked],
Sum(LaborMaintTemp.[Total Hrs Worked]) AS [Total Hrs Worked]
FROM LaborMaintTemp
WHERE (((LaborMaintTemp.[Date Worked]) > [Enter last day of PRIOR month as
mm/dd/yy: ]))
GROUP BY LaborMaintTemp.[Date Worked], LaborMaintTemp.[Clock Number],
LaborMaintTemp.Name
ORDER BY LaborMaintTemp.[Clock Number];

 

[Rick B]

ONE post will do. Please do not continue posting your question over and
over.

 

[Guest]

Did not intend to - kept getting error when posting to "try again later..."

Chris

ONE post will do. Please do not continue posting your question over and
over.

Am grouping various types of Work Hours by Date Worked. Problem is when two
records share same date, for example, but not same time, the result set
returns 2 rows instead of one. How do you group by date regardless of time?
Date format has to be "general", as diffdate (minutes) is a necessary
calculation in one of the forms. Thanks for any info. -Chris

INSERT INTO LaborMaint2
SELECT DISTINCTROW LaborMaintTemp.[Clock Number] AS [Clock Number],
LaborMaintTemp.Name AS Name, LaborMaintTemp.[Date Worked] AS [Date Worked],
Sum(LaborMaintTemp.[Breakdown Hrs Worked]) AS [Breakdown Hrs Worked],
Sum(LaborMaintTemp.[PM Hrs Worked]) AS [PM Hrs Worked],
Sum(LaborMaintTemp.[Round Hrs Worked]) AS [Round Hrs Worked],
Sum(LaborMaintTemp.[Project Hrs Worked]) AS [Project Hrs Worked],
Sum(LaborMaintTemp.[Total Hrs Worked]) AS [Total Hrs Worked]
FROM LaborMaintTemp
WHERE (((LaborMaintTemp.[Date Worked]) > [Enter last day of PRIOR month as
mm/dd/yy: ]))
GROUP BY LaborMaintTemp.[Date Worked], LaborMaintTemp.[Clock Number],
LaborMaintTemp.Name
ORDER BY LaborMaintTemp.[Clock Number];

 

[John Vinson]

Am grouping various types of Work Hours by Date Worked. Problem is when two
records share same date, for example, but not same time, the result set
returns 2 rows instead of one. How do you group by date regardless of time?
Date format has to be "general", as diffdate (minutes) is a necessary
calculation in one of the forms. Thanks for any info. -Chris

The *format* of the date is COMPLETELY IRRELEVANT for the search; the
date - regardless of format - is stored as a double float number, a
count of days and fractions of a day since midnight, December 30,
1899.

To extract just the date portion (the integer part) of the date/time
value you can use DateValue([Date Worked]).

Sorry you got bit by the bogus error message on Microsoft's website.
We MVP's have been screaming at Microsoft about it for a month now,
and they have not been able to fix it. You might want to consider
using Outlook Express or Agent or another "newsreader" program to link
to the news server msnews.microsoft.com; this will get to exactly the
same newsgroups in a less buggy manner.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

Thank you so much for all the info. VERY helpful.

Am grouping various types of Work Hours by Date Worked. Problem is when two
records share same date, for example, but not same time, the result set
returns 2 rows instead of one. How do you group by date regardless of time?
Date format has to be "general", as diffdate (minutes) is a necessary
calculation in one of the forms. Thanks for any info. -Chris

The *format* of the date is COMPLETELY IRRELEVANT for the search; the
date - regardless of format - is stored as a double float number, a
count of days and fractions of a day since midnight, December 30,
1899.

To extract just the date portion (the integer part) of the date/time
value you can use DateValue([Date Worked]).

Sorry you got bit by the bogus error message on Microsoft's website.
We MVP's have been screaming at Microsoft about it for a month now,
and they have not been able to fix it. You might want to consider
using Outlook Express or Agent or another "newsreader" program to link
to the news server msnews.microsoft.com; this will get to exactly the
same newsgroups in a less buggy manner.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps