Solved

Create a Select Query

Posted on 2011-09-18
12
330 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auto Filter in Combo Box 7 30
ms/access hyperlink/ftp 7 34
Alter an update query which rounds 7 29
how to link subforms ms/access VBA 6 26
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now