one report based on multiple queries

  • Thread starter Thread starter accessusermm
  • Start date Start date
A

accessusermm

Hi All

I'm a relatively new user and have been learning Access via the on-line
tutes and these very helpful forums. I am having an issue creating a report
which draws on the results of two parameter queries (ideally 4) and am hoping
some of you might have some tips (you'll have to step me through though as
I'm not a programmer).

I have three tables - one with contact information, one recording details of
dates certain tasks have to be done (sending and chasing questionnaries) and
one with test information. All are linked by a participant ID number
(one-to-one).

I have set up two parameter queries (one for Q1 and one for Q2)
Q1 complete (Y/N) criteria (No)
Q1 chase date Between #1/1/09# And [enter Q1 chase date 1:]
Q2 chase date Or Between #1/1/09# And [enter Q1 chase date 2:]
where #d/m/y# is the beginning date of the study

I have also set up two parameter queries for 'send date' for sending two
different things (they draw on info in different tables). These are a little
simpler as they don't have an 'or' criteria.

My problem is I don't seem to be able to create a report which draws
information from more than one query. I either get just the field names
displaying or only one set of data repeated. I've tried to create a report
using only one query and then add the other query via a subreport but when I
do that the subreport does not appear in the report.

I am using access 2003 but will be upgraded shortly to 2007.

I have seen a few people with similar queries in this forum but have not
been able to follow the advice given exactly and don't know how to alter it
to fit my situation.

Thanks for reading this long post - I hope someone can help.
 
Without seeing your queries I would say that they could be combined into one
by using left join on [participant ID] from [contact] to [details] and to
[test information].
 
Thanks for your reply Karl. I've been doing a bit more thinking about it and
I think I need to combine each of the 2 related queries into a 'master'
query and then use this to generate 2 separate reports. Thing is I'm not
sure how best to do this. I've been doing some reading and it looks like a
Union SQL query could help but I'm not sure how to set up my date perameters
for this. Can anyone suggest how to do this? Do I need to use a crosstab
query? If so, how does this work? or can I get away with various WHERE
commands? Something that I think might be a problem is that for the Q1
query, the field [Q1_returned] must equal 'no' but for the Q2 query the field
[Q1_returned] must equal 'yes' and [Q2_returned] must equal 'no'. I don't
know how to set things up so these won't 'fight' each other

I have used the Access query wizard to set up each of the 4 queries so I
can't show you the syntax for these. However my attempt at a UNION query
looked a bit like this...

Select [study_ID], [First_Name], [Surname], [Q1chase1_date_due],
[Q1chase2_date_due]
From [q_Q1_kit_chase]
Union
Select [study_ID], [First_Name], [Surname], [Q2chase1_date_due],
[Q2chase2_date_due]
From [q_Q2_chase];

I also used 'sort by study_ID' and played around with WHERE commands to try
and get parameters working, for e.g.
Where [Q1chase1_date_due] Between #01/01/2009# And #[enter_date_due]# Or
[Q1chase2_date_due] Between #01/01/2009# And #[enter_date_due]#

However, I'm not sure how to combine all these into a functioning query that
will do what I want! Any further suggestions appreciated :)


KARL DEWEY said:
Without seeing your queries I would say that they could be combined into one
by using left join on [participant ID] from [contact] to [details] and to
[test information].
--
Build a little, test a little.


accessusermm said:
Hi All

I'm a relatively new user and have been learning Access via the on-line
tutes and these very helpful forums. I am having an issue creating a report
which draws on the results of two parameter queries (ideally 4) and am hoping
some of you might have some tips (you'll have to step me through though as
I'm not a programmer).

I have three tables - one with contact information, one recording details of
dates certain tasks have to be done (sending and chasing questionnaries) and
one with test information. All are linked by a participant ID number
(one-to-one).

I have set up two parameter queries (one for Q1 and one for Q2)
Q1 complete (Y/N) criteria (No)
Q1 chase date Between #1/1/09# And [enter Q1 chase date 1:]
Q2 chase date Or Between #1/1/09# And [enter Q1 chase date 2:]
where #d/m/y# is the beginning date of the study

I have also set up two parameter queries for 'send date' for sending two
different things (they draw on info in different tables). These are a little
simpler as they don't have an 'or' criteria.

My problem is I don't seem to be able to create a report which draws
information from more than one query. I either get just the field names
displaying or only one set of data repeated. I've tried to create a report
using only one query and then add the other query via a subreport but when I
do that the subreport does not appear in the report.

I am using access 2003 but will be upgraded shortly to 2007.

I have seen a few people with similar queries in this forum but have not
been able to follow the advice given exactly and don't know how to alter it
to fit my situation.

Thanks for reading this long post - I hope someone can help.
Click to expand...
Click to expand...
 
Back
Top