Hi,
I’m trying to add a calculated field in a group by query and I don’t get the result I want.
I’ve three tables:
I’ve the following two one-to-many relationships:
I use the following table fields:
I’m aiming to get the following fields in the datasheet view from the query:
Customer / TotalSum(currency) / GM(currency) / GM%(Percent)
The GM% field is the calculated one: GM%: Format([tblTender].[GM]/[tblTender].[TotSum];"Percent")
In the query I set the Customer filed to ‘Group By’, and the other two ‘Sum’. And then I observe that the calculated field also get ‘Group by’. I can delete the latter but then I get an error message.
The query seems to work just perfect when I run it without the calculated field. It’s when I’m adding the calculated field the result is ‘freaking out’. Without the calculated field I get the same customer once (which is the result I want), with the calculated field I get the same customer several times. (It has to do with Contacts of course… but there must be a way to adjust this in the SQL expression I guess)
Rgds
I’m trying to add a calculated field in a group by query and I don’t get the result I want.
I’ve three tables:
- tblCustomer
- tblContact
- tblTender.
I’ve the following two one-to-many relationships:
- tblCustomer (one) – tblContact (Many)
- tblContact (one) – tblTender (Many)
I use the following table fields:
- Customer=tblCustomer.CustomerName
- TotalSum=tblTender.TotalSum
- GM=tblTender.GM
I’m aiming to get the following fields in the datasheet view from the query:
Customer / TotalSum(currency) / GM(currency) / GM%(Percent)
The GM% field is the calculated one: GM%: Format([tblTender].[GM]/[tblTender].[TotSum];"Percent")
In the query I set the Customer filed to ‘Group By’, and the other two ‘Sum’. And then I observe that the calculated field also get ‘Group by’. I can delete the latter but then I get an error message.
The query seems to work just perfect when I run it without the calculated field. It’s when I’m adding the calculated field the result is ‘freaking out’. Without the calculated field I get the same customer once (which is the result I want), with the calculated field I get the same customer several times. (It has to do with Contacts of course… but there must be a way to adjust this in the SQL expression I guess)
Rgds