reports using union query

[rahoffman]

I don't really know alot of sql/vba and i'm fairly knowlageable about access. I am thinking that a union query will help me display all the data for a particular patient. I have enclosed a few tables and forms. If you could break it down to really simple terms I would appreciate it. What I need in particular is to show all the choices for the patients choices for both forms. I have looked in the help file but i'm not grasping the concept. Also on the forms i have a button that will make a report but it will print all the reports for all the records. I only want it to be able print the record that is currently being viewed.

Thanks for any help,

 

[WayneRyan]

ra,

The union query must return similar rows. Since each survey
has different number of questions, this approach is out.

What you can do:

Keep your button to launch the report.

Now, base the report on a new query. The new query will
have both tables in it. You'll see that Access links the tables
for you. Then you need to specify in the criteria field for
SSN:

=Forms![Inpatient Survey]![Patient SSN]

Then you will get just the one patient's report. But the form
must be open and on the desired person.

Then painfully design your report. As you add each new
field, the dropdowns will show you which survey they are from.

Wayne

 

[rahoffman]

Are you talking about a make table query? If so can you do that in access 97?

 

[WayneRyan]

ra,

No, not a maketable query.

Just a query that will return the "join" of your two tables.

Make a new query, in design view, add each of your tables,
you will see that Access "joins" them on your id field.

Save it and run it.

To keep it from returning everybody, you can put criteria in
the SSN field.

Later, you can add Forms![YourMainForm]![SSNField] to the
query and your report will be dynamic.

Wayne

 

[rahoffman]

gotcha

as always ur the man!

 

[rahoffman]

Well do I need to try to make the report in design view only? Or will I be able to ues the wizard, but I must take in the fact that it is a fairly large query?

 

[rahoffman]

Where in the query will I need to place this function?
Forms![YourMainForm]![SSNField]

 

[WayneRyan]

ra,

You can use the wizard, just tell it to base it on your query.
You'll probably have to get it into design view and move things
around yourself.

On the query, in the criteria box:

=Forms![Inpatient Survey]![Patient SSN]

That way it will only do the report for the person who is
on the OPEN Inpatient Survey form.

Wayne

 

[rahoffman]

When I try to create the repoort it tells me that I am selecting too many fields. Sir if you could send me back a sample from the zip file of how you would do it, that would be much appreciated.