Solved

Create a Select Query

Posted on 2011-09-18
12
337 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
[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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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