Clever Geek Handbook

Combining 2 similar fields into 1 in MS Access

I have 5 columns: c1 = Item, c2 = LHrs, c3 = WHrs, c4 = Lead, c5 = Worker, built as such:

ITEM  LHRS  WHRS  LEAD  WORKER
123   25    10    John  Gary
456   15    20    John  John

I want the results to be displayed like this:

RESOURCE  ITEM  HRS
John       123   25
Gary       123   10
John       456   35

Matching the names and executing the "AS Resource" results doesn't break it up the way I want it to. Basically I want to know for each person, for each element, how many hours they work if they are the lead (LHrs) or the worker (WHrs).

Thoughts?

+3


source to share


1 answer


How about a UNION query?

SELECT item, person, sum(hrs) AS sumhours 
FROM (
   SELECT Item, lhrs as hrs,lead as person from wk
   UNION ALL
   SELECT Item, whrs, worker AS person from wk) a
GROUP BY item, person


Where wk is the name of the table.

+3


source






More articles:


All Articles