Counting several fileds in table 2

[easyit]

Hi,

I tried to find a solution in the forums, but couldn't apply it to my problem. Maybe someone can point me in the right direction? I believe I have to do something with subqueries, but I'm missing general knowledge.

I have a query which returns the following fields:

installation (double, =prim.key)
contract (double)
list1 (boolean)
list2 (boolean)
list3 (boolean)
list4 (boolean)

example:

group installation contract list1 list2 list3 list4
NET 6000082793 3000234655 Ja Ja Nee Nee

LEV 6000082805 3000234655 Ja Nee Nee Nee

LEV 6000090818 3000262337 Nee Nee Nee Nee

NET 6000094998 3000238506 Nee Ja Nee Nee

LEV 6000095011 3000238506 Nee Ja Nee Nee

LEV 6000095024 3000238506 Nee Ja Nee Nee

NET 6000096615 3000238097 Nee Nee Ja Nee

I need to count for each group the unique contracts, the number of installations with each unique contract, and the number of times a contract is listed on each of the four lists. In this example the result should be:

Group contracts installations list1 list2 list3 list4
LEV 3 4 1 2 0 0
NET 3 3 1 1 1 0

Do I actually have to nest a query for each count (seems complex)?

Any help is (as always) appreciated.

easyit

 

[r937]

since access doesn't do COUNT DISTINCT, you need to use a nested query

create this and save it as grp_contracts--

select [group]
     , contract
     , count(*) as inst
     , -sum(list1) as countl
     , -sum(list2) as count2
     , -sum(list3) as count3
     , -sum(list4) as count4
  from yourtable
group
    by [group]  
     , contract

then you can produce your results like this --

select [group]
     , count(*) as contracts
     , sum(inst) as installations)
     , sum(count1) as listl
     , sum(count2) as list2
     , sum(count3) as list3
     , sum(count4) as list4
  from grp_contracts
group
    by [group]

r937.com | rudy.ca

 

[lespaul]

easyit have a star for a very clear, precise posting. Thank you!! And it's amazing how quickly you get a query that does EXACTLY what you want when you provide just that simple bit of information, here's what I've got, this is what I want it to be. Getting just basic details from some posters is like pulling teeth!

Leslie


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[easyit]

Wow!

A star for having a problem!!

This is great, my problem is solved also. I should pass the star to r937 as well.

thanx!

easyit