Use the Total in my Queries to populate my Report

[Imintrouble]

Hello.

The report I'm creating will be getting its information from two sepearate queries, bot having information whittled down by a set of criteria. I have, so far, created a set of totals which are at the bottom of the query, one which counts how many data entries there are, the other, how many of a specific field there are. I need to take these totals and use them in my report.

The main problem I've had so far with attempting something like this was that I couldn't get a total to appear, without having to put every little bit of information into the report, which is then counted in the report itself to get how many there are. I just want it to take the total's from the queries and say,

"There are ##### files under this.

Of these files, ### have been answered.

then under this last line, list the different asnwers (there are only a maximum of 6 possible answers however only the ones which are actually present in the query should be listed.)

Lastly, if I'm going about this in the wrong way, please also say so, while providing the alternative, if any.

Thanks!

P.S. I'm using Windows 7 with MS Access 2010

[NeoPa]

Queries don't subtotal for you. If you want totals in a query then you'll need to design a totals query. Usually, that totals at a particular level, so if you want multiple levels of totals you'd need two separate queries. Multiple queries can be linked of course, but I suspect what you really need is the grouping facilities of a report, which was designed to handle such situations.

I can offer little other advice at this time as, though your question is well laid out, it lack most of the details that would be necessary to give any idea of what you're working with.

[Imintrouble]

Well what I ment by the total thing, was where you click the Totals button in the Home tab, creating a total's row at the bottom of the screen.

As for the groupings, all I really need is a way of having my report tell me how many files there are, without listing off information, and then just providing multiple subtotals from the two queries. If I am missing any details, please let me know what I should be providing you with to help me solve my conundrum.

[NeoPa]

ImInTrouble:
If I am missing any details, please let me know what I should be providing you with to help me solve my conundrum.

If you want a detailed answer then you have to provide details. If you provided details but missed some important ones out, then I could explain what was missing. Even now all details are missing, so you need to look and see what should be in the question. Partly because it's your original responsibility anyway, and partly because you're the only person who can at this stage.

I already explained the situation to you broadly. If you want to progress from there then feel free to respond to what I've said. Asking for more, without working within that context, doesn't indicate that you're engaging fully.

From what I know yet, you need to be looking at using the aggregation (grouping/totalling) features of the report rather than trying to do that in the query, as the latter has many limitations in that area. Basically it handles either details or grouping, but not both.

[Imintrouble]

Hi NeoPa,

Sorry about that earlier question and how baddly worded it was.

Well after going over my database I decided to take another crack at the report problem I had been having. Upon looking at it, I got an epiphany. Because the information I wanted displayed only needed to be shown to me ONCE I started looking for aspects of the report format that only show ONCE. My answer, albeit mildly redundant and probably not the best option, was this:

I know that I didn't want to list off any information, just count the information. So instead of pursuing the total from query idea, I decided to count the information present in my report. Then, after assigning names and creating the count functions I would need. I collapsed the entire details portion of the report until only the report Footer was visible. From there I simply made text boxes which stated what I wanted, along with the different counter variables provided by the details area. This worked just fine.

HOWEVER!!

When I tried to do this on the same report with the other query, it was no longer working or giving me an error which I don't remember what the error was called. So I guessed that this new problem was more than likely because the two queries are from the same originating table, meaning the information really would be redundant.

Thanks for the help so far, and if any help can be provided on this, I would surely appreciate it.

[NeoPa]

Starting from a premise that you have a report with two separate Record Sources is difficult for me, as I understand that this cannot possibly make sense. However, that seems to be your implication, which leaves me struggling to find a way to answer helpfully.

Up to where you say HOWEVER, everything makes sense. The solution you found is one that can work. There may be others, but I'd need a better understanding of your requirements to be sure of that. You've certauinly found a workable solution though, and one which leads towards a better understanding of the capabilities of reports and what thay can do. All good so far.

When you refer to another query on the same report however, I'm thoroughly lost.

[Imintrouble]

Thanks, I just happy I'm finally getting somewhere.

after the HOWEVER, what I ment was this:

I have one report named Letter Report. I also have two queries, Potential and Eligible. These two queries are made with data found only in one table, Audit Results. The only difference between these two queries is their criteria which makes an entry Eligible or Potential. After creating the right text boxes I needed which displayed the information from Eligible, as this was the query which I used in originally making this report, I tried to go into the data of the report and add the two fields from Potential query that I needed to output the information. This wouldn't work at all i'm guessing because of the fact that the two queries I want to use to make the report are from the same Table, Audit Results.

[NeoPa]

Interesting.

If both queries return the same data in the same way but simply have differing criteria, then switching the Record Source value of the report and continuing to work on it should work fine. Especially, though not limited to, when they both return data from the same table.

That said, of course, there really is no need to have two separate queries at all if the only difference is in the criteria. A report can include criteria both in its design, and from its invocation. The more usual approach is to have one report, but call it in different ways depending on the data required. You may even get away with not requiring a query for it at all if the data is just as it comes out of the table.

[Imintrouble]

You, my dear sir or madam, are a genius. One thing though, how exactly would I set criteria from this table into the report?

[NeoPa]

The 'Pa' part is quite literal, so I'm definitely more of a Sir than a Madam, and thank you.

There are two main ways of setting criteria in the report. For you I would expect #2 is more appropriate :

1. Set the Filter property of the report.
2. When calling the report to open (DoCmd.OpenRepor t()), use the WhereCondition parameter.

NB. Beware of saving the report design after having opened it with criteria. It typically saves the criteria in the design of the report itself, which isn't too clever, and can confuse people. If you want to save it from there then simply try to remember to reset the Filter property first. Otherwise, remember it's a possibility, so when you notice it filtering automatically without being asked to, go and resave it properly.