Corsstab Query Question - Multiple Sums

  • Thread starter Thread starter Steve Lilley
  • Start date Start date
S

Steve Lilley

Hello

Any help much apprecited with the following...

I have a table that records time and expenses booked to jobs

Date Name Job Hours Expenses
1-1 Steve A 5 3.50
2-1 Bob B 4 0.00
3-1 Jim A 3 2.50
4-1 Steve A 2 1.00
....


Then I have a query that when I run it asks for a start date and end date so
I can set them to the current reporting period. So for example If I enter
the start date of 1-1 and end date of 4-1 it would look just like the
above.

Then I want to view this is a nice easy format a bit like this.

Name Hours Expenses
Steve 7 4.5
Bob 4 0.00
Jim 3 2.5

I have tried creating a cross tab query, and it works a treat for counting
just the hours or just the expenses but it there a way to get both in a list
like the above. I've a funny feeling the answer may not be a crosstab query
after all, but any help much appreciated to point me in the right direction.

Regards
Steve
 
Dear Steve:

I don't see where your needs involve using a crosstab.

What would be needed is a simple totals query:

SELECT Name, SUM(Hours) AS Hours, SUM(Expenses) AS Expenses
FROM YourTable
GROUP BY Name

A crosstab might be useful if you wanted either Hours or Expenses by
Job, with the Job in columns.

Hello

Any help much apprecited with the following...

I have a table that records time and expenses booked to jobs

Date Name Job Hours Expenses
1-1 Steve A 5 3.50
2-1 Bob B 4 0.00
3-1 Jim A 3 2.50
4-1 Steve A 2 1.00
...


Then I have a query that when I run it asks for a start date and end date so
I can set them to the current reporting period. So for example If I enter
the start date of 1-1 and end date of 4-1 it would look just like the
above.

Then I want to view this is a nice easy format a bit like this.

Name Hours Expenses
Steve 7 4.5
Bob 4 0.00
Jim 3 2.5

I have tried creating a cross tab query, and it works a treat for counting
just the hours or just the expenses but it there a way to get both in a list
like the above. I've a funny feeling the answer may not be a crosstab query
after all, but any help much appreciated to point me in the right direction.

Regards
Steve
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Create 1 crosstab for each then you will have 2 crosstab
queries with a name field. Then make a 3rd regular query
with the 2 crosstabs connected by name. As long as the
field names don't change that you use this should work.

Martin
 
A Totals Query should give you the Sums. Something like (***untested***):

SELECT [Name], Sum([Hours]) As SumOfHours,
Sum([Expenses]) As SumOfExpenses
FROM YourTable
WHERE [Date] Between [StartDate] And [EndDate]
GROUP BY [Name]

BTW, if your Field names are actually "Name" and "Date", they are bad
choices (since Date is an in-built function name and Name is an in-built
Property for every object in Access) and I would suggest re-naming these
Fields before you develop your database further.
 
Many thnaks for all your help.

I'm afraid my fields were "Name" and "Date" and never veen thought about
what a poor choice they were. :-) Thanks.

Regards
Steve

Van T. Dinh said:
A Totals Query should give you the Sums. Something like (***untested***):

SELECT [Name], Sum([Hours]) As SumOfHours,
Sum([Expenses]) As SumOfExpenses
FROM YourTable
WHERE [Date] Between [StartDate] And [EndDate]
GROUP BY [Name]

BTW, if your Field names are actually "Name" and "Date", they are bad
choices (since Date is an in-built function name and Name is an in-built
Property for every object in Access) and I would suggest re-naming these
Fields before you develop your database further.

--
HTH
Van T. Dinh
MVP (Access)




Steve Lilley said:
Hello

Any help much apprecited with the following...

I have a table that records time and expenses booked to jobs

Date Name Job Hours Expenses
1-1 Steve A 5 3.50
2-1 Bob B 4 0.00
3-1 Jim A 3 2.50
4-1 Steve A 2 1.00
...


Then I have a query that when I run it asks for a start date and end
Click to expand...
date
so
I can set them to the current reporting period. So for example If I enter
the start date of 1-1 and end date of 4-1 it would look just like the
above.

Then I want to view this is a nice easy format a bit like this.

Name Hours Expenses
Steve 7 4.5
Bob 4 0.00
Jim 3 2.5

I have tried creating a cross tab query, and it works a treat for counting
just the hours or just the expenses but it there a way to get both in a list
like the above. I've a funny feeling the answer may not be a crosstab query
after all, but any help much appreciated to point me in the right direction.

Regards
Steve
Click to expand...
Click to expand...
 
Back
Top