sum function in access 2010 query problem

[Panchovia]

I've tried to get a sum in access 2010 of my transaction table joined on my client table properties unique records

Payments:sum((([quantity*Price)))
The query result gave me 3 totals, for the three years of transactions in the transaction file.

Is it possible to get one sum of all the years in one total.

 

[dhookom]

Can you share your full SQL statement? I expect you are grouping by something you should not be grouping by.

Duane
Hook'D on Access
MS Access MVP

 

[Panchovia]

When you remove the groupby you get an error try to execute a query that does not incude the specified expression as part of an agregate

 

[Panchovia]

Correct,
quantity and price must be sum not groupby

 

[dhookom]

Panchovia,
Can we assume you have this question resolved?

Duane
Hook'D on Access
MS Access MVP

 

[Panchovia]

Hi Dhookom

thanks for your advice

now this query is use to print two clientinfo files one if there are transactions, this one works fine( the user will input clientnumber)

the other if there is no data print (clientinfo2)(on no data)
code I used in the onnodata

MsgBox "There are no transactions in the transactionsfile I will print clientinfo of the mainfile"
DoCmd.OpenReport "clientinfo2", acViewNormal, ",", acNormal

how do I pass the clientnumber supplied by the user to the onnodata code to the docmd.openreport

 

[dhookom]

The clientnumber should be on a control on an open form.

Duane
Hook'D on Access
MS Access MVP

 

[Panchovia]

Dhookom

is the following where clause code correct
If Me.Recordset.RecordCount = 0 Then
MsgBox "No data in transaction file"
DoCmd.OpenReport , acViewPreview, clientinfo3, where(Number) = (clientnumber)
End If

 

[dhookom]

Your OpenReport line has lots of issues. Search Help to find out the correct syntax which states:

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Docmd.OpenReport "Your Report Name", acViewPreview,,"Your Where Clause"

Duane
Hook'D on Access
MS Access MVP