Access Query Help 1

[herkiefan]

Hi,

I am relatively new to Access. I work at a casino and I am trying to run a query on our slot machines that is not quite working. I will do my best to explain my problem and question. Please advise if this is the wrong forum also.

My query is not really complex. I want to find the SlotNumber, Style_ID, and StyleDesc of our active slot machines. We have 663 slot machines that are active. When I run the following query (in SQL view) I get all 663 slot machines:

SELECT dbo_CDS_SLOTMAST.SlotNumber, dbo_CDS_SLOTMAST.Style_ID
FROM dbo_BB_STYLE, dbo_CDS_SLOTMAST
GROUP BY dbo_CDS_SLOTMAST.SlotNumber, dbo_CDS_SLOTMAST.CurrentRevision, dbo_CDS_SLOTMAST.Active, dbo_CDS_SLOTMAST.Status_ID, dbo_CDS_SLOTMAST.Style_ID
HAVING (((dbo_CDS_SLOTMAST.CurrentRevision)="y") AND ((dbo_CDS_SLOTMAST.Active)="y") AND ((dbo_CDS_SLOTMAST.Status_ID)=1));

Current Revision = Y means it is the most current information for the slot machine.
Active = Y means that it has not been deactivated from our revenue tracking.
Status_ID = 1 means that it is on the floor.

When I link the BB_STYLE table to my CDS_SLOTMAST table via Style_ID and add the criteria of StyleDesc I lose 51 machines. The SQL view of this query is as follows:

SELECT dbo_CDS_SLOTMAST.SlotNumber, dbo_CDS_SLOTMAST.Style_ID, dbo_BB_STYLE.StyleDesc
FROM dbo_CDS_SLOTMAST INNER JOIN dbo_BB_STYLE ON dbo_CDS_SLOTMAST.Style_ID = dbo_BB_STYLE.Style_ID
GROUP BY dbo_CDS_SLOTMAST.SlotNumber, dbo_CDS_SLOTMAST.CurrentRevision, dbo_CDS_SLOTMAST.Active, dbo_CDS_SLOTMAST.Status_ID, dbo_CDS_SLOTMAST.Style_ID, dbo_BB_STYLE.StyleDesc
HAVING (((dbo_CDS_SLOTMAST.CurrentRevision)="y") AND ((dbo_CDS_SLOTMAST.Active)="y") AND ((dbo_CDS_SLOTMAST.Status_ID)=1));

I looked at the BB_STYLE table there are only 6 records and 3 fields:
Style_ID StyleDesc StyleBasic
1 Reel Slot R
2 Video Slot S
3 Video Poker P
4 Video Keno K
5 Multi Game M
6 Video Blackjack B

The slot machines that are dropped off have a 0 (zero) in the Style_ID field.

My question is this:

Is it possible to run a query that will return all records even if one of the criteria points to a table that does not contain that value? In other words, can I fix my query so that it will pull all of the records even the ones that have a zero for the Style_ID? My guess is that since the zero is not included in the BB_Style table, that it is not possible.

Your help is always appreciated.

Mike


“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

Replace INNER JOIN with LEFT JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[herkiefan]

Works like a charm PHV. You are awesome!

I have no idea how it works, but it does.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[PHV]

I have no idea how it works
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[herkiefan]

thanks again. I love the web.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein