use of query makes report very slow

[Pierkes]

Hi,

I have a report which uses a query [Q_rap_reports] to generate the records. The query base is a table called [tbl_records]. In the table are 10 fields registering the whishes of the client called [whish1] through [whish9] that all use the same query to find te corresponding item [Q_whishes].

I started with 2 fields to link to the same query (added the query 2 times to generate the report query). This worked fine allthough a little slow.

Now i tried 10 fields to link to 10 (relationships) copies of the same query [Q_whishes] and the report became extremely slow.

How can i prevent this slow working report ?
Any suggestions ?

Regards,
Pierkes

[jimatqsi]

Pierkes,
Your description is a bit unclear to me. Specifically, I don't understand the joins you are doing. Could you post the SQL code of your query, please. Also, explain the organization of the data more.

Are you saying that each client can make 10 whishes (is this to be 'wishes'? Might be a language error - doesn't matter, 10 'things') Are you saying each client can be associated with 10 of these things but those 10 come from a list of more than 10? And you are trying to get the description of the 10 things from the table Q_whishes?

If that is the case, you might be better making a union query which "joins" 10 separate queries. It will look something like this:

Select thing1 as ThingID from tbl_records
union
Select thing2 as ThingID from tbl_records
union 
Select thing 3 as ThingID from tbl_records

etc...

Save that query. Then make a new query and join that saved query to your Q_whishes table to get the description of all of the ThingIDs. Of course I have left out some fields that you will need, such as ClientID and whatever else might be in those records. You might want to use the form

Select thing1 as ThingID, 1 as IDNumber from tbl_records
union
Select thing2 as ThingID, 2 as IDNumber from tbl_records
union 
Select thing 3 as ThingID, 3 as IDNumber from tbl_records

etc...
if you need to know which of the 10 whishes are in each row of the final query.

I hope that will help.

Jim

[jimatqsi]

I should point out that you will need to type this in the SQL view of the query editor. There is no way to graphically represent a union query in the view we normally use for editing queries.

Also, union queries automatically group identical results. If that is not the desired behavior and duplicates are possible, use "Union All" instead of "union".

[Pierkes]

Hi Jimatsqi,

Thanks for your help. I reviewed it myself and took a different approach....

I made an extra table called tbl_wishes_dbas e and us that to store all wishes of a client in a subform on the main form.

A report is then much easier to create for all wishes.

This way i can make it much easier.
Thanks for your help though !

Best regards,
Pierkes