Solved

Can a result be accomplish with just one query?

Posted on 2011-02-19
2
408 Views
Last Modified: 2012-05-11
I have two tables (table1, table2) with description of a product (a car) in these two tables the car field is a common field. In the first table de date of when the car was painted is storage, in table2 the when it was delivered.  How can I accomplish with a query a result as table 3 shown in picture? I need to show all the cars numbers from both tables in one column and in two column show when the car was either painted of delivered.

Thanks in advance for the help

tables.bmp
0
Comment
Question by:Exl04
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 34935474
SELECT t1.[CarNumber], t1.[DatePainted], t2.[DateDelivered]
FROM [table1] t1 INNER JOIN
    [table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
UNION ALL
SELECT t1.[CarNumber], t1.[DatePainted], Null AS [DateDelivered]
FROM [table1] t1 LEFT JOIN
    [table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
WHERE t2.[CarNumber] Is Null
UNION ALL
SELECT t2.[CarNumber], Null AS [DatePainted], t2.[DateDelivered]
FROM [table1] t1 RIGHT JOIN
    [table2] t2 ON t1.[CarNumber] = t2.[CarNumber]
WHERE t1.[CarNumber] Is Null
0
 
LVL 1

Author Closing Comment

by:Exl04
ID: 34935533
Great Patrick, exacly what I needed!

Thanks!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Perl Versus AWK? 7 46
ADODB problem 20 37
Using SQL*PLUS issue with where statement with trunc function 3 27
all contents of form cell highlighted access 2 7
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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