Running Sum in a Query?

S

Sarah

I have an append query which appends to a table with a field called item. In
the item field I would like to append a running sum, meaning the item field
for record one would have the value 1, record 2 would have the value 2 in its
item field, record 3 would have the value 3 in its item field and so on. Is
there any way of doing this in a query or do I have to do it programmatically?

Thanks for any help!

Sarah
 
J

jahoobob via AccessMonster.com

In a word, DON'T! First off, a table will not always store records in the
order that you think they should be and secondly, what hapens if one record
is deleted? You're running sum is out the window.
Do calcualtions in queries, forms, and reports but not in a table.
For what you want, make the item field in your table an autonumber. It will
accomplish exactly what you are seeking without the bother of going through
an append query EVERY time a new record is added.
 
S

Sarah

The reason why I don't want to use the autonumber is because if a record is
deleted, the autonumber uses the next available number for a new record
instead of re-using a number which has been deleted. For instance:

Record# Item #
1 1
2 2
3 3

Record number 2 is deleted and a new record is added, using autonumber I
would get:

Record# Item #
1 1
2 3
3 4

Instead I want the item # to read this way:

Record# Item #
1 1
2 2
3 3

I don't care about the order.
 
B

BruceM

For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The form will load more quickly (a consideration if there are lots of
records) if you use a similar approach in the form's Current event"

If Me.NewRecord Then
Me.YourField = DMax("YourField", "tblYourTable") + 1
End If

If your field or table name contains spaces, enclose the name in square
brackets (e.g. "[Your Field]").

However, if you have records 1, 2, and 3, and then delete record 2, the next
record will be 4 (because the highest-numbered record is 3). If you just
want the records to be numbered, you can set the control source of a textbox
on a report (not a form, just on a report) to =1, and set the running sum
property to Over All.
 
S

Sarah

I don't want to do this in a form or a report. I want to be able to do this
in a query.

Any other ideas?

BruceM said:
For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The form will load more quickly (a consideration if there are lots of
records) if you use a similar approach in the form's Current event"

If Me.NewRecord Then
Me.YourField = DMax("YourField", "tblYourTable") + 1
End If

If your field or table name contains spaces, enclose the name in square
brackets (e.g. "[Your Field]").

However, if you have records 1, 2, and 3, and then delete record 2, the next
record will be 4 (because the highest-numbered record is 3). If you just
want the records to be numbered, you can set the control source of a textbox
on a report (not a form, just on a report) to =1, and set the running sum
property to Over All.

Sarah said:
The reason why I don't want to use the autonumber is because if a record
is
deleted, the autonumber uses the next available number for a new record
instead of re-using a number which has been deleted. For instance:

Record# Item #
1 1
2 2
3 3

Record number 2 is deleted and a new record is added, using autonumber I
would get:

Record# Item #
1 1
2 3
3 4

Instead I want the item # to read this way:

Record# Item #
1 1
2 2
3 3

I don't care about the order.
 
B

BruceM

Do what exactly? Do you need to fill in the gaps if you delete a record?
Why do you so particularly want to do this in a query?

Sarah said:
I don't want to do this in a form or a report. I want to be able to do
this
in a query.

Any other ideas?

BruceM said:
For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The form will load more quickly (a consideration if there are lots of
records) if you use a similar approach in the form's Current event"

If Me.NewRecord Then
Me.YourField = DMax("YourField", "tblYourTable") + 1
End If

If your field or table name contains spaces, enclose the name in square
brackets (e.g. "[Your Field]").

However, if you have records 1, 2, and 3, and then delete record 2, the
next
record will be 4 (because the highest-numbered record is 3). If you just
want the records to be numbered, you can set the control source of a
textbox
on a report (not a form, just on a report) to =1, and set the running sum
property to Over All.

Sarah said:
The reason why I don't want to use the autonumber is because if a
record
is
deleted, the autonumber uses the next available number for a new record
instead of re-using a number which has been deleted. For instance:

Record# Item #
1 1
2 2
3 3

Record number 2 is deleted and a new record is added, using autonumber
I
would get:

Record# Item #
1 1
2 3
3 4

Instead I want the item # to read this way:

Record# Item #
1 1
2 2
3 3

I don't care about the order.

:

In a word, DON'T! First off, a table will not always store records in
the
order that you think they should be and secondly, what hapens if one
record
is deleted? You're running sum is out the window.
Do calcualtions in queries, forms, and reports but not in a table.
For what you want, make the item field in your table an autonumber.
It
will
accomplish exactly what you are seeking without the bother of going
through
an append query EVERY time a new record is added.

Sarah wrote:
I have an append query which appends to a table with a field called
item. In
the item field I would like to append a running sum, meaning the item
field
for record one would have the value 1, record 2 would have the value
2
in its
item field, record 3 would have the value 3 in its item field and so
on.
Is
there any way of doing this in a query or do I have to do it
programmatically?

Thanks for any help!

Sarah
 
J

jahoobob via AccessMonster.com

The easy way to do this is to have the Autonumber field in the table. After
you add a record, open the table in design view, delete the autonumber field,
and then insert it right back. You'll have every record numbered
sequentially!
I don't want to do this in a form or a report. I want to be able to do this
in a query.

