UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Duplicate Returns On Report, Access 2016    
 
   
KimFarm
post May 10 2018, 11:56 AM
Post#1



Posts: 50
Joined: 19-September 12



I have two reports I run based on the same tables. The reports list training staff has attended. One report lists all staff and all training. It works properly. The second report lists individual staff members and their training. The individual report works properly except for one staff member. Her report duplicates each entry. It does not do this for any other staff member. The same individual's data outputs properly in the "all inclusive" report.

At first I thought there must be something in one of the table entries that makes it duplicate, i.e. a character, but then I would think it would duplicate in both reports, and it doesn't. The fact that it works properly for everyone except this one individual has me stumped.

TIA in advance for any advice.
Go to the top of the page
 
GroverParkGeorge
post May 10 2018, 02:12 PM
Post#2


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


This is most likely to be the result of a one-to-many relationship between the main table and the child table. That individual probably has more than one training event, resulting in additional records being returned.
Go to the top of the page
 
KimFarm
post May 10 2018, 03:17 PM
Post#3



Posts: 50
Joined: 19-September 12



I'm not sure that's the issue. All staff members have multiple training events. This particular staff member's training is showing up like this:

Cake decorating
Cake decorating

Baking for beginners
Baking for beginners

Sprinkles, The Good, The Bad, The Ugly
Sprinkles, The Good, The Bad, The Ugly

Fondant Foolishness
Fondant Foolishness

Whereas everyone other staff member only has one entry showing (which is proper).

This only occurs when I run the "Individual" report. When I run the "Group" report, her entries show properly.
Go to the top of the page
 
RJD
post May 10 2018, 04:03 PM
Post#4


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi: I agree with George that this is most likely a 1:many relationship somewhere you don't expect. But, with the information we have so far, it would not be likely we could diagnose this.

Is it possible you could post a db (no sensitive data, relevant objects, zipped) for us to examine? Getting eyes-on with your db usually leads to a quick solution. Just tell us which report - and we can backtrack from there.

In the meantime, what does the report query look like (SQL and results)? ...

HTH
Joe
Go to the top of the page
 
GroverParkGeorge
post May 10 2018, 06:21 PM
Post#5


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Is it possible there actually ARE duplicate values? As in someone entered the same training event twice?
Go to the top of the page
 
zaxbat
post May 10 2018, 06:33 PM
Post#6



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I was considering something like that as well....wondering if her header got deleted but not her details....then header and details all added anew. But not sure how it would find the second set of details unless the primary key is not autonumber....that is a possibility.
Go to the top of the page
 
BruceM
post May 11 2018, 06:29 AM
Post#7


UtterAccess VIP
Posts: 7,638
Joined: 24-May 10
From: Downeast Maine


Are there other fields or tables in the query that could cause this? Using another example, if you have a client table and a contacts table (listing contacts for each client), and also a phone numbers table, a contact with two phone numbers could result in all data for that contact appearing twice.
Go to the top of the page
 
KimFarm
post May 11 2018, 03:58 PM
Post#8



Posts: 50
Joined: 19-September 12



I'm trying to upload a zip file for your review, however every time I try it indicates "You did not select a file to upload". I am clicking on "Choose File" then browsing to the file. I then click "Upload" and receive the error message.
Go to the top of the page
 
GroverParkGeorge
post May 11 2018, 05:17 PM
Post#9


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


To upload Access accd files you must first compress them into a ZIP file. I recommend compact & repair before you ZIP it.

If you are having trouble getting the upload to recognize the ZIP file, maybe try again, or maybe check if there's something blocking on your network.
Go to the top of the page
 
RJD
post May 12 2018, 08:14 AM
Post#10


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


QUOTE
I am clicking on "Choose File" then browsing to the file. I then click "Upload" and receive the error message.

Don't know if you did a step but left it out of your description ...

After clicking on Choose File, the dialog window appears. Select the zip file to attach and click on OPEN from the dialog window (maybe you left out this step?). Then, when the dialog closes and the file name appears in the box on the reply, click on UPLOAD.

And let us know if you are still having issues attaching the file.

HTH
Joe
Go to the top of the page
 
KimFarm
post May 14 2018, 04:06 PM
Post#11



Posts: 50
Joined: 19-September 12



Ok, I finally got it to upload! I tried to remove entries that did not pertain to this issue, and also the Compression suggested by GroverParkGeorge helped too! I hope you are able to view this properly.

Disclaimer - I am not, but ANY stretch of the imagination, a DBA! Attached File  CLE_Tracking_Database.zip ( 1.58MB )Number of downloads: 1
Go to the top of the page
 
RJD
post May 14 2018, 05:26 PM
Post#12


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi: For Chamberlain (the one doubling up) there are two records in table CarryOverUsed. There should only be one. This is what we were talking about in regards to a 1:many relationship "somewhere" in the data. Should be a 1:1.

See ID 30 and ID 35 in that table. ID 30 shows zero carry over. ID 35 shows .5 and 1.5. Both for Chamberlain. No others persons are duplicated. When ID 30 is deleted from the CarryOverUsed table, there is no doubling of results in the query or report.

HTH
Joe
Go to the top of the page
 
RJD
post May 14 2018, 07:58 PM
Post#13


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Oh, and one more thought. You are using Last Name to identify the attorney in several tables. Bad idea. You should use the AttorneyID instead. This is the unique identifier. What do you plan to do if, in the future, two attorneys have the same last name?

When you need it, you can always get the last name by linking to the attorney table through the ID value.

HTH
Joe
Go to the top of the page
 
KimFarm
post May 15 2018, 09:27 AM
Post#14



Posts: 50
Joined: 19-September 12



Thank you so much Joe! I really appreciate your willingness to look at this for me. I'll make the suggested changes.

Have a wonderful day!
Go to the top of the page
 
zaxbat
post May 15 2018, 09:49 AM
Post#15



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


The important point here is to figure out how a duplicate got in there and to make sure it cannot happen again.
Go to the top of the page
 
RJD
post May 15 2018, 09:52 AM
Post#16


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Let us know if this fixes your issue or if we need to provide more assistance.

Regards
Joe

from phone
Go to the top of the page
 
KimFarm
post May 15 2018, 03:54 PM
Post#17



Posts: 50
Joined: 19-September 12



Joe's suggestion addressed the issue. I appreciate everyone's input. Thank you!
Go to the top of the page
 
RJD
post May 15 2018, 04:55 PM
Post#18


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 02:33 AM