Use queries in reports

[beacon]

Can you use queries in reports? I'm trying to enter a SQL statement in a text box on a report, but if I do it with an equal sign I get a syntax error. If I do it without an equal sign I get an invalid control source.

If this is possible, where am I going wrong?

Here's my SQL statement:

SELECT CountOfSkillLevel FROM qrySkillsWF WHERE SkillLevelID = 1

Thanks for the help

[nico5038]

Can you use queries in reports? I'm trying to enter a SQL statement in a text box on a report, but if I do it with an equal sign I get a syntax error. If I do it without an equal sign I get an invalid control source.

If this is possible, where am I going wrong?

Here's my SQL statement:

SELECT CountOfSkillLevel FROM qrySkillsWF WHERE SkillLevelID = 1

Thanks for the help

To get the data out of a table you'll need the DLookup() function like:

=DLookup("CountOfSkillLevel","qrySkillsWF","SkillLevelID = 1")

Nic;o)

[NeoPa]

You won't need the query though. You can also use DCount() on the underlying table for similar results (assuming of course that the counting is as simple as it would appear).

[beacon]

I tried the DLookup in the report, but it's not returning a value. It's not even returning an error. I typed it in exactly as shown, checked the names of the tables and columns, and it is coming back blank.

Any ideas what I'm doing wrong? Does it make a difference that it's on a report?

[beacon]

Nevermind...I got it to work. Thanks for the help you guys.

[NeoPa]

I'm not clear exactly what you've tried.
How about you post the current SQL of the query the report is built upon, and any relevant controls in the report that are not working as expected.

[NeoPa]

Nevermind...I got it to work. Thanks for the help you guys.

Oh, no worries then ;)
I sometimes have to leave a page idle for (quite) a while before I get around to posting a response.

Do you want to let us know what your solution was. In case anyone finds this thread at a later date and wonders how they should duplicate your success.

[beacon]

Ok...I lied. It partially worked, but I know it's probably because of how I have my report setup.

Let me lay it out from the beginning and see if anyone can't figure out where I'm going wrong.

I have a report that is setup like this:

(Program Header)
Program
    (SkillLevelID Header)
     SkillLevel     SkillLevelID(not visible)
           (Details)
            LName   FName   JobTitle   Email   PhoneExt
(Program Footer)
                                    Total Beginner Employees                TextBox
                                    Total Intermediate Employees           TextBox
                                    Total Advanced Employees               TextBox
                                    Total Program Employees                 TextBox
 

I'm trying to get the total individual skill level for each individual program and have it summarized in the program footer. Then at the end of the report I wanted to put a comprehensive summary.

I created a query to count the data because I couldn't use a count in the program footer to summarize the data in different sections.

When I setup my query I put CountOfSkillLevel and SkillLevel, but that just did totals for the skill level. Then I added program, but my DLookup didn't have anything in it to determine that program was necessary for the lookup.

Here's the SQL for my query:

SELECT qryWFSummary.Program, Count(qryWFSummary.SkillLevel) AS CountOfSkillLevel, tblSkillLevel.SkillLevel
FROM qryWFSummary INNER JOIN tblSkillLevel ON qryWFSummary.SkillLevelID=tblSkillLevel.SkillLevelID
GROUP BY qryWFSummary.Program, tblSkillLevel.SkillLevel;
 

Right now my DLookup looks like this:

=DLookUp("CountOfSkillLevel","qrySkillsWF","SkillLevel = 'Beginner'")
 

Thanks for the help and sorry for thinking I had it figured out.

[beacon]

Can I use an '&' to tell my DLookup that I need to sort the values by the program they describe? Is that possible?

[nico5038]

You should be able to count in a report footer without the additional query by using in the textbox:

=Count([skilllevel])
 

Nic;o)

[beacon]

That doesn't work. I have 4 different skill levels that I'm trying to summarize in the program footer. Your idea would work perfect if I placed each of them in a skill level footer, but that's not what I'm trying to do.

Thanks anyway nico...

Any other ideas??

[nico5038]

Then use:

=Count([skilllevel]='beginner')
 

Nic;o)

[beacon]

