Jumat, 18 Mei 2012

RE: [MS_AccessPros] Auto Number and duplicates

 

Wow. Thanks Bill. That's a cool story.

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 Bill Mosca
Sent: Friday, May 18, 2012 1:20 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Auto Number and duplicates

Jim

I used to be a general manager for a restaurant. I was trying to come up with a way to make sure I had enough staff on each night to cover banquets. The computer guru at our corporate office suggested I look into Access. It was completely the WRONG thing to use but I found other ways to use it like order forms for food and beverages saving me a couple hours a day.

The more I used it, the more I learned. The more I learned, the more I wanted to make it do more. I had no internet or user group access so I basically just bought the biggest, fattest book on Access and read it. All of it. Not much programming, but lots of how to stuff.

I finally burned out on restaurant management and decided to make a career change. It was my loving wife who pushed me into taking programming and database administration courses. Access just sort of stuck with me as a hobby. I got my first job out of computer school because they needed someone to run ad hoc reports through Access.

The job was duller than dishwater but it got my career going.

Bill


--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...> wrote:
>
> Bill
>
> I do love Access. I took a class in Jr College years ago when I was learning about computers that taught about the office suite. The last 2 weeks of the class was about Access. I was hooked and decided to get a certificate in Database Management. After that I went for my Bachelors in computer information then a job building databases. All because of 2 weeks of an office class introducing Access. I wish I knew more about programming to make access more functional.Â
> Â
> Jim Wagner
> ________________________________
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, May 18, 2012 8:22 AM
> Subject: Re: [MS_AccessPros] Auto Number and duplicates
>
>
> Â
> Jim
>
> Don't ya just love Access?! Glad you got it fixed.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@> wrote:
> >
> > Bill
> >
> > I did not get any records back which is great.
> >  I think that I figured out how the whole mess happened. I have a form that shows Tasks or projects that are active. I have the TaskID setup so that when the user clicks the taskid on the active form the main data entry form opens and then is filtered to that specific taskid. Well the macro that filters that form had stopped working and I did not notice that the form was not on the correct record. I was adding records to the wrong record. Because it happened on the same day or close to it, I questioned it. Well I clicked on one of the taskid's to edit the record and noticed that the current record did not have multiple records in the subform. It was a big red flag. So I tested it again and realized it had broken. I fixed the macro and all is well.ÂÂ
> >
> > Thank You for your help
> > ÂÂ
> > Jim Wagner
> > ________________________________
> >
> >
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Friday, May 18, 2012 7:43 AM
> > Subject: 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]
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [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