William Hindman
wdhindman at bellsouth.net
Mon Nov 3 19:32:43 CST 2003
...write a module ...attach it to a button ...in the module use a DoCmd.RunQry(sic ...I'm writing off the top here) sequence to append any new empID records (without the Hours fields), then update Hours1, and then update Hours2. ...if this is mission critical data, I'd back off the queries and use transactions instead since with the queries the user won't know if everything got transferred correctly ...with transactions its an all or nothing deal which is more difficult to write but much more reliable ime. William Hindman <http://www.freestateproject.org> - Next Year In The Free State! ----- Original Message ----- From: "Brown, Bryan" <Bryan.Brown at acsalaska.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> Sent: Monday, November 03, 2003 8:12 PM Subject: RE: [AccessD] Summarizing like data - suggestions? > William - thanks for the info. > > This will be done monthly. I think your idea will work fine for this month > (also read as "tomorrow!"), but I'll need to end up with a process that the > end-user just "clicks" start to finish :-) > > Bryan > > -----Original Message----- > From: William Hindman [mailto:wdhindman at bellsouth.net] > Sent: Monday, November 03, 2003 4:06 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Summarizing like data - suggestions? > > > Bryan > > ...is this a 1-time fix or are you going to have to repeat this again and > again? ...the solutions differ. > ...the simplest 1-time fix ime would be to create a table with a unique > index set for empID and then query the source table to append all records to > it ...dupe empIDs wont be appended ...then you should be able to run update > queries on the new table Hours1 and Hours2 fields using calc fields on the > source table Hours1 and Hours2 field to total each records hours that match > the empID ...I have not done this but it should be doable in the query > itself, if not you'll need to write and call a function that does it. > > William Hindman > <http://www.freestateproject.org> - Next Year In The Free State! > > > ----- Original Message ----- > From: "Brown, Bryan" <Bryan.Brown at acsalaska.com> > To: "'Access Developers discussion and problem solving'" > <accessd at databaseadvisors.com> > Sent: Monday, November 03, 2003 7:41 PM > Subject: [AccessD] Summarizing like data - suggestions? > > > > Hi everyone, > > > > I listen a lot and offer help when I'm able (which, as a relative newcomer > > when compared to many of you, is not often) and now I could use some help. > I > > have two tables of like data (or it could be one table with multiple rows > of > > similar records) and I need to combine the records when appropriate into > one > > table... > > > > It seems simple enough, but it eludes me. I have about 3000 records to > > compare and summarize. If all data elements, except hours, are the same > > then, I add the hours, else I have to have separate entries. Any > suggestions > > would be appreciated - Thanks! > > > > Example of Multiple rows in one table: > > > > Sample Data Before Summary > > Emplid;Name;Rate ;Title ;Hours1;Hours2 > > 100001;Fred; 9.50;Dishwasher;40 ;22 > > 100001;Fred;15.00;Head Cook ;35 ;18 > > 100002;Bob ; 9.00;Waiter ;40 ;22 > > 100002;Bob ; 9.00;Waiter ;60 ;10 > > > > > > Desired Result Data after Summary > > Emplid;Name;Rate ;Title ;Hours1;Hours2 > > 100001;Fred; 9.50;Dishwasher;40 ;22 > > 100001;Fred;15.00;Head Cook ;35 ;18 > > 100002;Bob ; 9.00;Waiter ;100 ;32 > > > > > > > > > > > > > > > > This transmittal may contain confidential information intended solely for > > the addressee. If you are not the intended recipient, you are hereby > > notified that you have received this transmittal in error; any review, > > dissemination, distribution or copying of this transmittal is strictly > > prohibited. If you have received this communication in error, please > notify > > us immediately by reply or by telephone (collect at 907-564-1000) and ask > to > > speak with the message sender. In addition, please immediately delete this > > message and all attachments. Thank you. ACS3/11/2003 > > > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >