Jumat, 18 Mei 2012

Re: [MS_AccessPros] Auto Number and duplicates

 

Jim

That may very well be. Run a query to find your orphans like this:
SELECT MyField
FROM childtable LEFT JOIN parenttable
ON childtable.parentkey = parenttable.parentkey
WHERE parenttable.parentkey is null

That will return all the orphans. Hopefully, you don't have to spend the rest of the year fixing these.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...> wrote:
>
> Bill
>
> I was able to get it working like you said, but the relationship between the 2 tables shows indeterminate not one to many. I think that I need to change those records in the second table to the correct taskId
>  
> Jim Wagner
> ________________________________
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Wednesday, May 16, 2012 11:27 AM
> Subject: Re: [MS_AccessPros] Auto Number and duplicates
>
>
>  
> Good greif, Jim! this is going to take some manual editing. Set the relationship to cascading updates.
>
> 1. Open the table in design view and change the Autonumber to a Number(long) data type.
> 2. Create a Find duplicates query to get a list of all your dups.
> 3. Open the parent table in datasheet view, sort by the autonumber and change each dup's value to the highest value + 1
> 4. run your dups query again to make sure you caught them all.
>
> 5. right-click the table and select Copy.
> 6. in a blank area where the tables are, right-click and select Paste / Structure only.
>
> 7. Now change the long in the new table to an autonumber with a unique index.
> 8. use an append query to append everything into the new table.
> 9. Remove the relationship between the old table and its child table.
> 10. rename the old to something like MyTable_original; rename the new table to MyTable.
> 11. set up your relationship to the child.
>
> Hopefully, if you did everything right (and I didn't forget a step) you will be where you want to be.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@> wrote:
> >
> > I failed to mention that there is a related table with task details that are linked together
> >  
> > Jim Wagner
> > ________________________________
> >
> >
> >
> > ________________________________
> > From: luvmymelody <luvmymelody@>
> > 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]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar