Total on main from 1

[rudolfelizabeth]

I have a query in a main from members with the follow code

in the query ,
Total: (select sum(amount) from transactions2 where [Transactions2]![Number] = Forms![Members]![Number])

without the where clause it gives you a sum of amount for all the clients in the transaction2 table.

with the where clause it doesn't output anything
I want the value of the client that's on the sreen's total.
any Idea what's missing

I also joined the member table to the transactions2 table

 

[MazeWorX]

Total: DSum("[Amount]","Transactions2","[Number]=" & Forms![Members]![Number])

could give this a try. Not sure of the table layout you have or what else is included in your query

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[rudolfelizabeth]

Dear Mazeworkx

I have tried this code allready it gives me the following error missing operator in the query expression,
that is why I switch to the other.

And remember amateurs built the ark with the help of God that is why it did not sank and reached its destination,that is why we are still here, the Titanic with all its professionals didn't even reach its destination.
A huge difference.

 

[MazeWorX]

I tested this and it worked fine without any issues .. Post all of the sql for the query

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]

 

[rudolfelizabeth]

Here are the code
SELECT Members.Number, Members.FamilyName, Members.Firstname, Members.Birthdate, Members.Age, Members.Maritalstatus, Members.Socialsecurityno, Members.OfficialID, Members.Socialsecurityno, Members.Paymentvia, Members.Address, Members.Address2, Members.POBOX, Members.ZipCode, Members.PostCode, Members.Country, Members.Areacode, Members.City, Members.Region, Members.Atlaslocation, Members.Users, Members.Phone, Members.Pnone2, Members.Emailaddress, Members.Inscriptiondate, Members.Titleofcourtesy, Members.Creditcardname, Members.Creditcardnumber, Members.Bankers, Members.Notes, Members.Photo, Members.Url, Members.Contactperson, Members.Creditcardcv, Members.Title, Members.Datemodified, Members.Timemodified, Transactions2.Number, Transactions2.Amount, DSum("[Amount]","Transactions2","[Number]=" & [Forms]![Members]![Number]) AS Total
FROM Members INNER JOIN Transactions2 ON Members.Number = Transactions2.Number
GROUP BY Members.Number, Members.FamilyName, Members.Firstname, Members.Birthdate, Members.Age, Members.Maritalstatus, Members.Socialsecurityno, Members.OfficialID, Members.Socialsecurityno, Members.Paymentvia, Members.Address, Members.Address2, Members.POBOX, Members.ZipCode, Members.PostCode, Members.Country, Members.Areacode, Members.City, Members.Region, Members.Atlaslocation, Members.Users, Members.Phone, Members.Pnone2, Members.Emailaddress, Members.Inscriptiondate, Members.Titleofcourtesy, Members.Creditcardname, Members.Creditcardnumber, Members.Bankers, Members.Notes, Members.Photo, Members.Url, Members.Contactperson, Members.Creditcardcv, Members.Title, Members.Datemodified, Members.Timemodified, Transactions2.Number, Transactions2.Amount
ORDER BY Transactions2.Number;

 

[rudolfelizabeth]

The code must be enter in the form source code

DSum("[Amount]","Transactions2","[Number]=" & Forms![Members]![Number])
and it works not in the query

 

[rudolfelizabeth]

Mazeworx

Thank you

But how do you nz the amount if there are no transactions in the transaction file

 

[MazeWorX]

= Nz(DSum("[Amount]", "Transactions2", "[Number]=" & Forms![Members]![Number]), 0)

HTH M

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]