Total does not sum

[Ron_dK]

I have a table with a number of fields and made a query to get some sums/totals.
In the query I made three fields :
Gast : DSum("[aantal]","TbBedrag","[Discipline] like 'gast*'")
Elec : DSum("[aantal]","TbBedrag","[Discipline] like 'elect*'")
Pumps : DSum("[aantal]","TbBedrag","[Discipline] like 'cent*'")

The first gives me the sum of ‘aantal’ where the ‘Discipline’ is Gasturbines, the second is the sum of aantal in electronics, etc.
I made another field which is supposed to give me the total of the three forementioned fields : totaal: [gast]+[elec]+[pumps]. The output of this field however does not give me the actual total, but writes the numbers of ‘Gast’, ‘Elec’ and ‘Pumps’ after another, instead of totalling the three numbers.

Strange, why would the total not give me the sum of the three calculated fields ?

 

[TanisAgain]

Try changing the 3 fields 'Gast', 'Elec' and 'Pumps' to integer.

SELECT tbBedrag.AantalID, tbBedrag.Bedrag, tbBedrag.Gast, tbBedrag.Elec, tbBedrag.Pumps, tbBedrag.Discipline, Sum(([gast]+[elec]+[pumps])) AS Total
FROM tbBedrag
GROUP BY tbBedrag.AantalID, tbBedrag.Bedrag, tbBedrag.Gast, tbBedrag.Elec, tbBedrag.Pumps, tbBedrag.Discipline
HAVING (((tbBedrag.Discipline) Like "elec" & "*"));

 

[TanisAgain]

Sorry, should have said that if you don't want to change the data type, enclose the fields in brackets, like so.

Sum([gast])+([elec])+([pumps]) AS Total
FROM tbBedrag

 

[Brianwarnock]

I think that you cannot use the aliases to do this but must quote all of the formulae.

Brian

 

[Ron_dK]

Thanks guys but :

Sum([gast])+([elec])+([pumps]) or Sum([gast]+[elec]+[pumps]) does not work either.

Even more strange is that the last field in the query is a substraction:
Sum ([Elec]-[Gast]) and that gives the appropriate output of 103.

Any other pointers ?

Cheers, Ron

 

[Rich]

Why are you using DSum in a query when you can just use a TotalsQuery?

 

[TanisAgain]

I agree with Rich, why use DSUM? Incidentally, I have attached a small working DB. Have a look and see where the problems are. I cannot see why it doesn't work!

 

[Ron_dK]

I'm stumped !

I changed the fields gast and elec in Tanis' example to numbers ( long integer) and it works.

Why are you using DSum in a query when you can just use a TotalsQuery?

I started with this Dsum or sum in the first place, but agree that I should have used Totalsquery thing.
Learning every day

Thanks for the help

Ron

 

[neileg]

This thread has me puzzled. Looking at Ron's screenshot, you can see that [gast] [elec] & [pumps] are text fields. When you use the addition operator on text fields it simply concatenates the fields. So the behaviour is what you would expect. I don't understand why these fields aren't numeric.

Second point is one of my personal hates. If you use the syntax sum(a+b+c), then sum is completely redundant since the formula is identical in effect to a+b+c. So you are just typing three letters and a pair of brackets for no reason.

 

[Ron_dK]

Looking at Ron's screenshot, you can see that [gast] [elec] & [pumps] are text fields. When you use the addition operator on text fields it simply concatenates the fields. So the behaviour is what you would expect. I don't understand why these fields aren't numeric.

Neil, that's exactly where my confusion came from , the concatenate thing in stead of an appropriate sum figure.
Btw, the gast, elec and pumps fields initially were number fields and still it concatenated i.s.o summing.
As you can see the subtraction ( sub field) gave the appropriate figure.

Still don't understand why it didn't work in the first place.


Cheers, Ron

 

[Brianwarnock]

Hi Ron, why did you ignore my reply?
aliases can cause problems , there have been a number of posts recently with problems in aggregate and sort queries, as I said in the post you have to restate the formulae that created the alias field.

brian

 

[Brianwarnock]

Second point is one of my personal hates. If you use the syntax sum(a+b+c), then sum is completely redundant since the formula is identical in effect to a+b+c. So you are just typing three letters and a pair of brackets for no reason.

What's more it turns the query into a Totals query, with all the implications of that, which a+b+c isn't.

Brian

 

[Ron_dK]

Hi Ron, why did you ignore my reply?

Brian I didn't !
I'm still looking into this.

aliases can cause problems ..... you have to restate the formulae that created the alias field.

I'm afraid I don't quite get what you're saying. ( bear with me, I'm a simple dutch guy
How would I restate the formulae ?

 

[Brianwarnock]

It is possible that the word alias is wrong but it was used by somebody on another thread and as I am self taught i thought it was maybe correct, I'll call it the calculated field name, using them in subsequent controls in the same query can cause problems, if using the query grid it sometimes sorts it out and sometimes it doesn't.

You would end up with
Total: DSum("[aantal]","TbBedrag","[Discipline] like 'gast*'") + DSum("[aantal]","TbBedrag","[Discipline] like 'elect*'") + DSum("[aantal]","TbBedrag","[Discipline] like 'cent*'")

Brian

PS I hope this is not Double Dutch

I support Liverpool and today after the game Kuyt and Agger were interviewd and they were easier to understand than Carragher and Crouch

 

[RV]

I support Liverpool and today after the game Kuyt and Agger were interviewd

If you're referring to Dutch players, Agger is actually Danish

RV

 

[Brianwarnock]

If you're referring to Dutch players, Agger is actually Danish

RV

I know just the fact that neither were English but they were more comprehensible than the english players whom normally are interviewed.

Brian

 

[Ron_dK]

You would end up with
Total: DSum("[aantal]","TbBedrag","[Discipline] like 'gast*'") + DSum("[aantal]","TbBedrag","[Discipline] like 'elect*'") + DSum("[aantal]","TbBedrag","[Discipline] like 'cent*'")

Although this works ( Thanks Brian), I have played around with the totals query and came up with :

SELECT TbBedrag.Discipline, Sum(TbBedrag.aantal) AS SumOfaantal, Sum(TbBedrag.bedrag) AS SumOfbedrag
FROM TbBedrag
GROUP BY TbBedrag.Discipline
HAVING (((TbBedrag.Discipline) Like "gas*"));

This is exactly what I wanted and the output is consistant.

Again, thanks for all pointers gentlemen.