Queries 1

[jchollo]

Hello,

If I have a query which produced the following:

Org: Status: Count of Status:
Q488 Student 8
Q4883 New Hire 10
Q4884 Student 2
Q4886 New Hire 5

The Org field and Status field are Group By in the total line and used the count for the Count of Status.

How would I do the query in design view to get just one sum that would produce that Q488 has a total of 10 Students and then a total of 15 New Hires instead of counting up the lines above manually.

Also how would I do a query to get just one sum that Q488 has a total of 25 people instead of having each group with a total and then having to add up all the lines. I had removed the Group By but that still did not work.

I hope this is understandable.

tks

 

[TheAceMan1]

How are ya jchollo . . .

Two queries with the same SQL and one of the added [blue]WHERE Clauses below[/blue]:

[blue]WHERE ([Status] = 'Student')
   or
WHERE ([Status] = 'New Hire')[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[VRoscioli]

You seem to want to group different values for Org together. If this is correct, how do you determine which get grouped together?


-V

 

[TheAceMan1]

Correction:

[blue]WHERE ([Org] Like "Q488*") AND ([Status] = 'Student')
   or
WHERE ([Org] Like "Q488*") AND ([Status] = 'New Hire')[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jchollo]

Tks for the replies - I will try those.

 

[jchollo]

Hi VRoscioli

To answer your question, yes I want to group different values for the org.

For ex. Q488 is the head org and they have 8 students and within that org is Q4884 which has 2 students. Instead of producing line by line of each org like I have above, I was looking just to be able to produce one total number for the students no matter what org they were in - and the same for new hire.

tks

 

[PHV]

You wanted this (SQL code snippet) ?
GROUP BY Left([Org],4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jchollo]

ok - tks