Child ID Access

Hello,I am trying to construct an sql statement that will insert a new row in to the frequency table containing the ball number, count statement to count how many times a particular ball has been drawn as a main ball, count statement to count how many times a particular ball has been draw as the bonus ball.

I have two tables with the following schema

Table Name: DrawResults

Fields
——

Draw Number – Integer
Date – Date
FirstBall – Number
Secondball – Number
ThirdBall – Number
ForthBall – Number
FifthBall – Number
SixthBall – Number
BonusBall – Number

Table Name: Frequency
Fields
——

Ball Number – Number
AsMainBall – Number
AsBonusBall – Number

My SQL is
INSERT INTO Frequency (BallNumber, AsMainBall, AsBonusBall)Select 1, Count(*) FROM DrawResults Where (FirstBall = 1 or SecondBall = 1 or ThirdBall = 1 or ForthBall = 1 or FifthBall = 1 or SixthBall = 1), Count(*) FROM DrawResults Where BonusBall = 1)

But Access reports an error
"Number of query destination fields are not the same

"Please could someone tell me where I am going wrong.
Thanks

I’m assuming that there is a relatively small number of ball values and that a given ball can only be selected once for picks 1-6 but can also be picked as a bonus. I would define another table called Balls containing a single column of BallNumber with the domain of possible values.

INSERT INTO Frequency (BallNumber, AsMainBall, AsBonusBall) FROM
(SELECT BallNumber, SUM(Main), SUM(Bonus) FROM
(SELECT BallNumber, 1 AS Main, 0 AS Bonus FROM Balls, Draw
WHERE BallNumber = FirstBall OR BallNumber = SecondBall OR …
BallNumber = SixthBall
UNION
SELECT BallNumber, 0 AS Main, 1 AS Bonus FROM Balls, Draw
WHERE BallNumber = BonusBall)
GROUP BY BallNumber)

This entry was posted on Friday, February 12th, 2010 at 7:09 pm and is filed under access count. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.