Use queries in reports

[nico5038]

Hmm, the normal way for reporting os to total in a footer underneath the level the counts.
When you want it in the report footer the given =Count([skill]='beginner') should work, but you can use an alternative by placing a subreport based on your qrySkillsWF as you want all to be visible.

Nic;o)

[beacon]

I don't know how to do that. Now I'm confused...

[nico5038]

Just place a subreport from the toolbar and follow the wizard instuctions.

Nic;o)

[beacon]

I understand that the count for a section goes in that sections footer. My instructions, however, are to set it up with a program header and a skill level header, but have everything summarize in the program footer.

I know that for what I'm doing the count function will not work. That's why I inquired about using queries in the first place and never mentioned the count function.

Everything was very close to working when I used the DLookup, but I think my query may not be sound.

Also, I've never used a subreport before and because of that, I don't think now would be a good time to employ it.

I hope this doesn't come across negatively or stubborn, although I'm sure the latter is probably somewhat apparent, but I was given guidelines to follow and I'm trying to adhere to them.

[NeoPa]

OK Beacon, when Nico used [= he was trying to draw your attention to the "Sorting and Grouping" button on the "Report Design" toolbar. If you look you may notice the similarity. This is so that you can say clearly what groupings you're using.
Let me guess from your post #8 that you have grouped first by Program (including both a header and a footer), and also by SkillLevel (with just a header shown)?

Let me also guess that the Count() doesn't work mainly because you want only the count of records where certain conditions are met (Beginner; Intermediate; Advanced)? The overall total should work for you.
NB. Your Details section (as shown) does not include anything to differentiate between these types.

I will explain why this is a better idea than DLookup().
Reports can be flexible little beasties and can be filtered and manipulated (Not just by the code but also the operator). Getting the DLookup() to reflect any changes is complicated and is essaentially an "unnatural" way to do it. Wherever possible, I always try to accomplish something working with Access rather than to try to get around it. When things change later (They do - trust me), it's generally a lot easier to maintain correctly.
To get around this problem I would create small and hidden items in the Details Section which show (hold) values reflecting what type the line contains. If the value is 1 for true and 0 for false, you can create a control in the Program Footer Section and use Sum() (instead of Count()) to tally the number of items that are of that type.

[beacon]

Thanks Neo, but let me ask you this. If I place a text box in a section, for instance in the Skill Level header, and it counts the number of entries in the details section, which I have done before and which calculated correctly, is there a way to reference that value in another text box in a different footer where I want my data summarized?

[beacon]

Do you think you could give me an example of how I could place a holder in the details section that will be unique for each skill level so I can total it in the footer?

Plus, would these holders have to be reinitialized to zero when they reach the end of a skill level/program?

[NeoPa]

Thanks Neo, but let me ask you this. If I place a text box in a section, for instance in the Skill Level header, and it counts the number of entries in the details section, which I have done before and which calculated correctly, is there a way to reference that value in another text box in a different footer where I want my data summarized?

I think so.
I tend to use aggregate functions (Count(); Sum(); etc) in the Footer rather than the header myself, but you can access a control in the header section (I'm pretty sure) from a control in the Footer section. The other way around might be tricky.

[NeoPa]

Do you think you could give me an example of how I could place a holder in the details section that will be unique for each skill level so I can total it in the footer?

Plus, would these holders have to be reinitialized to zero when they reach the end of a skill level/program?

Everything in the Details section should be unique to the record itself. It must be unique to all the headers it's under too.
The Count() function will only count items within the Footer section that it pertains to anyway. The Program Footer will count all items matching the Program it pertains to. Same for each SkillLevel.
I can't give an example at this time as you still haven't indicated what determines which "box" a record should be put into (See my NB in post #20).

[NeoPa]

Thanks Neo, but let me ask you this. If I place a text box in a section, for instance in the Skill Level header, and it counts the number of entries in the details section, which I have done before and which calculated correctly, is there a way to reference that value in another text box in a different footer where I want my data summarized?

Just realised this is open to misinterpretati on.
What do you mean by a "different" footer? No footer had been introduced up to that point.

[beacon]

Let me see if I'm understanding you/can't clarify...

I have a text box associated with Beginner, Intermediate, and Advanced labels. Of these text boxes, the first one should contain the number of beginners for the first program. The second should contain the number of intermediates for the first program, and so on. I have a total text box too that has '=Count(*)' in it and works just fine.

Each of these text boxes are in the program footer. I have a program header and a skill level header, but no skill level footer. I see why having a skill level footer makes sense, but they completely go against my instructions so I'm having to find another way to get the totals to show up.

I'm sure that trying to do this violates what you were saying about every record for a section needing to be unique, but that's why I was trying to use a query in the text box. I figured that if I could pull the data from a query instead of counting it in the report that I would have a little more flexibility.

Ultimately, I have 5 programs (header/footer) and each will have 3 skill levels (header only) associated with them. Then there will be the details of the employees who fall under these skill levels below that. Because there are 5 programs, there should be 5 summaries, one at the end of each program.

Does this help at all?

[NeoPa]

In as much as it just clicked with me (while reading this) that the Beginner, Intermediate & Advanced items are actually "SkillLevel "s, yes it did.
Shortcuts (DCount etc is an example) are often easier to implement. As they are not the most "natural" way though, they introduce problems for you (especially later on).

What you need to do is go back to post #20 and, at the end, it explains what you need to add in the way of controls to enable the Program header to count the various Skill Levels correctly.

Now I know how that is determined I can give you an example source for one of the controls (txtBeginner) :

=IIf([SkillLevel] = "Beginner", 1, 0)

You need to make sure that the fields and controls are referenced correctly for your db of course.
The Beginner control in the Program Footer would be :

=Sum([txtBeginner])

[beacon]

Ok...that makes sense, but whenever I try it, it asks me for a parameter value for the text box I reference in the Sum() expression.

Just to test out what you advised, I placed a text box in the details section, called txtBeginner, and typed in

 =IIF([SkillLevel] = "Beginner", 1, 0

and then I typed in

 =Sum([txtBeginner])

in a text box I placed in the Program footer.

When I go to Print Preview, it asks me for a parameter for txtBeginner. I also tried placing the text box in the Skill Level header, but the same thing happened.

Am I missing something else? It's like it doesn't recognize the txtBeginner text box, or it doesn't want to pass the value.

Have I made a mistake somewhere with this? Maybe I left out a step?

[NeoPa]

It doesn't seem as if you've either misunderstood OR missed anything out (except maybe the parenthesis at the end of the formula in txtBeginner but that's not an issue).
Let me do some testing. There's still a little time before my coach turns back into a pumpkin tonight.

[NeoPa]

I did some testing and found that I get the same problem. I'm referring it to Mary to help with. She uses that sort of stuff a lot more than I do.

Will get back to you as soon as I am able.