how to combine multiple records into one 1

[DougP]

I have a Timesheet program. It lists days Saturday - Monday or Sa - M.
A person can key in Notes about what they did that day.
The query currently shows hours for each day as a separate record. How can I make a query that shows all days on the same record?

SELECT[Employee Name],Notes,Sa,Su,M,T,W,Th,F
FROM tmpnewQB
GROUP BY[Employee Name],Notes,Sa,Su,M,T,W,Th,F
HAVING (((tmpnewQB.[Employee Name])="Doug"));

this is what I have now
Employee Name Notes2 Sa Su M T W Th F
Doug IT 2
Doug IT 2
Doug IT 4
Doug IT 4.5
Doug IT 5.5
this is what I would like it to be
Doug IT 5.5 4.5 4 2 2


DougP
< I Built one

http://www.robots2000.com

 

[dhookom]

I assume this is a temp table for user interface. Have you tried a totals query:

SELECT [Employee Name], Sum(Sa) as Sat,Sum(Su) as Sun, Sum(M) as Mon, Sum(T) as Tue, Sum(W)as Wed, Sum(Th) as Thu, Sum(F) as Fri
FROM tmpnewQB
WHERE [Employee Name]="Doug"
GROUP BY [Employee Name];

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Tell me you don't have a table that looks like this:
[Employee Name],Notes,Sa,Su,M,T,W,Th,F

No,No, a thousand times No.

See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

It violates the very first rule of Normalization in quite a few ways.

 

[DougP]

Thankyou dhookom
fneily, you are right it's not Normalised, I do in most cases make tables normal but there are times when it becomes quicker (get the job done) not to. This is a temp table driving a report. And also there is a lot more I know telling, since I don't have time.


DougP
< I Built one

http://www.robots2000.com