How to summarize data in MS Access

[anasttin]

Hi all,

I'm having trouble summarizing some data the way I want it and was hoping for some help. I'm really hoping to not have to dump the summarized data to a table before creating the summarized view if possible. I've played with expression, group by and average, but not having much luck.

This is my data...

Part No.	Start Price	Price Change
abc	6.19	0
abc	6.19	-0.02
abc	6.19	-0.64

and I want to summarize it to:

Part No.	Start Price	Price Change	End price	% change
abc	6.19	-0.66	5.53	-10.7%

If anyone can assist, I'd be very grateful.

Thanks

[pbaldy]

The first 3 I'd get with a totals query, summing the price change and using min or max on the start, presuming it's always the same. The other 2 I'd calculate on the form/report you're presenting this with.

[anasttin]

Thanks pbaldy.

Is there no way to do it in the query itself?

[pbaldy]

Probably. You'd use Expression I suspect, and something like

Max(StartPrice) + Sum(PriceChange)

but it can get tricky going too deep in the query. If you need it there post a sample db and I'll play with it.

[anasttin]

Thanks pbaldy.

I tried Max(StartPrice) + Sum(PriceChange) and I think its working.

Will have to do a bit more testing.

Appreciate your time.

[pbaldy]

Happy to help!