Any other ideas?
[quoted text clipped - 67 lines]
 
S

Sarah

This was my original question:

I have an append query which appends to a table with a field called
item. In the item field I would like to append a running sum, meaning the
item
field for record one would have the value 1, record 2 would have the value
2 in its item field, record 3 would have the value 3 in its item field and
so on.
Is there any way of doing this in a query or do I have to do it
programmatically?


BruceM said:
Do what exactly? Do you need to fill in the gaps if you delete a record?
Why do you so particularly want to do this in a query?

Sarah said:
I don't want to do this in a form or a report. I want to be able to do
this
in a query.

Any other ideas?

BruceM said:
For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The form will load more quickly (a consideration if there are lots of
records) if you use a similar approach in the form's Current event"

If Me.NewRecord Then
Me.YourField = DMax("YourField", "tblYourTable") + 1
End If

If your field or table name contains spaces, enclose the name in square
brackets (e.g. "[Your Field]").

However, if you have records 1, 2, and 3, and then delete record 2, the
next
record will be 4 (because the highest-numbered record is 3). If you just
want the records to be numbered, you can set the control source of a
textbox
on a report (not a form, just on a report) to =1, and set the running sum
property to Over All.

The reason why I don't want to use the autonumber is because if a
record
is
deleted, the autonumber uses the next available number for a new record
instead of re-using a number which has been deleted. For instance:

Record# Item #
1 1
2 2
3 3

Record number 2 is deleted and a new record is added, using autonumber
I
would get:

Record# Item #
1 1
2 3
3 4

Instead I want the item # to read this way:

Record# Item #
1 1
2 2
3 3

I don't care about the order.

:

In a word, DON'T! First off, a table will not always store records in
the
order that you think they should be and secondly, what hapens if one
record
is deleted? You're running sum is out the window.
Do calcualtions in queries, forms, and reports but not in a table.
For what you want, make the item field in your table an autonumber.
It
will
accomplish exactly what you are seeking without the bother of going
through
an append query EVERY time a new record is added.

Sarah wrote:
I have an append query which appends to a table with a field called
item. In
the item field I would like to append a running sum, meaning the item
field
for record one would have the value 1, record 2 would have the value
2
in its
item field, record 3 would have the value 3 in its item field and so
on.
Is
there any way of doing this in a query or do I have to do it
programmatically?

Thanks for any help!

Sarah
 
B

BruceM

You need to contrive a way of getting the records into a predictable order.
An autonumber field would do the trick. Maybe there is another field you
could use (one without repeating values). You could define a field in your
query as 1 (NewField: 1) and then use DSum in another calculated field:
RunningTotal: DSum("[NewField]","[qryYourQuery]","[AutonumberField] <=" &
[AutonumberField])
You should know that you are not storing the number. You are getting what
you said you want: a query with a field that will show 1, 2, 3, etc. in
successive records. Since you don't care about the order, and since the
number is meaningless, there is no reason to store it.

Sarah said:
This was my original question:

I have an append query which appends to a table with a field called
item. In the item field I would like to append a running sum, meaning the
item
field for record one would have the value 1, record 2 would have the value
2 in its item field, record 3 would have the value 3 in its item field and
so on.
Is there any way of doing this in a query or do I have to do it
programmatically?


BruceM said:
Do what exactly? Do you need to fill in the gaps if you delete a record?
Why do you so particularly want to do this in a query?

Sarah said:
I don't want to do this in a form or a report. I want to be able to do
this
in a query.

Any other ideas?

:

For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
The form will load more quickly (a consideration if there are lots of
records) if you use a similar approach in the form's Current event"

If Me.NewRecord Then
Me.YourField = DMax("YourField", "tblYourTable") + 1
End If

If your field or table name contains spaces, enclose the name in
square
brackets (e.g. "[Your Field]").

However, if you have records 1, 2, and 3, and then delete record 2,
the
next
record will be 4 (because the highest-numbered record is 3). If you
just
want the records to be numbered, you can set the control source of a
textbox
on a report (not a form, just on a report) to =1, and set the running
sum
property to Over All.

The reason why I don't want to use the autonumber is because if a
record
is
deleted, the autonumber uses the next available number for a new
record
instead of re-using a number which has been deleted. For instance:

Record# Item #
1 1
2 2
3 3

Record number 2 is deleted and a new record is added, using
autonumber
I
would get:

Record# Item #
1 1
2 3
3 4

Instead I want the item # to read this way:

Record# Item #
1 1
2 2
3 3

I don't care about the order.

:

In a word, DON'T! First off, a table will not always store records
in
the
order that you think they should be and secondly, what hapens if
one
record
is deleted? You're running sum is out the window.
Do calcualtions in queries, forms, and reports but not in a table.
For what you want, make the item field in your table an autonumber.
It
will
accomplish exactly what you are seeking without the bother of going
through
an append query EVERY time a new record is added.

Sarah wrote:
I have an append query which appends to a table with a field
called
item. In
the item field I would like to append a running sum, meaning the
item
field
for record one would have the value 1, record 2 would have the
value
2
in its
item field, record 3 would have the value 3 in its item field and
so
on.
Is
there any way of doing this in a query or do I have to do it
programmatically?

Thanks for any help!

Sarah
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top