I'm trying to make it count my "ComplianceIssue" field. But it seems to be only "Adding" across rows.

If i had a similar formula working in a report, could i transfer it over to a query? If so where would I put it? Or is the syntax completely different?



I feel like I'm just missin the whole picture on queries, could someone possibly post a semi-complicated query formula? And where to plug it in as well.



-Bryan

You can use a Totals query and use Count in the Sort field of the query. I'm not sure what it is you are trying to do and where you want to use the count of the number of records... A bit more information on what you are trying to do will help someone here to help you....

Jack

Heres what I'm trying to do...I'm trying to get this pivot table working. But the problem is ComplianceIssue is a checkbox which is Yes/No. In a report I can count or add them just fine and do what I want with them. So I figure if i have it create a new table in the query with the numbers then I can use that as a data table for my pivot table!

Hows that sound?


-Bryan

Now I don't know zip about crosstab queries or pivot tables, but cant you get a sum in a crosstab query? You can get a count of Yes/No fields in a query by using a Totals query and using True for the criteria. You can use a DCount() function in a query, but I am not sure if this is what you need to do or not.. I am truly fumbling in the dark on this one....

Jack

Right...Can you give me an example of using a totals query? or a dcount? I'm not sure where to put it at in the query.


-Bryan

DCount("*", "NameOfTable", "[NameOfYesNoField] = -1")

In a Totals query:

TotalYes: IIF([NameOfYesNoField],1,0)

Select Sum from the Totals line for this column

hth,
Jack

Ok, the Dcount is working great. the IIF doesn't do a running sum tho. It just does 1 twice. (there is 2 complianceissues)


So...what I need to do is convert my yes/no's over to a 1/0 then transfer it over to the pivottable and let the pivottable do the TotalSums and such for me...

I'll need some luck

-Bryan

Bryan -

Good luck! If you got this far you can make it to the end...

Jack

Ok. I got it working...

IIf([ComplianceIssue]=-1,1,0)

Gives the values 0 and 1 AND then the Pivot table sums them up.

I'm having problems with my queries tho...If i use all my tables then it repeats a bunch of data. Because I can have Multiple VisitIssues per Program.


But I dont know how to pull the program name WITH the VisitIssues w/o getting the Program repeated like 10000 times :(


Know what I'm sayin?

Click on the join line between the two tables (in the query) once and it should turn bold. Then right click on the line and select Join Properties. Select the correct join and that should fix your problem. If you have more than two tables you may need to set the join properties different for each join...

hth,
Jack