Solved Unwanted result when adding a calculated field (1 Viewer)

[hhag]

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:

• 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

(As you see I don’t use any field in the query from the tblContact. It’s just present due to the relationship perspective)

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

 

[theDBguy]

Hi. Try leaving the query without the calculated column and then create another query based on the first query and add the calculated column there.

 

[hhag]

Hmm....I tried this before without any success....but thanks to you theDBguy I managed to figure it out.
What to say? Thanks and the hat off!

 

[theDBguy]

Hmm....I tried this before without any success....but thanks to you theDBguy I managed to figure it out.
What to say? Thanks and the hat off!

Hi. Glad to hear you got it sorted out. Good luck with your project.