Major Duplicate Record Problemo (1 Viewer)

[dkinnz]

Hello Forum,

I have a problem with duplicate records in my query. I know why the duplicate records are occuring and I have spent a ridiculous amount of time trying to fix the problem, but I'm really at a lost and am quite desperate for some help.

Here's some background info that shows the cause of the problem:
I have two tables. In the first table (tblMainData), there are three columns of interest (P1, P2, & P3). The second table (tblGroupList) has two columns (PN & GroupList). A relationship exists such that the data in "PN" is parital-text values of the data in columns "P1", "P2" and "P3".

For example, "P1" in tblMainData may contain "A1235XX". The "A1235" is what's important, so that's one of the values that I have in "PN".

Continuing, I have a user form that uses the "GroupList" field as the RowSource for one of my combo boxes (cboGroup), and this field contains an (ALL) option. So, when a value is selected from cboGroup, I use the following Criteria filter

Like [PN] & "*"

in my query (qryFilterGroup) to look for those partial text fields that match what's in fields P1, P2 and P3. The results are then shown in a subform.

Here-in, I believe, lies the source of the duplicate records. In my userform, if "(ALL)" is selected under the cboGroup combo box, a new record is created for each field P1, P2 and P3 in tblMainData that contains data and that is related to the partial text matching Criteria. A new field is created because each field corresponds to one of the values in the "GroupList" field.

This is really difficult to explain, and I don't really know if I should continue without writing a book. If someone or someone(s) could be so kind, please have a quick look at my attached database as it's obvious to see my problem. Any help is greatly appreciated!!

 

[Ghostjung]

It doesn't look like there are any new records added. Rather, it looks like that your query results just isn't showing what you want. If you mean something like this;

Location	P1	P2	P3	Status	PN	Group Name	PrimaryKey1
GA		A5568Z	A7565	A1236	Out	A1236	Alpha		3
GA		A5568Z	A7565	A1236	Out	A7565	Omega		3
GA		A5568Z	A7565	A1236	Out	A5568	Kappa		3

Then it maybe due to the set-up of your query criterias (the usage of having 3 "or"s). As you can see from this example, it appears as if there are "duplicates". However, if you look at the [PN] and [group name] (which are NOT duplicated), then the records are not really duplicates at all. It is just matching up your PNs with each of your [P1] [P2] and [P3] fields for your record 3 in tblMainData.

I'm not sure how to solve this problem though, maybe someone else can. =)

 

[neileg]

I think Ghostjung's analysis is correct.

It looks to me as if your design is not normalised. P1, P2 and P3 should probably be separate records in a linked table, not the repeating group of fields that you have now.

 

[dkinnz]

Thanks for the reply neileg and ghostjung. You're right, it does just appear to be creating duplicate records.
I'm really depressed now as I have no idea what to do from here...If anyone else has any solutions I could really use some help!!!!

Neileg,
I'm giving that linked table trick a try. I'm clearly not that familiar with Access and have been researching linked tables. I'm still not sure how this would help with my problem. Could you please be a bit more specific on what exactly you meant that I should do with your recommendation? Thanks a ton!