Solved

Access Join Query

Posted on 2016-10-05
12
54 Views
Last Modified: 2016-10-25
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
0
Comment
Question by:Genius123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41830909
Hi,

Try below.

SELECT a.Job, a.Type
, MAX(CASE WHEN a.Name = 'B' THEN a.Amount END) Value1 
, MAX(CASE WHEN a.Name = 'A' THEN a.Amount END) - MAX(CASE WHEN a.Name = 'B' THEN a.Amount END) Value2
, MAX(CASE WHEN a.Name = 'A' THEN a.Amount END) - MAX(CASE WHEN a.Name = 'B' THEN a.Amount END) - MAX(CASE WHEN a.Name = 'B' THEN a.Amount END) Value3
FROM 
tblTest a 
GROUP BY a.Type, a.Job

Open in new window


Enjoy!
0
 
LVL 4

Expert Comment

by:bfuchs
ID: 41830924
@Pawan,
Looks like asker needs it in Access.

So instead of case when, use equivalent function,
iif(a.name = 'B', a.amount,0)

Open in new window

1
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41830929
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

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Genius123
ID: 41831995
Thanks for your response.  However, I get this error: error
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41832257
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
0
 

Author Comment

by:Genius123
ID: 41832276
Now I get this:

error
0
 
LVL 4

Expert Comment

by:bfuchs
ID: 41832565
put word as before value1

like
MAX(iif(a.name = 'B', a.amount,0))  as Value1 

Open in new window


same to the other values
0
 

Author Comment

by:Genius123
ID: 41837341
I'm sorry, I don't understand.  Your example is the same as the previous one.
0
 
LVL 4

Accepted Solution

by:
bfuchs earned 500 total points
ID: 41837385
I added word as before value1.

try this

SELECT a.Job, a.Type
, MAX(iif(a.name = 'B', a.amount,0)) as Value1 
, MAX(iif(a.name = 'B', a.amount,0)) - MAX(iif(a.name = 'B', a.amount,0)) as 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)) as Value3
FROM 
tblTest a 
GROUP BY a.Type, a.Job

Open in new window

0
 

Author Comment

by:Genius123
ID: 41837477
Thank you.  Worked perfectly.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837488
Ohhh ... I missed the as keyword in my query.

Great ! thnx bfuchs
0
 

Author Closing Comment

by:Genius123
ID: 41859372
Thank you.  Worked perfectly.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question