two different conditional sums

[Guest]

I have a table of projects:
- Project_ID
- Name
- Approval_Date
- (etc.)

.... and a linked table of diary-type work entries completed on these projects:
- WorkEntry_ID
- Project_ID
- WorkDate
- Hours_Spent
- (etc.)

I'm hoping to find out three things in a single query:
1. How many total hours spent on the project?
2. How many total hours spent before the approval date?, and
3. How many total hours spent on or after the approval date?

Can this all happen in one query? Any example SQL statements out there?!?!

 

[[MVP] S.Clark]

You could make a pretty groovy report that does all this at once, but not a
single query.

 

[Steve Schapel]

Access_Newbie,

This should do the trick...

SELECT Projects.Project_ID,
Sum(-[HoursSpent]*([WorkDate]<[Approval_Date])) AS WorkBefore,
Sum(-[HoursSpent]*([WorkDate]>=[Approval_Date])) AS WorkAfter, Sum([Work
Entries].HoursSpent) AS WorkTotal
FROM Projects INNER JOIN [Work Entries] ON Projects.Project_ID = [Work
Entries].Project_ID
GROUP BY Projects.Project_ID

 

[John Spencer (MVP)]

Perhaps something like the following will work (substitute your table names and fieldnames).
SELECT P.Project_ID,
SUM(Hours_Spent) as TotalHours,
SUM(IIF(WorkDate<Approval_Date,Hours_Spent,0) as BeforeHours,
SUM(IIF(WorkDate>=Approval_Date,Hours_Spent,0) as AfterHours
FROM TableProjects as P INNER JOIN WorkEntriesTable as W

 

[Guest]

Brilliant, Steve. Thank you - that did the trick.

I think I even understand the solution. The "true" result of the comparison
returns a negative 1, and a "false" returns a zero?

Access_Newbie,

This should do the trick...

SELECT Projects.Project_ID,
Sum(-[HoursSpent]*([WorkDate]<[Approval_Date])) AS WorkBefore,
Sum(-[HoursSpent]*([WorkDate]>=[Approval_Date])) AS WorkAfter, Sum([Work
Entries].HoursSpent) AS WorkTotal
FROM Projects INNER JOIN [Work Entries] ON Projects.Project_ID = [Work
Entries].Project_ID
GROUP BY Projects.Project_ID

--
Steve Schapel, Microsoft Access MVP

I have a table of projects:
- Project_ID
- Name
- Approval_Date
- (etc.)

... and a linked table of diary-type work entries completed on these projects:
- WorkEntry_ID
- Project_ID
- WorkDate
- Hours_Spent
- (etc.)

I'm hoping to find out three things in a single query:
1. How many total hours spent on the project?
2. How many total hours spent before the approval date?, and
3. How many total hours spent on or after the approval date?

Can this all happen in one query? Any example SQL statements out there?!?!

 

[New_Newb (Charlie Burgess)]

I have a similar situation, with a bit of variation-

I have a table of artifacts, with fields:
Site_Number (where the artifact was found)
Code (Artifact Type)
Amount

Some sites have the same Code (with an unrelated amount) more than once

I'm hoping to ask Access to create a table with a field for Site Number and
fields for each Code, and enter Amount totals for each Artifact type in it's
Site Number.

Alternatively, I can (and have) manually created the structure of such a
table (with one field for site number and a field for each code), and would
like to ask Access to provide the numbers.

Can this be done with a query? The tricky part is that amounts must be
summed for each code with each site number. Since I have some 250 sites and
150 codes, and 10,000+ artifacts, it's not realistic for me to simply sum
each Code's Amounts for each Site one at a time.