Accessing Fields From Other Queries In A Report

[daviegravy]

I would like to know if I can access fields from other queries in a report? I'm trying to use the expression builder to do this, but it keeps prompting me for the value as a parameter. My data looks like...

=(Sum([CountOfnotification_only])/[get_counts_clients_total!CountOfclient_name])

Is there an easy way for me to reference fields from other queries/reports without doing a complex query? Does Access have this functionality built in? I appreciate any help at all! Thanks!

 

[Remou]

Have a look at the domain aggregate functions DlookUp, DSum, DCount etc.

 

[dhookom]

Remou is correct (as usual). Looking a little deeper, how would your expression "[red]get_counts_clients_total!CountOfclient_name[/red]" understand which record in get_counts_clients_total to pull the CountOfClient_Name from?

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]

 

[daviegravy]

Only one record is being returned from that query. I'll look into those functions. Thanks for the replies!

 

[daviegravy]

Amazing! That DLookUp was exactly what I needed. Thanks so much guys for the replies!!

 

[dhookom]

If the query only returns one record then I would just add the get_counts_clients_total query to the report's record source. This would allow you to add the field to the report's field list and use:

=Sum([CountOfnotification_only])/[CountOfclient_name]



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]

 

[daviegravy]

How do I add a field from another query to a reports record source if it's already using another query as its source?

 

[dhookom]

You just view the design view of the query and add the new query. It's exactly like adding multiple tables or queries to any query.

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]