Genius123
asked on
Access Join Query
Hello,
I'm trying to join two records into one and am having trouble. I made a simple example of what I'm trying to do in this PDF: table.pdf
The Excel file is just so I could show you the results I want.
Thanks for your help!
Joel
I'm trying to join two records into one and am having trouble. I made a simple example of what I'm trying to do in this PDF: table.pdf
The Excel file is just so I could show you the results I want.
Thanks for your help!
Joel
@Pawan,
Looks like asker needs it in Access.
So instead of case when, use equivalent function,
Looks like asker needs it in Access.
So instead of case when, use equivalent function,
iif(a.name = 'B', a.amount,0)
Thank you bfuchs. Updated my query.
SELECT a.Job, a.Type
, MAX(iif(a.name = 'B', a.amount,0)) Value1
, MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value2
, MAX(iif(a.name = 'A', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value3
FROM
tblTest a
GROUP BY a.Type, a.Job
Try below..
SELECT a.Job, a.Type
, MAX(iif(a.name = 'B', a.amount,0)) Value1
, MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value2
, MAX(iif(a.name = 'A', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value3
FROM
tblTest a
GROUP BY a.Type, a.Job
SELECT a.Job, a.Type
, MAX(iif(a.name = 'B', a.amount,0)) Value1
, MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value2
, MAX(iif(a.name = 'A', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) Value3
FROM
tblTest a
GROUP BY a.Type, a.Job
put word as before value1
like
same to the other values
like
MAX(iif(a.name = 'B', a.amount,0)) as Value1
same to the other values
ASKER
I'm sorry, I don't understand. Your example is the same as the previous one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Worked perfectly.
Ohhh ... I missed the as keyword in my query.
Great ! thnx bfuchs
Great ! thnx bfuchs
ASKER
Thank you. Worked perfectly.
Try below.
Open in new window
Enjoy!