Select by Dsum criteria

[amir0914]

Hi all,
I'm trying to populate a table field to ComboBox so that another column of the table is greater than sum of another table :

Table1 :

C_Code​	C_Amount​
A190​	2600​
A250​	3200​

Table2:

D_Code​	D_Amount​
A190​	150​
A190​	500​
A250​	3250​
A190​	400​

="Select C_Code from Table1 where C_Amount > Dsum("D_Amount","Table2","D_Code = '" & C_Code & "'")"

If SQL Select is ran, the ComboBox items must be "A190" because >> 2600 > 150 + 500 +400
I'm running in VBA, But my SQL Select is not working and gives error, Does someone know reason of the problem?

 

[Gasman]

And the error is?

 

[Minty]

What error are you getting - have you tried just using that expression in the query editor?

Alternatively, create a grouped query that sums the D_Code amounts and then use that as a source.

 

[amir0914]

Excuse me, I had forgotten,

 

[arnelgp]

C_Code must be defined somewhere, in order for it to work

dim dblValue as Double
dblValue = Nz(DSum("D_Amount", "Table2", "D_Code = '" & C_Code & "'"), 0)
pSQL = "Select C_Code From table1 where C_Amount > " & dblValue

 

[arnelgp]

pSQL = "Select C_Code From Table1 Left Join " & _
"(Select D_Code, Sum(D_Amount) As Expr1 From Table2 Group By D_Code) As T1 " & _
"On Table1.C_Code = T1.D_Code " & _
"Where Table1.C_Amount > T1.Expr1;"

 

[amir0914]

In this case, "dblValue" is fixed, while "dblValue" must be calculated for each C_Code.

 

[Pat Hartman]

It is very inefficient to do this using domain functions. NEVER use domain functions in a query or a loop when there is an alternative. The alternative in this case is to create a totals query of the second table. Then join the first table to the totals query and add the > criteria. Easy and efficient. You could use a sub query instead of a saved querydef. I don't because they are too hard to create/view using QBE.

 

[Cronk]

@amir0914 wrote in #1

Does someone know reason of the problem?

You have a string
pSQL= "Select C_Code from Table1 where C_Amount > Dsum("

with extra characters after it
.....D_Amount","Table2","D_Code = '" & C_Code & "'")"

That line will not compile.

In any case, Docmd.runsql will only work with executable queries, not a select query. Open a recordset instead.

 

[amir0914]

pSQL = "Select C_Code From Table1 Left Join " & _
"(Select D_Code, Sum(D_Amount) As Expr1 From Table2 Group By D_Code) As T1 " & _
"On Table1.C_Code = T1.D_Code " & _
"Where Table1.C_Amount > T1.Expr1;"

This worked for my file, great.

 

[amir0914]

@amir0914 wrote in #1


You have a string
pSQL= "Select C_Code from Table1 where C_Amount > Dsum("

with extra characters after it
.....D_Amount","Table2","D_Code = '" & C_Code & "'")"

That line will not compile.

In any case, Docmd.runsql will only work with executable queries, not a select query. Open a recordset instead.

Thank you, Yes your right, I don't nee to Docmd.runsql