Help! I have 2 tables in MS Access

[072463]

Help!
I have 2 tables in MS Access database, if I want to do a calculation of total hrs in table1
for each user with different Account type as "Client, Util....etc and update to second table 2 with the table setting as follow

Table 1: with 5 columns as follow
User Client Project Account Hours
Jennifer DSM DSM Client 8
Jennifer DSM DSM Client 8
Jennifer DSM DSM Client 5
Jennifer EU Utilized Engineering Util 4
Jennifer DSM Pre-Sales Client 8
Sinha US Ase Mentoring NonUtil 2
Sinha AMAT Pre-Sales Client 2
Sinha US Employee NonUtil 3
Sinha US Ase Metho None 1
Sinha AMAT Pre-Sales Client 2

Table 2:
User Ttl_hrsClient Ttl_hrsUtil Ttl_hrsNonUtil
Jennifer 29 4



I have a query:
INSERT INTO total_hrs ( [User], Ttl_Client )
SELECT [UserTime].[User], Sum([UserTime].[Hours]) AS Ttl_Client
FROM UserTime
WHERE ((([UserTime].[Account])="Client&quot)
GROUP BY [UserTime].[User];


it only update one column in table 2, how I can inclued all other different column w/ different Account type.

Appreciate w/ any input

Michael

 

[johpje]

Why don't you use the same method for the other 2 hour totals?

INSERT INTO total_hrs (User, TtlHrs_Util)
SELECT User, Sum(Hours) AS Ttl_Util
FROM UserTime
WHERE Account = "Util"
GROUP BY User;

Greetz,
Johpje

 

[072463]

Hi Johpje,
First of all thank you of the input.
The query above can updated one accout type each time, but it add data on the top of the old data (see e.g 1 below), what I need is to generate a table with total hrs in different Account type in the same row under the same user name. In other word to combine all result to under user (see example 2).


example 1:
User Ttl_Client Ttl_Util Ttl_NonUtil Ttl_NonAsera
Litsinger Jennifer 29
Litsinger Jennifer 4
Shubhra Sinha 6
Shubhra Sinha 12
Shubhra Sinha 18



example 2
User Ttl_Client Ttl_Util Ttl_NonUtil Ttl_NonAsera
Litsinger Jennifer 29 4
Shubhra Sinha 6 12 18


Apreciate any input

Michael