[AccessD] Summarizing like data - suggestions?

William Hindman wdhindman at bellsouth.net
Mon Nov 3 19:06:08 CST 2003


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
>




More information about the AccessD mailing list