Help With Total Query

[Evagrius]

Hi all,

I have a query I am having a tough time resolving. I have a dozen IDs. Each ID is a client that owns 5 different types of Assets. Asset A, Asset B, Asset C . . .ect.

I am trying to create a query that would count how many of each asset a client owns. A crosstab query didn't seem to work since I could only count one category. Now I am having the same issue with a Totals Query. How should I go about this? In the Totals Query I grouped by client, and tried to enter a field for each type of asset, and then count that type . . Thank you for any help on this!

 

[boblarson]

Not sure how your table structure is in order to provide guidance. Are the assets listed as rows in a table and not as columns (I hope not)?

 

[Evagrius]

Sorry Bob

The assets are Fields. So I have, for Exmaple, in one field "High Risk Asset", the entry for that field would either be "YES" or "NO". The in antoher field I have "Low Risk Asset" with either a "YES" or "NO" then "Medium Risk" . .etc . . .it doesn't seem I can do this in a query though . . .

 

[boblarson]

It means that your structure is not properly normalized and as such it will require a bit more of a workaround to get what you are wanting.

 

[Evagrius]

I Understand Bob - I sadly inherited the table. I created a form (for a work around) and placed the formula below as the control source - but that gives an error. Can you spot my error?

=DCount([ID],"AssetQry",[HIGH]="YES")

 

[boblarson]

Something like this:

=DCount("*","AssetQry","[HIGH] = True AND [ID]= " & [ID] )

 

[Evagrius]

Thank You Bob! I am going to spend some time taking that apart and learning from it. Thanks!

 

[Brianwarnock]

Maybe I'm not understanding correctly but isn't this a simple Sum(IIF query

Ie Group By ID sum for

Countasseta: IIF(asseta="YES",1,0)
etc

Brian

 

[boblarson]

Maybe I'm not understanding correctly but isn't this a simple Sum(IIF query

Ie Group By ID sum for

Countasseta: IIF(asseta="YES",1,0)
etc

Brian

Yeah, now that you mention it, I believe so.

 

[Brianwarnock]

LOL

I love your images Bob, you seem to have one for each occasion.

Brian

 

[Evagrius]

Hi Brianwarnock - I am not in the office today but I will try it first thing on Monday - I left the project unfinished yesterday. So, just to be clear, I use the IFF statment for each field I wish to sum,

IIF([High] = "YES",1,0), 
IIF([Med] = "YES",1,0)
etc . .

then use the sum rather than the count option for totals? Thanks to both of you for the help!!

 

[boblarson]

Yes, but you put each IIF as a DIFFERENT FIELD to capture each of the types.

 

[Brianwarnock]

There is an example by Bob in This Thread

Note that he uses Sum(IIF(

then expression in the Totals row

both approaches are valid

Brian