Sorry for if it take too long-Here it is:
SELECT Sum(IIf(Not IsNull([dbo_2007].[specdate]),1,0)) AS Num_Test,
Sum(IIf([ctresult]="1",1,0)) AS PosC, Sum(IIf([gcresult]="1",1,0)) AS PosG,
Sum(IIf([race]="1",1,0)) AS race1, Sum(IIf([race]="2",1,0)) AS race2,
Sum(IIf([race]="3",1,0)) AS race3, Sum(IIf([race]="4",1,0)) AS race4,
Sum(IIf([race]="8",1,0)) AS race5, Sum(IIf([race]="9",1,0)) AS race6,
Sum(IIf(( (race='5')),1,0)) AS race7,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]<10,1,0)) AS Mage1Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]<10,1,0)) AS Mage1Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]<10,1,0)) AS Mage1Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]<10,1,0)) AS Mage1Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]<10,1,0)) AS Mage1Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]<10,1,0)) AS Mage1Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]<10,1,0)) AS Mage1Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]<10,1,0)) AS Mage1Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]<10,1,0)) AS Mage1Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]<10,1,0)) AS Mage1Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]<10,1,0)) AS Fage1Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]<10,1,0)) AS Fage1Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]<10,1,0)) AS Fage1Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]<10,1,0)) AS Fage1Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]<10,1,0)) AS Fage1Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]<10,1,0)) AS Fage1Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]<10,1,0)) AS Fage1Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]<10,1,0)) AS Fage1Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]<10,1,0)) AS Fage1Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]<10,1,0)) AS Fage1Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]>=10 And [age]<15,1,0)) AS
Mage2Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]>=10 And [age]<15,1,0)) AS
Fage2Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]>=15 And [age]<20,1,0)) AS
Mage3Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]>=15 And [age]<20,1,0)) AS
Fage3Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]>=20 And [age]<25,1,0)) AS
Mage4Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]>=20 And [age]<25,1,0)) AS
Fage4Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]>=25 And [age]<30,1,0)) AS
Mage5Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]>=25 And [age]<30,1,0)) AS
Fage5Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]>=30 And [age]<>99,1,0)) AS
Mage6Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]>=30 And [age]<>99,1,0)) AS
Fage6Ct5,
Sum(IIf([Gcresult]="1" And [Sex]="1" And [age]=99,1,0)) AS Mage7Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="1" And [age]=99,1,0)) AS Mage7Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="1" And [age]=99,1,0)) AS Mage7Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="1" And [age]=99,1,0)) AS Mage7Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="1" And [age]=99,1,0)) AS Mage7Gc5,
Sum(IIf([Gcresult]="1" And [Sex]="2" And [age]=99,1,0)) AS Fage7Gc1,
Sum(IIf([Gcresult]="2" And [Sex]="2" And [age]=99,1,0)) AS Fage7Gc2,
Sum(IIf([Gcresult]="3" And [Sex]="2" And [age]=99,1,0)) AS Fage7Gc3,
Sum(IIf([Gcresult]="4" And [Sex]="2" And [age]=99,1,0)) AS Fage7Gc4,
Sum(IIf([Gcresult]="9" And [Sex]="2" And [age]=99,1,0)) AS Fage7Gc5,
Sum(IIf([Ctresult]="1" And [Sex]="1" And [age]=99,1,0)) AS Mage7Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="1" And [age]=99,1,0)) AS Mage7Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="1" And [age]=99,1,0)) AS Mage7Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="1" And [age]=99,1,0)) AS Mage7Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="1" And [age]=99,1,0)) AS Mage7Ct5,
Sum(IIf([Ctresult]="1" And [Sex]="2" And [age]=99,1,0)) AS Fage7Ct1,
Sum(IIf([Ctresult]="2" And [Sex]="2" And [age]=99,1,0)) AS Fage7Ct2,
Sum(IIf([Ctresult]="3" And [Sex]="2" And [age]=99,1,0)) AS Fage7Ct3,
Sum(IIf([Ctresult]="4" And [Sex]="2" And [age]=99,1,0)) AS Fage7Ct4,
Sum(IIf([Ctresult]="9" And [Sex]="2" And [age]=99,1,0)) AS Fage7Ct5
FROM dbo_2007
WHERE (((Right(Trim([DBO_2007].[specdate]),4))="2007"))
Jerry Whittle said:
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Hi,
I cannot figure out is how to - Please Help -Thank you.
I have 1 table 1 report and I want to write a query for that report but it's
too long then MS Access 2K cannot run it! (I don't know why?) But it run in
Access 2K3.
How can I cut that query into 2 part or 3 part ? then join it for 1 report?
MN
