Report based on multiple queries

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

I have a report primarily based on one query, call it
query 1. I have another query (query 2)that performs a
calculation on the information on query 1. I am trying to
get the report to show information from both queries.
The report fetches the data from query 1 fine, but the box
which is meant to fetch data from query 2 produces
a "enter parameter value" window, with the name of the
query and field showing as if it didn't exist (It does
exist).
Does anyone have any recommendations?

Thanks in advance for your advice,
Adam
 
Adam said:
I have a report primarily based on one query, call it
query 1. I have another query (query 2)that performs a
calculation on the information on query 1. I am trying to
get the report to show information from both queries.
The report fetches the data from query 1 fine, but the box
which is meant to fetch data from query 2 produces
a "enter parameter value" window, with the name of the
query and field showing as if it didn't exist (It does
exist).
Does anyone have any recommendations?
Click to expand...


"boxes" don't fetch data from a query. I have no idea what
you're trying to do, but most likely you want to use the
DLookup function or possiblly change it from a text box to a
subreport.
 
When Access generated a report for me it produced a series
of text boxes (as properties refers to them) that pull
data from a query, the query that the report is based on.
I reassigned one of these text boxes to instead draw data
from a different query.

I am trying to produce a report which lists records from a
query. With each record, I want it to show a record from
another query which has done mathematics on each record
from the first query. A subreport cannot reference/link
each field in this way, but instead will only show all the
records in my second query.

As an example, my first query might have adam, dave and
peter in it. There is a field showing their age. My second
query calculates their age in dog years. My report has a
line for each record in the first query - on each of these
lines I want to include a piece of data from the second
query, their dog age.

Hopefully this has clarified the issue. Do you have any
suggestions?

I really appriciate your help.
Thanks,
Adam
 
My appologies, a subreport worked wonderfully after I used
the correct Master/Child relationship.

Thank you very much!!!
Sincerely,
Adam
 
Adam said:
When Access generated a report for me it produced a series
of text boxes (as properties refers to them) that pull
data from a query, the query that the report is based on.
I reassigned one of these text boxes to instead draw data
from a different query.

I am trying to produce a report which lists records from a
query. With each record, I want it to show a record from
another query which has done mathematics on each record
from the first query. A subreport cannot reference/link
each field in this way, but instead will only show all the
records in my second query.

As an example, my first query might have adam, dave and
peter in it. There is a field showing their age. My second
query calculates their age in dog years. My report has a
line for each record in the first query - on each of these
lines I want to include a piece of data from the second
query, their dog age.
Click to expand...

I can't be sure, but I have to wonder about the use of the
second query. IF it is based on the same table as the
report and you are not using aggregate functions in the
second query, then the first query can do the type of
calculations in your example above:

SELECT *, Age/7 As DogsAge
FROM table
WHERE whatever

OTOH, if you're doing a some aggregate functions then you
can JOIN the second query to the table in the first query:

SELECT table.*, query2.DogsAge
FROM table LEFT JOIN query2
ON table.key = query2.key
WHERE whatever

With either of these approaches, the calculated fields are
in the report's record source and the text boxes can be
bound directly to those fields.

Using a subreport is actually sort of like the Join method
above (with more formatting features) where the key fields
are specified in the Link Master/Child properties.
--
Marsh
MVP [MS Access]

PS. I hope you're not actually storing age values in a
table. An age might become incorrect tomorrow and all
records will certainly have inaccurate ages a year from now.



 
Back
Top