Combining records

[Steve Y]

I need to know how to accomplish the following. I have a table with a large
amount of data. Below is an example of three records:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 6 234
32 2453 4/5/06 2 112

I want to run some type of query that would take BOTH records and combine
them into the following record:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 8 346

The problem that I seem to be having is that the DATE is different and
therefore when I use the Totals option and choose Group By, it does NOT
combine these two records. Is it possible to get done what I am trying to
do?

I REALLY need some help on this ASAP!!!

 

[Mr B]

Steve,

From looking at your data, I would say that the date field in the data where
you want to put the totals would not be accurate if you did as you are
suggesting.

If you are attempting to create a record where you would have the totals,
wouldn't you want the maximum date from the original data? This would then
be the totals as of that date. If this is the case, you can just make your
date field in your totals query a Max of the date field.

Another point. If my thought is correct above, then this seems to be just
calculations and therefore you would not need to move the data to another
table.

 

[Mike Labosh]

I need to know how to accomplish the following. I have a table with a

large
amount of data. Below is an example of three records:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 6 234
32 2453 4/5/06 2 112

I want to run some type of query that would take BOTH records and combine
them into the following record:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 8 346

This should get you the results you want:

SELECT
[Employee #],
[Job #],
MIN([Date]) AS Date,
SUM([Qty]) AS Qty,
SUM([Minutes]) AS Minutes
FROM YourTable
GROUP BY
[Employee #],
[Job #]

Then you need to figure out how to delete or update the records containing
what you consider to be the duplicate subsets of data. You ALSO HAVE TO
MAKE SURE that you take whatever other measures you need to prevent your
anomaly from happening again.

But my own personal opinion is that you still might be a bit off. What you
are asking for violates the normal forms in that I bet Employee#, Job# &
Date seem to be the composite primary key, and that a dual entry that
becomes aggregated into a sum of Qty and a sum of Minutes seems to not make
sense in this context. I would prefer to design it this way (I omit the Qty
field because I don't know what it means):

Employee# Job# StartTimeStamp EndTimeStamp

That way, you have a time-clock metaphor where your employees can log in
[clock in] or log out [clock out] and then when you need to compute the
delta-T, you simply say this:

SELECT [Employee#], [Job#], DateDiff(EndTimeStamp, StartTimeStamp, ??) AS
ElapsedTime
FROM YourTable

Perhaps it's just me, but this seems to have a more solid design.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Steve Y]

Mike:
Great. I understand and am sure it will work. Now I have another situation.
Using the same scenario as originally presented, except now I have two
differen employees.

Employee # Job # Date Qty Minutes
32 2453 4/4/06 6 234
12 2453 4/5/06 2 112

Now is there a way to combine BOTH of these records into one record but also
show that BOTH employees worked on this job? New record in new table would
look something like this:

First Empl Second Empl. Job # Date Qty Minutes
32 12 2453 4/4/06 8 346

Thanks for your help!

Steve Y

I need to know how to accomplish the following. I have a table with a
large
amount of data. Below is an example of three records:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 6 234
32 2453 4/5/06 2 112

I want to run some type of query that would take BOTH records and combine
them into the following record:

Employee # Job # Date Qty Minutes
32 2453 4/4/06 8 346

This should get you the results you want:

SELECT
[Employee #],
[Job #],
MIN([Date]) AS Date,
SUM([Qty]) AS Qty,
SUM([Minutes]) AS Minutes
FROM YourTable
GROUP BY
[Employee #],
[Job #]

Then you need to figure out how to delete or update the records containing
what you consider to be the duplicate subsets of data. You ALSO HAVE TO
MAKE SURE that you take whatever other measures you need to prevent your
anomaly from happening again.

But my own personal opinion is that you still might be a bit off. What you
are asking for violates the normal forms in that I bet Employee#, Job# &
Date seem to be the composite primary key, and that a dual entry that
becomes aggregated into a sum of Qty and a sum of Minutes seems to not make
sense in this context. I would prefer to design it this way (I omit the Qty
field because I don't know what it means):

Employee# Job# StartTimeStamp EndTimeStamp

That way, you have a time-clock metaphor where your employees can log in
[clock in] or log out [clock out] and then when you need to compute the
delta-T, you simply say this:

SELECT [Employee#], [Job#], DateDiff(EndTimeStamp, StartTimeStamp, ??) AS
ElapsedTime
FROM YourTable

Perhaps it's just me, but this seems to have a more solid design.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

The problem that I seem to be having is that the DATE is different and
therefore when I use the Totals option and choose Group By, it does NOT
combine these two records. Is it possible to get done what I am trying to
do?

I REALLY need some help on this ASAP!!!