Multiple Count queries within Insert statement

[Andy722]

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

 

[PWise]

Try

Select 1, Count(*) AsMainBall ,(Select Count(*) FROM DrawResults Where BonusBall = 1) AsBonusBall
FROM DrawResults Where 1 in (FirstBall,SecondBall ,ThirdBall,ForthBall, FifthBall ,SixthBall)

 

[BitZero]

Hi Andy

Try this:

create a query named qryUnion that looks like this:

SELECT firstball AS ball, Count(*) AS countmain, 0 AS countbonus
FROM DrawResults
GROUP BY firstball
UNION ALL 
SELECT bonusball AS ball, 0 AS countmain, count(*) AS countbonus
FROM DrawResults
GROUP BY bonusball;

create a query named qryFreq that looks like this:

SELECT ball, sum(countmain) AS cntmain, sum(countbonus) AS cntbonus
FROM qryUnion
GROUP BY ball;

then your insert statement will look like this:

INSERT INTO frequency ( ballnumber, asmainball, asbonusball )
SELECT ball, cntmain, cntbonus
FROM qryFreq;