Running Sum in a Query?

[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

 

[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.

 

[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.

 

[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.

 

[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?

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.

 

[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?

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

 

[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?

For one approach to incremented numbers, see here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

[quoted text clipped - 67 lines]

 

[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?

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?

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

 

[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.

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?

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?

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