Access query sum buy column

  • Thread starter Thread starter benjamin.fisk
  • Start date Start date
B

benjamin.fisk

ok here is what i am trying to do i am basicaly trying to create a
summary report in a qurry based on a table

Serial # Name(last) Name(first) Hours ID
AB000000 Doe john 5 1
AB000000 Doe john 1 2
AB000000 Doe john 2 3
AB000000 Doe john 8 4
AB000001 smith jane 7 5
AB000001 smith jane 1 6
AB000001 smith jane 2 7
AB000003 home jack 6 8
AB000003 home jack 9 9
AB000003 home jack 2 10
thats what it looks like right now in the qurrey

what i want is
Serial # Name(last) Name(first) Hours
AB000000 Doe john 16
AB000001 smith jane 10
AB000003 home jack 17


so that when some one has more then 40 hours worked i can sort them
out of the qurrey
i have been trying to play with sum and dsum but i always get a error
 
ok here is what i am trying to do i am basicaly trying to create a
summary report in a qurry based on a table

Serial #          Name(last)  Name(first)  Hours   ID
AB000000      Doe            john               5       1
AB000000      Doe            john               1       2
AB000000      Doe            john               2       3
AB000000      Doe            john               8       4
AB000001      smith          jane              7       5
AB000001      smith          jane              1       6
AB000001      smith          jane              2       7
AB000003      home          jack               6       8
AB000003      home          jack               9       9
AB000003      home          jack               2       10
thats what it looks like right now in the qurrey

what i want is
Serial #          Name(last)  Name(first)  Hours
AB000000      Doe            john               16
AB000001      smith          jane              10
AB000003      home          jack               17

so that when some one has more then 40 hours worked i can sort them
out of the qurrey
i have been trying to play with sum and dsum but i always get a error
Click to expand...

use a totals query.

SELECT [Serial #], [LastName], [FirstName], Sum(Hours) As TotalHours
FROM MyTable
GROUP BY [Serial #], [LastName], [FirstName];
 
ok here is what i am trying to do i am basicaly trying to create a
summary report in a qurry based on a table
Click to expand...
Serial #          Name(last)  Name(first)  Hours   ID
AB000000      Doe            john              5       1
AB000000      Doe            john              1       2
AB000000      Doe            john              2       3
AB000000      Doe            john              8       4
AB000001      smith          jane               7       5
AB000001      smith          jane               1       6
AB000001      smith          jane               2       7
AB000003      home          jack               6       8
AB000003      home          jack               9       9
AB000003      home          jack               2       10
thats what it looks like right now in the qurrey
Click to expand...
what i want is
Serial #          Name(last)  Name(first)  Hours
AB000000      Doe            john              16
AB000001      smith          jane               10
AB000003      home          jack               17
Click to expand...
so that when some one has more then 40 hours worked i can sort them
out of the qurrey
i have been trying to play with sum and dsum but i always get a error
Click to expand...

use a totals query.

SELECT [Serial #], [LastName], [FirstName], Sum(Hours) As TotalHours
FROM MyTable
GROUP BY [Serial #], [LastName], [FirstName];- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you it worked wonders
 
Back
Top