Kamis, 17 Mei 2012

RE: [MS_AccessPros] Auto Number and duplicates

 

Good. I'm glad there wasn't too much damage. I've had weird times when my back end tables went corrupt, and then after I did a compact/repair, it lost the pk.

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Thursday, May 17, 2012 6:36 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Auto Number and duplicates

John,

I started Bills suggestion and realized it was only 21 dups. I went through each duplicate and deleted one of the duplicates that did not have any related records in the referenced table and then pasted it back and the autonumber went to the next record. I did noticed that the majority of the duplicates were mostly on the same day. I am not sure why that is the the case but it was. So I was able to get the numbers to be unique and then changed the indexed to no duplicates and to PK. I did not find any duplicates so I think it is fixed. I copied all the records to an excel spreadsheet to document what they were before and did a backup before the changes.

Jim Wagner
________________________________



________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 16, 2012 10:40 PM
Subject: RE: [MS_AccessPros] Auto Number and duplicates



Actually, Bill's suggestion is better. It doesn't involve an Update query at all!

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Wednesday, May 16, 2012 11:55 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Auto Number and duplicates

BEAUTIFUL! Gosh John, you must be an author. ;-)

I do like how you explain and talk people through the language for creating queries. :-)

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, May 16, 2012 12:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Auto Number and duplicates

Further to what Liz has said:

Your new table should now have a unique AutoNumber as well as the old number. In a *backup* copy of the related table, do this:

Update RelatedTable
SET RelatedTable.Fkey = DLookup("NewAutoNumKey", "ParentTable", "OldAutoNum = " & [RelatedTable].[Fkey])

What this will do:

It will update the old foreign key that used to point to the old autonumber field that had duplicates so that it points to the new AutoNumber unique key.

You should find out what numbers were duplicated in the old "parent" table and then look at the the "child" records that are related to "parent" records that have those duplicate numbers in the old key. You may need to manually re-assign some of the related records.

By the way, the ONLY way you can get duplicates in an AutoNumber field is if it has no index or allow duplicates and you force-enter a duplicate number via an Append or Update query.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Wednesday, May 16, 2012 8:17 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] Auto Number and duplicates

And the new pk field is of course empty and autonum so when you append the old table in to the new structure, it should renumber. I might not be good at explaining myself here.

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, May 16, 2012 11:01 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Auto Number and duplicates

Liz

There are dups in the tables, so I get a nasty error saying that violations are preventing it from appending. I am not much of a violating person

Jim Wagner
________________________________

________________________________
From: Liz Ravenwood <liz_ravenwood@beaerospace.com>
To: "'MS_Access_Professionals@yahoogroups.com'" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, May 16, 2012 10:44 AM
Subject: RE: [MS_AccessPros] Auto Number and duplicates

0. BACK UP All Stuff
1. copy structure on to a new table/s structure only
2. amend new table design/s to have an additional pk autonum, and the old autonum to just num
3. append/paste old table to new table/s
4. establish new relationships based on the new pk (but I'd leave the old number's behind for reference)
Look at what you get and see it it links out okay

Best of luck. What a fun little mess.

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, May 16, 2012 10:38 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Auto Number and duplicates

I failed to mention that there is a related table with task details that are linked together

Jim Wagner
________________________________

________________________________
From: luvmymelody <luvmymelody@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 16, 2012 10:33 AM
Subject: [MS_AccessPros] Auto Number and duplicates

Hello everyone,

I just discovered that my Auto number has duplicates. So I checked the design and it has Yes(Duplicates OK). First of all my ignorance was that I always thought Auto numbers would be indexed as no dups. So Now how do I renumber these tasks?

Thank You

Jim Wagner

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

------------------------------------

Yahoo! Groups Links

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.




[Non-text portions of this message have been removed]



------------------------------------

Yahoo! Groups Links




This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar