SQL help!!! ...working in code

[jammerdk]

Hi I'm trying to figure out how to calculate certain fields in SQL but I'm running against problems everytime got it working in code on a single form but I want it to in a continuous form.

the form is based on a query Counting ID and Grouped by Type

my code looks something like this

Me.TotalSum = DCount("ID", "tblUnimec", "Type='" & Cbotype & "'")
Me.Sum1 = DCount("[Good]", "tblUnimec", "[Good] = 1 And type='" & Cbotype & "'")
Me.Sum2 = DCount("[Good]", "tblUnimec", "[Good] = 2 And type='" & Cbotype & "'")
Me.Pro1 = (100 / Me.TotalSum) * Me.Sum1 & " %"
Me.Pro2 = (100 / Me.TotalSum) * Me.Sum2 & " %"

Hope some of u guys could help me on transforming it to SQL

 

[mp9]

If it helps, the easiest SQL equivalent of a DCount like this

DCount("ID", "tblUnimec", "Type='" & Cbotype & "'")


Is this

"SELECT Count(*) FROM tblUnimec WHERE Type='" & Cbotype & "';"

 

[jammerdk]

Tried what u described only got a field containing Expr..and no data

 

[PHV]

And what about a query like this (SQL code) ?
SELECT ID, type, Count(*) AS TotalSum
, Sum(IIf([Good]=1,1,0)) As Sum1
, Sum(IIf([Good]=2,1,0)) As Sum2
, Sum(IIf([Good]=1,1,0))*(Count(*)/100) As Pro1
, Sum(IIf([Good]=2,1,0))*(Count(*)/100) As Pro2
FROM tblUnimec
GROUP BY ID, type

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jammerdk]

thanx PHV just what I needed to get further into SQL ...solved it now.

Ps. have a star