I am trying to design a report in MS Access 2003

See the sample scenario

1. Having 3 tables
2. One parent table (called X) and other two tables Y and Z are child tables (one to many relationship)
3. Designed a form to provide a facility to filter the report output

- With lot of fields from the three tables (input boxes, selection boxes, check boxes)
- With 5 field names from table X to sort the report output (user can select only one value -option box)


- User can select ASC or DESC options

Now I want to design a report with the 10-15 columns from Table X and Y

I tried but facing so many problems with sorting, grouping, duplicate values...etc

I tried the following two methods...

1. Created a single sql, contains all the required columns from all 3 tables, designed only one report, but got duplicate entries in the report - don’t know how to avoid

2. created a main report (for X) and sub report (for Y), but don’t know how to pass the search parameter to the sub report (filter='abcd' in the sub report throws run time error)

can anyone please suggest some idea?

-Kart