Solved

Create a Select Query

Posted on 2011-09-18
12
327 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

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)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

759 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

19 Experts available now in Live!

Get 1:1 Help Now