Running Totals in Access

[nerf13]

I've got a headache with running totals, I think I've got a special twist on the question. My data looks like the first table, and I'd like to create the running total in the second table, filling in gaps in between. I can think of various ugly ways to do this, can anyone recommend an efficient method?

[tt]
FISH MONTH NUMBER_PURCHASED
GOLDFISH 1 1
GOLDFISH 3 2
GOLDFISH 8 1
GOLDFISH 12 1
BLUEGILL 2 1
BLUEGILL 5 2
BLUEGILL 7 2

FISH MONTH TOTAL_PURCHASED
GOLDFISH 1 1
GOLDFISH 2 1
GOLDFISH 3 3
GOLDFISH 4 3
GOLDFISH 5 3
GOLDFISH 6 3
GOLDFISH 7 3
GOLDFISH 8 4
GOLDFISH 9 4
GOLDFISH 10 4
GOLDFISH 11 4
GOLDFISH 12 5
BLUEGILL 1 0
BLUEGILL 2 1
BLUEGILL 3 1
BLUEGILL 4 1
BLUEGILL 5 3
BLUEGILL 6 3
BLUEGILL 7 5
[/tt]

Thanks!

nerf13

 

[plantj]

Every solution is ugly in Access

This is an SQL 7 solution you could adapt (I don't have Access, sorry)

create table month(
i int
)
go

insert into month values(1)
insert into month values(2)
insert into month values(3)
insert into month values(4)
insert into month values(5)
insert into month values(6)
insert into month values(7)
insert into month values(8)
insert into month values(9)
insert into month values(10)
insert into month values(11)
insert into month values(12)

select
f.fish
,m.i month
,IsNull(sum(s.number_purchased),0) number_purchased
from
(select distinct
fish
from
sales
) f
cross join integers m
left join sales s
on m.i>=s.Month
and f.fish=s.fish
group by
f.fish
,m.i
order by
f.fish
,m.i

 

[nerf13]

Thanks plantj!!!!!

You're absolutely right. It took me a couple hours to get the equivalent into Access (my Access equivalent is pretty ugly still...)

It really was the logic you used that helped the most. Once I got it deciphered, it was just the tedium of dragging Access along to implement it.

nerf13

 

[bluedragon2]

Just wondering:

Is there a reason you need two tables?

Doing a query on Table one will give you the results you are looking for.

Blue

 

[plantj]

bluedragon2, sorry about the delayed response. I believe 2 tables are required because otherwise if there are no sales for a particular month there wouldn't be any output for that month. But you are correct in that all the "real" data is in the existing database.