Adding a count into a report

[viperstingray]

I am trying to create a report but it has fields that are not in my tables. Some of them are math (% this of that, etc.) so those are not a prblem...i don't think so anyways.

My problem comes from certain fields where I need to 'count' how many 'yes' and 'no' were selected from my form. I have a form where the user selects if a job was selected using a combo box containing "yes" or "NO". I need to count how many 'yes's were selected for a specific facility. I then have to see from those 'yes' selected ones, how many were completed (also using another combo box on the same form). ANY IDEAS ON HOW I CAN GET THE COUNT INTO MY REPORT. I know that its do-able, I just can't figure it out.

Cheers

 

[ThreeCrow]

In your report footer add these controls


Total Yes: formula in the txtbox =Count(IIf([NameOf]="YES",0))
Total No: formula in the txtbox =Count(IIf(NameOf]="No",0))

the NameOf should be your control name where you have "Yes" or "No"

Hope the helps

 

[viperstingray]

THANK YOU, that worked perfectly, Now I need to trouble you for one more question. I also need to count how many entries (jobs) are specified for each client. How would I go about doing that? It should be simpler than the above problem. THANKS

 

[viperstingray]

nevermind, I think I got it. Thanks a lot for your help though.

 

[viperstingray]

At least I thought I got it

Well, The fields actually work but there is a problem. My database does not use any queries. I am now creating a report from my 'Audit Info' table but I need a couple of fields from my 'ECM Details" table. I can't see to be able to link them in the report. Is there any way that this can be fixed without the use of queries...I don't know how to use them. I tried the wizard and I included ALL fiedls from both tables just to be safe and It still gave me the same problems.

THE REPORT ALWAYS PROMPTS THE USER TO ENTER IN THE PARAMETER VALUES.

HELP!!!!

 

[viperstingray]

once again...

NEVERMIND. I GOT IT. I think I might have one more small problem..i'll keep you posted

 

[ThreeCrow]

Queries are you friends

Try this:

Select Query

Select New

The query window will pop up, and a little window box will be right in front of it. You'll see three Tabs "Table"; "Queries"; "Both".
The one that is displayed is "Tables". You do not have any Queries so if you select the "Queries" Tab it will be emty.

Make sure you are looking at the "Tables", you'll see your Table Names listed.
Here's the hard part Pick The Two Tables that you want join for the Report by double clicking are thier name.

Hard Part over.

Now the two Tables you should see in the above pane. From each Table select the fields for the Report by double clicking on the field name.

Almost to the top of the window on the left side you will see a liitle box, place your mouse pointer on it. It should say view. Left click.

There you are Sir, You have create your first query.
Now click the X on the right side of the window (Not The Red One).
You'll be asked if you want to save the query, Yes you do.
Now you'll asked for a name. Call the query "qryYourReportsName"
Go to your report data source change to the name of query you just made.

Hope this Helps

 

[viperstingray]

The Last Question

Thank you very very very much for your help with this issue. you have been AMAZING and fast. I just have one more thing to ask you. I already have a report and I want to add another 2 fields in it. here is the situation:

I have a field "# of measure identified","total number measures selected for approval", "number of measures implemented from selected", "precent of selected measures".

EX: Assume
# of measure identified = 8
total number of meausres selected fo approval = 7
number of measures implemented from selected = 2
percent selected measures = 28.57 (2/7*100)

I NEED TO ADD ANOTHER FIELD TO DO THE FOLLOWING:
1) Total cost of implemented projects (the total cost of the 2 from the above)
2)% cost of implemented (this one is not a problem since its division)

How would I go about doing the first one. I need to be able to get the values from those two measures and add them on the report (NOTE: the actual costs are stored in a table "ECM Details")

I hope you can help...I know it seems like a lot. If you need clarification, post back.

 

[ThreeCrow]

How you calculate your total cost

Where do these other numbers come from, the 8, 7, 2

 

[viperstingray]

not really, Each measure has its own cost. I need to add up all the total values of the "number of measures implemented from selected". This cost of each meausre is inputed on a form "ECM Details". If there are 5 measures, 3 are selected as 'yes' for approval, and only 2 of those 3 are implemented then then I need to total those two.

Total Cost = cost of implemented measure 1 + cost of implemented measure 2

A measure must have been selected for approval first for it to be implemented.

 

[ThreeCrow]

I believe that I have the jest of it.

Taking for granted that you have created the Query "qry[YourReportName]

Open this query in Design View, the query will open showing the pane and the grid. Place your pointer in one the top most grid cells. Press the Tab Key until you come to an emty cell. In that cell type the following formula.

"PercentOfMeasure: (2/7)*100"
Without the q
Save the query

Goto to the Report Open in design view, goto RecordSource open the RecordSource Query, Double click on Field Name PercentOfMeasure, This will bring the new field into the Query, Close and Save the Query.

In the Report put this control where you want it.

View your report

This can be done for most any Calculation Field.

 

[viperstingray]

why am i getting this message whenever I try to run the report or the query.

"you are trying to execute a query that does not include the specified expression 'client prefix' as part of an aggregrate function."

what is wrong?

 

[viperstingray]

is there any way I could do it without the query. I have two of the same report. One is with a query and the other isn't. I could still use the other report. I only need this one field. TOTAL COST OF IMPLEMENTED MEASURES

 

[BADBOYPR28]

I need to add record counts to a form, what should I do

 

[ThreeCrow]

Is it the main Form or a subForm that you want the Count