Duplicated values in subform

[Sue]

First off, I hope your holiday was wonderful & that 2009 will be both happy
and healthy. Thanks for all your help this past year, and thanks in advance
for all your help in years to come.

Now...

I have a subform based on a query. The query functions as expected without
duplicating values, but in the subform, some values repeat. How do I avoid
"seeing double" in the subform? (Actually, some information repeats 8 or so
times.)

Appreciate your help!

 

[Jeff Boyce]

A bit more specific information might help...

What are the datasets and queries your form and subform are using?

When you say "some values repeat", do you mean the entire record is
duplicated, or that a value within a field repeats (we can't see your
subform so we can only guess)?

More info, please...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

Sue

Also, since there's more than one way to connect a main-form and sub-form,
how have you connected yours?


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Sue]

Sorry to give inadequate information, but it's difficult to figure what you
need to know while still avoiding taking up too much of your time with
information that isn't pertinent.

I've connected the subvorm & main form using Client_ID, which is the primary
key in the Client table and the foreign key in the Interventions table.

The Interventions table contains quite a few possible interventions that may
be used for an individual client. And the entire record is repeated...
Abbreviation, Key, and Intervention.

 

[John W. Vinson]

I have a subform based on a query. The query functions as expected without
duplicating values, but in the subform, some values repeat. How do I avoid
"seeing double" in the subform? (Actually, some information repeats 8 or so
times.)

It sounds like you have an incorrect query: what IS the subform's Recordsource
property? If you open it in query datasheet view do you see duplicate records?
Did you perhaps base the form on a Query joining some other table (which might
have eight records for a given subform record)?

 

[Jeff Boyce]

Sue

If the sub "contains quite a few possible interventions that may be
used...", I'm thinking that your data structure might benefit from further
normalization.

If I had to construct a table structure for client interventions, I'd start
with a tblClient with a ClientID (sounds like you did this). Then I'd build
a table with all possible interventions (tlkpInterventions, with
InterventionID).

Finally, I'd build a table to resolve the implicit many-to-many
relationship -- I'm assuming that not only can one client have many
interventions but that one intervention (type) could be applied to many
clients. That third table, a resolver/junction/relation table, might look
something like:

trelClientIntervention
ClientInterventionID
ClientID
InterventionID
DateOfClientIntervention
... (other details specific to THIS intervention with THIS client)

It would be this third table that I'd use as the source for a sub, so I
could see all the interventions that HAD been applied to the client showing
in my main form. And I'd construct a query against the tlkpInterventions as
the source for a combobox on the sub, so I could select which intervention I
was recording.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Coderz]

Just use the query in your subform datasource that does not include a
duplicate query.

http://www.sourcecodester.com - Download free source code