Omit Duplicates in query

[Stockwell43]

Hello,

I have a database with a subform. My main form has the following fields:
Employee Name, Current Date and Comments. My Subform has: Tasks and volume. I
have the two tables set up as a one to many, one being the main form and many
being the subform. The form works fine, no problems there. However, when I
run a query with all five fields Tasks, Volume comeout fine but I get
duplicates in Employee Name, Current Date and Comments. So the data coming
out of the subform is fine but the main form is replicating with each task
that was entered in the subform. My question is:

How do I get rid of the duplicates???? Did make the relationship wrong?

Thanks!!!

 

[Armen Stein]

However, when I
run a query with all five fields Tasks, Volume comeout fine but I get
duplicates in Employee Name, Current Date and Comments. So the data coming
out of the subform is fine but the main form is replicating with each task
that was entered in the subform. My question is:

If you're running a query with these two tables joined together, it's
expected that the "one" table's data would be repeated. That's how
it's supposed to work.

If you are basing your main form on this query, that's not correct.
The main form should be based only on the "one" table. The subform
should be linked using Master and Child fields, and it will show the
records from the "many" table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Stockwell43]

That's how I have it set up. However, if my subform has 4 records on the
first record of the main form, when I run the query it will show the 4
records from the subform but also show the current date and employee name 4
times instead of once. So if this is how it is suppose to work, how do I get
rid of the duplicate employee name and current date?

Thanks!!

 

[Armen Stein]

That's how I have it set up. However, if my subform has 4 records on the
first record of the main form, when I run the query it will show the 4
records from the subform but also show the current date and employee name 4
times instead of once. So if this is how it is suppose to work, how do I get
rid of the duplicate employee name and current date?

Well, maybe I'm not understanding what you are trying to do. When you
query two tables in a one-to-many relationship, seeing repeated
information from the one-side is expected behavior. That's just the
way queries work.

Another way is to use a subdatasheet, which will give you a plus sign
(+) next to each one-side record, and by clicking the (+) you can see
the many-side records related it. But subdatasheets have their own
performance and other issues too, and I'm not sure if that's what
you're looking for.

I guess the question to ask is: why are you using a query? Looking
at queries directly is more for you the developer, not for an end-user
that is using forms and reports in your application.

If you want to view the data with the repeating information
suppressed, you have to use a form & subform (which you are already
doing) or a report (with header & detail sections).

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Stockwell43]

I know. I just thought there was a way to omit the duplicates before creating
the report. Sometimes when I have hide the dups in my report I get gaps and
it doesn't look as good. Is there is a way to prevent that?

Thanks!!!

 

[Armen Stein]

I know. I just thought there was a way to omit the duplicates before creating
the report. Sometimes when I have hide the dups in my report I get gaps and
it doesn't look as good. Is there is a way to prevent that?

I'm not sure what you mean by "gaps". However, if you're hiding the
duplicates in the report using the Hide Duplicates property, that's
more of a quick and dirty way to do it. Using a header section to
display the main record and a subreport to display the child records
is going to look a lot nicer. Look at Sorting and Grouping options in
your report design.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Stockwell43]

Great, I'll try it Armen and thank you for all your help!!!!