Two queries on a report? Dynamic field-adding?

[grande]

So, I have a report with a field that looks like this:

AM, TS, TC
TS, TC
EM
AM, CL, CM
CI, EM, GM
TS, TC
EM
TS, TC

(They're abbriviations for antibiotics)

I would like my Report Footer to say:

AM, TS, TC    1
TS, TC        2
EM            2
AM, CL, CM    1
CI, EM, GM    1

There is no guaruntee that there will be EM. Likewise, there is no guaruntee that there won't be EM. It can be any combination of 10 different antibiotics. I have a query, qryAMRCount, that has the SQL:

SELECT AMRProfile, Count(AMRProfile) As Counter
FROM CampyTbl_Grid_Results
GROUP BY AMRProfile

and that gives me what I need. Now how do put that information (from a different query) onto my report?

-------------------------
Just call me Captain Awesome.

 

[GingerR]

Hi Captain Awesome.

How about making a subreport and embedding it into the report?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Okay, that works great, but one more question.

Now I'm being asked for the "Between" info 3 times when the report opens, and two more times when I want to go to the last page (Where the subreport is).

The subreport uses the parent report as its Record Source. The parent report has
"Between [Enter starting Sample ID - for example 0001] And [Enter ending Sample ID - for example 0100]"
as its criteria in one of the fields. How do I prevent it from asking my 5 times?

-------------------------
Just call me Captain Awesome.

 

[grande]

I forgot to mention, when I run just the parent or just the child, it only asks me once.

-------------------------
Just call me Captain Awesome.

 

[dhookom]

You should abandon your parameter prompts in favor of using criteria from controls on forms. If you have questions, check this page

http://www.fontstuff.com/access/acctut08.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GingerR]

I agree with Duane, Captain Awesome. It's much more professional as well as easier for the user!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grande]

Yeah, I've decided to go that route. It does seem a lot better.

The reason Parameters were used was because when this DB was made (about 7 years ago, in Access 97) space was a concern (The three linked DBs I'm working with total almost 3GB).

-------------------------
Just call me Captain Awesome.