467,161 Members | 889 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Duplicate records in a main form when there are multiple subform records

Hello,

I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.

So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.

I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.

Thanks
Jun 8 '07 #1
  • viewed: 5551
Share:
6 Replies
Hello All,

I'm bumping this because I'm ready to pull my hair out. I know there must be an easy fix and I'm just missing it (or I'm a complete moron)

Any help would make my day.

Thanks
Jun 12 '07 #2
FishVal
Expert 2GB
Hello,

I have been struggling with this for a while. I am working on a rather simple database for claims in Access 2003. I have a table called claims that has a primary key field called Claim #. It is linked to a table called parts. The idea is that a claim can be filed for multiple damaged parts. In other words a claim # could have 0 to several damaged parts. My problem lies with the form that I have created. It is a main form with a subform for the list of parts that were damaged. When a claim has more than one part listed in the subform Access gives me back a record in the main form for each part listed in it's subform.

So if Claim #123 has three parts listed in the subform Access will give me three records (in the main form) for Claim #123. I have tried several things to get it return only one record but so far I have come up empty.

I hope this makes sense. I tried to find this issue in the forum but couldn't. Any help would be greatly appreciated.

Thanks
Hi!

It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.
Jun 13 '07 #3
Hi!

It seems to me that the main form RecordSource property refers to tables join.
To help you I need to know what is the Record Source of your main form.

The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.

Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.

Thanks so much for your help.
Jun 14 '07 #4
FishVal
Expert 2GB
The record source for the main form is a query that includes the two tables in question joined in a one to many relationship by the "claim number". The claim number is a key field in the table on the one side and it linked to "Claim Number" on the many side. There are two other tables linked to the claim table as well but by different fields.

Please let me know if you need more info. I'm pretty new to this and I'm getting pretty frustrated.

Thanks so much for your help.
Hi!

Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.
Jun 14 '07 #5
Hi!

Get rid of this join.
You main form RecordSource should be table Claims, subform RecordSource should be table Parts, link master/child fields ClaimNumber.


That's fantastic!! I knew it would be something easy like that. However, by changing the record source for the main form from a query to the claims table it has created another issue. As I mentioned above the query had two additional tables linked to the claim table. One of them is the Plant Table. The idea was that I could enter the Plant # on the main form and it would automatically populate the plant address. The other linked table was to operate in the same way. Now the Plant number still appears but the address fields all show "#Name?"

Is there something I can do about this?

I can't thank you enough for the help. It looks like I'll get to keep my job for at least another week.
Jun 15 '07 #6
HI,

Never mind my last question. I've figured it out on my own. I set the record source for the main form to a query that DID NOT include the part table but did include the plant and carrier table. Now everything populates automatically and I only get one record per claim number.

This has been a good Friday...so far.

FishVAl, thank you so much for the help.
Jun 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Catherine Jo Morgan | last post: by
6 posts views Thread by 6thirty | last post: by
1 post views Thread by Charles Ledbetter | last post: by
reply views Thread by claus@lucking.dk | last post: by
3 posts views Thread by 6afraidbecause789@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.