Group By Date issue - date format is "general"

C

chris

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];
 
C

chris

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

Chris

Rick B said:
ONE post will do. Please do not continue posting your question over and
over.



chris said:
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];
Click to expand...
Click to expand...
 
J

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
Click to expand...

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
 
C

chris

John,

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

John Vinson said:
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
Click to expand...

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
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

Group by Date question (date format is "general") 1
Group By Date question - date format needs to be "general" 1
labour report 1
Calculate hours, past midnight etc 4
Time Calculations 1
Another Date Query 2
Returning zero in a query? 3
End of Month: Parameter Query? 3

Top