Problem with In(SELECT...)

[Alan Armstrong]

I am trying to count the winning numbers in a line of lotto. If I use
expressions for each field like

Expr1: IIf([Numbers].[1] In (1,5,13,22,34,38,20),1,0)

that works fine, but if I try to take the contents inside the bracket from
another table with this SELECT statement

Expr1: IIf([Numbers].[1] In (SELECT [1] & Chr$(44) & [2] & Chr$(44) & [3] &
Chr$(44) & [4] & Chr$(44) & [5] & Chr$(44) & [6] & Chr$(44) & FROM
Winners,1,0)

that doesn't work. Both tables have the same data type (integer) and I've
tried using Val without success. The SELECT statement looks OK as a
standalone field.

Can someone help please?

Alan Armstrong

 

[Myrna Larson]

Your SELECT statement is the equivalent of writing

IIf([Numbers].[1] In ("1,5,13,22,34,38,20"), 1,0)

i.e. you are creating a single string, not a list of 7 numbers separated by
commas.

 

[Dale Fye]

Alan,

This goes back to your issue of not having your data normalized. To
normalize you Winners table, you probably need two fields, the
SelectionDate, and a WinningNumber field, and the primary key would be a
composite of both of these fields. If your Winners table was normalized you
could write your expression this like:

EXPR1: IIf([1] In (SELECT tbl_Winners.[WinningNumber] FROM tbl_Winners
Where [SelectDate] = Date()),1,0)

Although this still leaves you Numbers table denormalized, which should be
changed as well. I think your numbers table should contain fields for
TicketID, SelectDate (the date of the drawing), and NumberSelected.
Depending on which lottery you are tracking you might have 5, 6 or 7 records
in this table for each ticket sold).

Then, to identify the tickets which have won, you could write a query that
looks something like:

SELECT N.TicketID, COUNT(W.WinningNumber) as Matches
FROM tbl_Numbers N
Left JOIN tbl_Winners W
On N.SelectDate = W.SelectDate AND N.NumberSelected = W.WinningNumber
GROUP BY N.TicketID
HAVING Count(W.WinningNumber) = 6

Assuming that you need to match 6 numbers to be a winner.

Hope this helps.
Dale

 

[Alan Armstrong]

Thank you Dale and Myrna. I will keep working on it.

Alan

 

[Myrna Larson]

Check out Help re how to create and fill an array, and/or how to use the Array
statement.

 

[Alan Armstrong]

Thank you both again.

Once I understood Dale's structure - a 'Numbers' table with ten rows of
fields called [1] to [6] but a 'Winners' table having only one WinningNumber
field and a separate record for each of the 6 numbers drawn, everything fell
into place:

1st query (called InList):
SELECT IIf([Numbers].[1] In (SELECT Winners.[WinningNumber] FROM
Winners,1,0) AS 1st, IIf([Numbers].[2] ...etc
FROM Numbers;

2nd query:
SELECT [1st]+[2nd]+[3rd]+[4th]+[5th]+[6th] AS Hits
FROM InList;

Voila! No normalisation needed as all values will be renewed each week. All
I wanted was a quick screening to avoid tedious manual comparison.

Wonder how the lotto organisers do it? My guess is they would keep a
database of ticket ID's created during the payout calculation routine which
takes two or three hours after the draw.

I am following up your point about strings and arrays, Myrna. What I am
still puzzled about is Dale's use of inverse table structures - it works
like a charm but I'm hazy as to why. Is there a link either of you can give
me that explains it?

Alan