Solved

Create a Select Query

Posted on 2011-09-18
12
335 Views
Last Modified: 2012-06-22
PROBLEM BACKGROUND
 I HAVE TWO CAR LOTS (CAR LOT 1 AND CAR LOT 2).
i AM TRYING TO MAKE A QUERY THAT WILL TELL WHEN  IF BOTH CAR LOTS HAVE THE SAME COLOR CARS.

EXAMPLE

CAR LOT 1 HAS A RED CAR AND A BLUE CAR.
CAR LOT TWO HAS A RED CAR AND A YELLOW CAR.

BOTH LOSTS HAVE RED CARS.


CarColors.accdb
0
Comment
Question by:cssc1
  • 5
  • 5
  • 2
12 Comments
 
LVL 7

Expert Comment

by:BusyMama
ID: 36557553
In the SQL view,

SELECT * FROM
LOT1, LOT2
WHERE LOT1.COLOR = LOT2.COLOR

So, you are basically creating an inner join that will result in only matching colors.  Any color NOT in both tables will not be returned as values in the query.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 36557578
Select A.CarColor from CarInventory A, CarInventory B where A.CarColor = B.CarColor AND A.CarLot <> B.CarLot;
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 36557656
Can you clarify if the cars for each lot are in different tables, or the same table?
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.

 
LVL 7

Expert Comment

by:BusyMama
ID: 36557765
In your example data tables, you have 3 columns for color.  When I presented my first response I originally assumed you would have one car per row in your tables:

tblLotOneCars
CarID
CarColor

tblLotTwoCars
CarID
CarColor

What is the purpose of the columns "Red" "Blue" "Green" "Yellow" in your sample database?
0
 

Author Comment

by:cssc1
ID: 36559244
pdebaets:
   The cars are in different tables
0
 

Author Comment

by:cssc1
ID: 36559248
BusyMama:
What is the purpose of the columns "Red" "Blue" "Green" "Yellow" in your sample database?

I just used each field in the table as the color of the car.
0
 

Author Comment

by:cssc1
ID: 36559251
BusyMama:
   Can you give example of an innerjoin?
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36559719
What I gave you originally is one way to do an inner join, but here is other syntax.  I am selecting "all" of the columns just for ease (the * indicates all columns).

SELECT tblLotOneCars.*, tblLotTwoCars.*
FROM tblLotOneCars INNER JOIN tblLotTwoCars ON tblLotOneCars.Color = tblLotTwoCars.Color;

I would recommend you change your design to just have one column where you type the color of the car in, instead of a different column for each color.  That would be unnecessary columns and cause you to have many empty columns in your data table, which takes up data space for no reason and way down the road could cause performance issues.
0
 

Author Comment

by:cssc1
ID: 36559841
BusyMama:
   Is this example I attached correct?
CARS.mdb
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36559854
Yep!  Now just create your query and you should be all set.
0
 

Author Comment

by:cssc1
ID: 36560595
BusyMama:
   The query is what this question was for. I am un-clear on how to creat this type of query.
0
 
LVL 7

Accepted Solution

by:
BusyMama earned 500 total points
ID: 36560680
Normally I would walk you through this step by step, but I have Microsoft Access 2010, so I'm going to also upload the database for you, in case my steps don't quite match your version.

Create a query using the query wizard --> Simple Query Wizard
Choose all the fields from the Lot 1 table
Using the drop-down choose all the fields from the Lot 2 table
Click Next
Choose Detail
Click Next
Name the query (mine is qryColors)
Click the radio button next to "Modify the query design"
Click Finish

There is automatically an inner join between CarID in Cars1 and CarNo in Cars2 - I'm assuming you created that, but for this query we don't want that.  So I am deleting it ONLY INSIDE THE QUERY (this does not affect your relationships elsewhere in the database).

Then I click on CarColor in Cars1 and drag it over and drop it on CarColor in Cars2.

That automatically creates an inner join for you.  The SQL programming behind the query is now:
SELECT LotCARS1.CarID AS LotCARS1_CarID, LotCARS1.CarNo AS LotCARS1_CarNo, LotCARS1.CarColor AS LotCARS1_CarColor, LotCARS2.CarID AS LotCARS2_CarID, LotCARS2.CarNo AS LotCARS2_CarNo, LotCARS2.CarColor AS LotCARS2_CarColor
FROM LotCARS1 INNER JOIN LotCARS2 ON LotCARS1.CarColor = LotCARS2.CarColor;

The "AS" parts are renaming the fields from each field which have the same name in both tables, the system is putting the table name in front so that you can identify which came from which table.


I left all of the data fields in the query.  If you wanted to delete them you would open the query in design view and eliminate what you don't want to see.  You must retain at least one field.

There's no data so it's kind of hard to see it working, but if you enter a couple of test "cars" you should be able to see it work.
CARS-1-.mdb
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

828 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