Microsoft Access -- Updating a Field with "Cumulative" Data 1

[BizDev]

Hey everybody, first time poster here.

Here's my situation. I have a table that basically looks like this...

Field Names:

Item
Date
Qty1
Qty2
Net
Cumulative

There is accurate data in all of the fields, except for cumulative. I have the data sorted ascending by Item, then ascending by date.

What I am trying to do is basically this:

If [Item] != (prior record)[Item], [Net], [Net]+(prior record)[Net]

Does that make sense? Basically, I want to plot out what the net qtys will be, over a course of time. Should I use visual basic for this? Write a query? I'm by no means a programmer and was wondering if anyone could help. I would greatly appreciate it.

 

[BizDev]

Yes, they are denormalized. This isn't a production database with dynamic data going in/out on a daily basis. It's more for reporting/analysis look at daily/weekly/monthly/quarterly/etc. snapshots in time.

I added in the aliases as you showed and it works like a charm. Thank you _so_ much.

If you'll indulge me, what is the point of the alias table name, it seems key to the sum calculation, but I can't wrap my mind around why.

 

[cmeagan656]

A star to Duane for sticking with the problem and providing the solution.

Hope this helps.

Please help us help you. Read Tek-Tips posting polices before posting.

 

[dhookom]

The alias is required because you are working with two references to the same table. When you alias the table, its fields and records can be queried separately.

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Very cool-- I did not know that, thank you.

With over 500,000 records, and needing to do 3 of these "cumes" in the same table (or recordset), I'm beginning to understand why calculated records shouldn't be stored (or created in the first place). This query hard on the cpu.

 

[dhookom]

Make sure the Period and Item Number fields are indexed. The same goes for any field that might be part of a JOIN or WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[BizDev]

Holy...

Yes. That made a _world_ of difference. Wow. You rock, Duane.