Need Help: Issues With Permutations in Access

cnichols0021

New member
Local time
Today, 03:36
Joined
May 25, 2011
Messages
1
Hey everyone - This is my first post, hope I get some advice from it! I've just started using Microsoft Access DB within the last month for work and I'm learning very fast (I think), but I'm still trying to understand the language. So, I've been doing side projects to practice.

Here is my issue - I like to play cards (blackjack in particular). I've created multiple tables (just named CARD1, CARD2, CARD3 etc.) with the fields: CARD1_ID, CARD1_NAME AND CARD1_VALUE. I have 56 rows (four extra rows because Ace's can be played as 1 as well as 11). I'm trying to query all the possible permutations (order does not matter). The problem is the query returns ACE CLUB, JACK CLUB and JACK CLUB, ACE CLUB (see below).

Do I have the correct table design?

Do I need multiple tables or can I generate the query off one table?

Whats wrong with my code?

I'm taking it slow, meaning I'm looking at all possible 2 card combinations, the 3 card combinations and so on...But if someone knows how to combine these into one query I'd like to see the code for it.

Here is my SQL view:
SELECT
CARD1.CARD1_NAME,
CARD2.CARD2_NAME,
CARD1_VALUE+CARD2_VALUE AS [TOTAL VALUE]
FROM
CARD1,
CARD2
WHERE CARD1.CARD1_NAME<>CARD2.CARD2_NAME
AND
CARD1_VALUE+CARD2_VALUE="21"

Here is whats returning (sorry it didnt copy/paste like expected):
SINGLE DECKCARD1_NAMECARD2_NAMETOTAL VALUEACE CLUB10 CLUB21ACE HEART10 CLUB21ACE SPADE10 CLUB21ACE DIAMOND10 CLUB21ACE CLUBJACK CLUB21ACE HEARTJACK CLUB21ACE SPADEJACK CLUB21ACE DIAMONDJACK CLUB21ACE CLUBQUEEN CLUB21ACE HEARTQUEEN CLUB21ACE SPADEQUEEN CLUB21ACE DIAMONDQUEEN CLUB21ACE CLUBKING CLUB21ACE HEARTKING CLUB21ACE SPADEKING CLUB21ACE DIAMONDKING CLUB2110 CLUBACE CLUB21JACK CLUBACE CLUB21

THANKS!!!
 
To get all the permutations of 13 cards and four suits, Create one table named "Card" with a text field "Card"
Put in all cards. If you create a second table "Suit" with the text field "Suit", put in all the suits

Create this query: qryDeck
Code:
select s1.Suit, c2.Card from Suit s1, Card c2
This will give you a cartesian product of two tables (suit and cards) the result will be 4 x 13 = 52 records.

To get all permutations of a single deck of cards run this query:

Code:
select q1.*, q2.* from qryDeck q1, qryDeck q2 where q1.Suit & q1.Card <> q2.Suit & q2.Card

All from the top of my head. So don't know if it works.

No, they don't add up to 21. Was that what you wanted?

Enjoy!
 
Honestly, this is more of a programming problem than database problem. Doesn't mean you can't learn about databases from it though.

For example, from a database standpoint, your table structure is completely wrong--its not normalized. You shouldn't have multiple tables with the exact same fields much less the exact same data. Guus2005's structure is the proper way to go.

Also, I'm just reading between the lines, but it sounds like your ultimate goal is to generate all permutations of possible blackjack hands (i.e. AQ, 786, A22J3, etc). Because of the way Aces work your WHERE clause is going to be pretty complex (trying to decide which Aces are 10, which are 1) and will probably require a programming via a VBA function to do that.
 
... and here's the database. Just wanted to see if it worked.
You need more time figuring out what to do with more cards. Perhaps if you add a card with no value you can create just one (7-Card) query which calculates all permutations that equals 21.

Share & Enjoy!
 

Attachments

Users who are viewing this thread

Back
Top Bottom