Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create a Select Query

Posted on 2011-09-18
12
Medium Priority
?
344 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

571 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