combo boxes , query, related report

[lambert]

Greetings, I am an occasional Access user and have a problem with 2 subforms containing a combobox each .
I have a report related to these combos and 2 lines to receive the results of these combos.When I work with only one combo and 1 line in the report, it works well but with 2 combos I have 2-3 times the same line repeated in the report. It must be a very simple step to solve this problem but right now I spent 1 week on it and was not able to solve it. My report is based on a query on the subforms.
Thanks in advance for your help

JML

[rpeare]

Can you provide a sample database to work with, there's not enough information here to work with but I am guessing it's related to table join.

p.s. if you edit for misspelling you should spell misspelling correctly!

[lambert]

Thanks for your quick answer ! How do I provide a sample data base on this forum? I am new
JML

[rpeare]

create a copy of your database, take out any information you don't want made public, put enough fake data in there to re-create the problem then compact/repair, zip it up and use the GO ADVANCED button to attach your zip file.

[lambert]

create a copy of your database, take out any information you don't want made public, put enough fake data in there to re-create the problem then compact/repair, zip it up and use the GO ADVANCED button to attach your zip file.

db7.zipdb7.zipdb7.zip

[lambert]

To start, press on the form "formdepart" and to see the report go to "reportessai"

Thanks a lot for your help

JML

Sorry to have attached the file 3 times: I did'nt realize it was already attached !!!

[rpeare]

Ok here is the problem. In your query you have:

main table: Tabledeparttbl

three subtables:
visitetbl
ordonnancetbl
ordonancetbl2

the three subtables all have MANY records for each ONE record of TableDeparttbl.

In essence the way you have joined your tables for this query you are going to get

the number of records in visitetbl * the number of records in ordonnancetbl * the number of ordonancetbl2 = total records in the query

In this case with your example data patient number 1 has 1 visit record, 4 records in ordonnancetbl, 3 records in ordonancetbl2

1*4*3 = 12 records.

If you want to create a report out of this data you will have to create subreports because it looks to me like the only link between ordonnancetbl and ordonancetbl2 is to the TableDeparttbl. In other words their medication is linked to the client, NOT to the visit. In short you want to create a report very similar to the way you constructed your form to have to function.

[lambert]

Hello ! Thanks for your input ! But even with subreports I get the same problem. However with only one "ordonnancetbl" it works perfecly well.It's only when I want to have the results of the 2 subforms that I get the multiple answer. In fact if I have 3 medecine in the combo, I get 3 times the three medecines!
Thanks again
JML

[rpeare]

I explained why that's happening.

Your MAIN report should be based on your MAIN table (tabledateparttbl) and the visit table (visitetbl) because you have a date criteria on the visit.

remove the ordonnance tables from your MAIN query.

Create two sub reports, each report based on one of your two ordonnance tables. Then put those subreports on your main report linking by them via your patient number.

[lambert]

I explained why that's happening.

Your MAIN report should be based on your MAIN table (tabledateparttbl) and the visit table (visitetbl) because you have a date criteria on the visit.

remove the ordonnance tables from your MAIN query.

Create two sub reports, each report based on one of your two ordonnance tables. Then put those subreports on your main report linking by them via your patient number.

Hello rpeare!
You were absolutely right!!! As soon as I did what tou told me I got only one copy of the 2 subreports ! I was also making 2 mistakes in my criteria but nothing related to the main mistake so this problem is solved !!