That does the same thing as before. It returns all of the skill levels for the entire section. Not just one individually. This is because I'm summarizing this in a different footer section. If I was doing this in the skill level footer section then it would work fine, but my instructions are to place it in the program footer.

I think you were on the right track with the DLookup, I just couldn't get it to separate by program. I was hoping my DLookup could do something like this:

=DLookup("CountOfSkillLevel" & [Program], "qrySkillsWF", "SkillLevel = 'Beginner'")
 

But that didn't work very well...

[nico5038]

Losing me, did you define a skilllevel grouplevel with footer ?
(Press the "[=" looking button for the defined levels)

Nic;o)

[beacon]

Do what [=???

I have a program header and footer and a skill level header, but no skill level footer. I want to summarize everything at the end of the program, not the skill level.

I'm trying to summarize data for the program by showing how many people fall under each skill level.

Check out my example from earlier and see if it doesn't make a little more sense. Excuse the textboxes that continue to the next line.

[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 misinterpretation.
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.

[Rabbit]

Have I missed something or can you not use:

=DLookUp("CountOfSkillLevel","qrySkillsWF","SkillLevel = 'Beginner' AND Program = '" & [Program] & "'")
 

[NeoPa]

If you check out post #20 Rabbit, you'll see why I don't recommend that approach, although you certainly can get a correct result that way.
@Beacon,
I've discovered (was told by Mary actually) that the aggregate functions can actually only work on the underlying fields (from the recordset) rather than on any controls in the report per se.
This means that to produce the results we need you will need to update the design of the recordset to include the extra three fields which each contain a 1 if, and only if, the Skill Level matches. Does this make sense?

[beacon]

Are you saying that I need to include different text boxes in the details section of my report?

[beacon]

I went ahead and used what Rabbit suggested and I'm going to use that. I don't think the users of this db are ever going to use enough data that using the dlookup will compromise anything.

I'm going to call this issue complete.

Thank you Nico, NeoPa, and Rabbit for all of your help...and I'm sorry for any headache this might have caused considering that the report's design was flawed from the get-go.

[NeoPa]

That's certainly one of the important issues Beacon.

What I was suggesting (for posterity now as your way is fine for you) was to add extra fields in the data source (query) where the Beginner field would be set to 1 if the Skill Level was "beginner" and 0 otherwise. This would then be processed naturally within the report itself (as discussed previously).

Anyway, good luck with your project :)

[mlcampeau]

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?

From my experience, you can't Sum an expression that is in a different section in the report. What I've done in the past to overcome this is create the =IIF([SkillLevel]="Beginner", 1, 0) textbox in the details section and name it txtBeginner. Then also in the details section, create another textbox that says =[txtBeginner] and go to the properties of the textbox and change it to Running Sum Over Group. Change Visible=No. Name it appropriately, like sumBeginner. Then in your footer, create a textbox =[sumBeginner].

[beacon]

From my experience, you can't Sum an expression that is in a different section in the report. What I've done in the past to overcome this is create the =IIF([SkillLevel]="Beginner", 1, 0) textbox in the details section and name it txtBeginner. Then also in the details section, create another textbox that says =[txtBeginner] and go to the properties of the textbox and change it to Running Sum Over Group. Change Visible=No. Name it appropriately, like sumBeginner. Then in your footer, create a textbox =[sumBeginner].

I tried that, but kept having the 'Enter Parameter' dialog box appear. For some reason it didn't like the fact that I created a text box in one section, as you suggested, and then try to reference that text box with the brackets around it in another section. Maybe I just completely missed something altogether.

[mlcampeau]

I tried that, but kept having the 'Enter Parameter' dialog box appear. For some reason it didn't like the fact that I created a text box in one section, as you suggested, and then try to reference that text box with the brackets around it in another section. Maybe I just completely missed something altogether.

From what I understood from your previous post was that you tried =sum([txtBeginner]) in your footer. Did you try the running sum invisible box in the details section and then just have a text box that says =[sumBeginner]? Because I know Access won't allow the aggregate functions on a calculated expression in a different section, but it has let me just reference a calculated textbox from a different section.