running sum

[Bob sg]

In Access is there a way to number the cells in a query so when I have an
invoice number that is referenced more than one time it will put a, b, c or
1, 2, 3 after the invoice number.

 

[John W. Vinson]

In Access is there a way to number the cells in a query so when I have an
invoice number that is referenced more than one time it will put a, b, c or
1, 2, 3 after the invoice number.

Access doesn't have "cells"...

This can be done with some hassle in a query, but it's really easy on a
Report. If the purpose is just to print it out, put a textbox on the report;
set its control source to =1 (the number one); and set its Running Sum
property to "Over Group" (if you're grouping by the invoice) or "Over All" (if
you want the numbers to keep incrementing).

If you need to do it on a Form or in a Query post back with more details about
the nature of your tables/queries.

 

[Bob sg]

I did find out that this can be done in a report, but I need this to be done
in a query. I have mulitiple columns in a query that I need to export to
excel. This is very hard to review the data in a report.

 

[BruceM]

Perhpas this information about ranking records will be of help:
http://allenbrowne.com/ranking.html

A Google groups search for "ranking" or "ranking query" may turn up more
information.

The numbers obtained for ranking can be changed to letters with the Chr
function. If the ranking column in a query produces 1, 2, and 3 for a
particular invoice, add 96 and wrap the Chr function around it. Chr(97)
will return "a", Chr(98) is "b", etc.

 

[hor vannara]

In Access is there a way to number the cells in a query so when I have an
invoice number that is referenced more than one time it will put a, b, c
or
1, 2, 3 after the invoice number.

 

[hor vannara]

In Access is there a way to number the cells in a query so when I have an
invoice number that is referenced more than one time it will put a, b, c
or
1, 2, 3 after the invoice number.

 

[Bob sg]

In a query, I have the following invoices:

353
353
353
354
354
126
126
126
126

Is there a way to put a "a" after the first invoice number and then "b" for
the next and so on. Then start over for the next series of invoice numbers.

 

[BruceM]

The simplest thing would be to generate the number in a field when the
record is created, and covert it using the Chr function as I described.

Failing that, you should be able to use a ranking query. The link I
provided may offer some insight, as would a Google goups search for: query
number group
or
"microsoft.public.access" query number group

This is borrowed from something John Spencer posted a while ago:

SELECT RankID, ColumnA, (SELECT Count(*) FROM Table1 as T
WHERE T.ColumnA = Table1.ColumnA
AND T.RankID >= Table1.RankID) as Rank
FROM Table1 ORDER BY Table1.ColumnA;

Use your table name in place of Table1, the field name in which the number
appears in place of ColumnA, and your primary key field for RankID. To use
lower case letter in place of numbers, wrap Chr around the second SELECT:

SELECT RankID, ColumnA, Chr((SELECT Count(*) FROM Table1 as T
WHERE T.ColumnA = Table1.ColumnA
AND T.RankID >= Table1.RankID) + 96) as Rank
FROM Table1 ORDER BY Table1.ColumnA;

This is very generic, but you have not provided much information about your
fields. Ordering and other details may vary.

In a query, I have the following invoices:

353
353
353
354
354
126
126
126
126

Is there a way to put a "a" after the first invoice number and then "b"
for
the next and so on. Then start over for the next series of invoice
numbers.

 

[Bob sg]

Could you show me how I should use the chr function in a query to put a
"A","B","C", etc. after the invoice number. There is no primary key in this
table just to let you know.

Invoice Number
72661020
72661020
72661020
72661020
72654525
72654525
72654525
72654525
72654525
72654525
72657230
72657230
72657230
72657230
72657230
72657229
72657229
72657229
72657229
72657229
72656832
72656832
72651114
72651114
72637774
72650641
72650641
72632697
72632697
72632697
72661021
72661021
72661021
72661021
72661021
72661021
72637148
72641699
72634593
72634593
72641699
72635400
72635400
72635400
72635400
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72661022
72661022
72661022
72661022
72661022
72661029
72661029
72661029
72653986
72654430
72654430
72661033
72661033
72661033
72661033
72661033
72636514
72636514
72661037
72661037
72661037
72661037
72661038
72661038
72661038
72661038
72651115
72651115
72651115
72651115
72651115
72651115
72650868
72650868
72650868
72650868
72650868
72650868

 

[John Spencer]

The only way to do this is manually, UNLESS you have some way to
distinguish the several records from each other.

Is there a datetime field for each of the invoice records that is
different for the group? Or some other way you can uniquely sort them in
order.

72661020
72661020
72661020
72661020

Or you could use VBA code to step through an ordered recordset to do
this. That would fix the current records, but would not take care of
records that would be added.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[BruceM]

I showed you how to use the Chr function in a query. If you want upper case
rather than the lower case you showed initially, add 64 rather than 96.

I provided a generic SQL statement because you provided so few details. Is
the sample data from a table or a query? Is there a unique constraint in
the table? That is, is there something unique about each record? Do the
records have anything in common other than the invoice number?

Could you show me how I should use the chr function in a query to put a
"A","B","C", etc. after the invoice number. There is no primary key in
this
table just to let you know.

Invoice Number
72661020
72661020
72661020
72661020
72654525
72654525
72654525
72654525
72654525
72654525
72657230
72657230
72657230
72657230
72657230
72657229
72657229
72657229
72657229
72657229
72656832
72656832
72651114
72651114
72637774
72650641
72650641
72632697
72632697
72632697
72661021
72661021
72661021
72661021
72661021
72661021
72637148
72641699
72634593
72634593
72641699
72635400
72635400
72635400
72635400
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72650918
72661022
72661022
72661022
72661022
72661022
72661029
72661029
72661029
72653986
72654430
72654430
72661033
72661033
72661033
72661033
72661033
72636514
72636514
72661037
72661037
72661037
72661037
72661038
72661038
72661038
72661038
72651115
72651115
72651115
72651115
72651115
72651115
72650868
72650868
72650868
72650868
72650868
72650868

 

[Bob sg]

Each record would have the same data, but I wouldnt be adding new data to the
queary. Do you know the VBA code I could use on this.

 

[BruceM]

I have provided SQL for a ranking query, and have asked several questions
about the data. You have given no indication you have made any attempt to
implement the suggestions, and you have not answered the questions. If each
record has the same data there is no basis for ranking. Even if you could
assign numbers the "ranking" would be arbitrary and meaningless.

 

[Bob sg]

I have a query that our system generates so we can invoice our customers.
Somtimes we bill mulitple lines because the customer wants a breakdown of the
material that we are billing for and sometimes the material is listed more
than once so the line item would be the same. I tried to do the Chr function,
but I'm not sure how to use it. And I'm not sure what a ranking query is.

 

[John Spencer]

COMPLETELY UNTESTED procedure follows. Use at your own risk.
Backup your data first in case this does not work the way you want it to.

You can only update the invoice numbers ONE TIME


Public Sub fAddLetterToInvoice()
Dim dbany As DAO.Database
Dim rstAny As DAO.Recordset
Dim strSQL As String
Dim sInvoiceNumber As String
Dim iChr As Integer

strSQL = "SELECT InvoiceNumber FROM Invoices ORDER BY InvoiceNumber"
Set dbany = CurrentDb()
Set rstAny = dbany.OpenRecordset(strSQL)

If rstAny.RecordCount > 0 Then
'Initial settings
sInvoiceNumber = rstAny!InvoiceNumber
iChr = 64
'Loop through all the records
While rstAny.EOF = False
rstAny.Edit
If rstAny!InvoiceNumber = sInvoiceNumber Then
iChr = iChr + 1
Else
iChr = 65
End If
sInvoiceNumber = rstAny!InvoiceNumber
rstAny!InvoiceNumber = rstAny!InvoiceNumber & Chr(iChr)
rstAny.Update
rstAny.MoveNext
Wend
End If

End Sub

 

[Bob sg]

I feel that we are getting there and I do appreciatte you help. I don't think
I gave all of the information that is needed. If I have multilple queries,
how do I make this work for that specific query (query number is SUNRISE).
And the invoice column within that query is called INV_CM#.

 

[BruceM]

Help has more information about the Chr function. Essentially, characters
may be identified by a number. To go to the immediate code window, open any
database and press Ctrl + G. In the Immediate window type:
?Chr(65)
The letter A will appear. Chr(66) = B, etc., then Chr(96) is a.
To see the code for a character, in the immediate window type:
?Asc("A")
This should return 65.
If you have a record with the number 1 in a field, Chr([SomeField] + 64)
will return the letter A. This is part of what John showed in his example
with the use of Chr(iChr).

A ranking query is one name for a query that starts with this:

1000
1000
1000
1006
1006
1010
1012
1012
1012

and returns this:
1000 1
1000 2
1000 3
1006 1
1006 2
1010 1
1012 1
1012 2
1012 3

It can be used for other types of sorting, such as arranging runners by
their race times and numbering the records starting with 1. One of the
assumptions with the example above is that there is a reason for numbering
the "1000" records 1, 2, and 3. What places them in that order in the
query? A date? A number field?

I will leave you to John's part of the thread since you indicated you are
getting close with his suggestion, which is quite different from mine.

 

[Bob sg]

I tried putting in Chr([inv_cm#]+65) and I got this error: #error. All my
invoices start with 6 or a 7. Not sure what I'm doing wrong.

Help has more information about the Chr function. Essentially, characters
may be identified by a number. To go to the immediate code window, open any
database and press Ctrl + G. In the Immediate window type:
?Chr(65)
The letter A will appear. Chr(66) = B, etc., then Chr(96) is a.
To see the code for a character, in the immediate window type:
?Asc("A")
This should return 65.
If you have a record with the number 1 in a field, Chr([SomeField] + 64)
will return the letter A. This is part of what John showed in his example
with the use of Chr(iChr).

A ranking query is one name for a query that starts with this:

1000
1000
1000
1006
1006
1010
1012
1012
1012

and returns this:
1000 1
1000 2
1000 3
1006 1
1006 2
1010 1
1012 1
1012 2
1012 3

It can be used for other types of sorting, such as arranging runners by
their race times and numbering the records starting with 1. One of the
assumptions with the example above is that there is a reason for numbering
the "1000" records 1, 2, and 3. What places them in that order in the
query? A date? A number field?

I will leave you to John's part of the thread since you indicated you are
getting close with his suggestion, which is quite different from mine.

I have a query that our system generates so we can invoice our customers.
Somtimes we bill mulitple lines because the customer wants a breakdown of
the
material that we are billing for and sometimes the material is listed more
than once so the line item would be the same. I tried to do the Chr
function,
but I'm not sure how to use it. And I'm not sure what a ranking query is.

 

[BruceM]

Putting it in where? How?

I tried putting in Chr([inv_cm#]+65) and I got this error: #error. All my
invoices start with 6 or a 7. Not sure what I'm doing wrong.

Help has more information about the Chr function. Essentially,
characters
may be identified by a number. To go to the immediate code window, open
any
database and press Ctrl + G. In the Immediate window type:
?Chr(65)
The letter A will appear. Chr(66) = B, etc., then Chr(96) is a.
To see the code for a character, in the immediate window type:
?Asc("A")
This should return 65.
If you have a record with the number 1 in a field, Chr([SomeField] + 64)
will return the letter A. This is part of what John showed in his
example
with the use of Chr(iChr).

A ranking query is one name for a query that starts with this:

1000
1000
1000
1006
1006
1010
1012
1012
1012

and returns this:
1000 1
1000 2
1000 3
1006 1
1006 2
1010 1
1012 1
1012 2
1012 3

It can be used for other types of sorting, such as arranging runners by
their race times and numbering the records starting with 1. One of the
assumptions with the example above is that there is a reason for
numbering
the "1000" records 1, 2, and 3. What places them in that order in the
query? A date? A number field?

I will leave you to John's part of the thread since you indicated you are
getting close with his suggestion, which is quite different from mine.

I have a query that our system generates so we can invoice our
customers.
Somtimes we bill mulitple lines because the customer wants a breakdown
of
the
material that we are billing for and sometimes the material is listed
more
than once so the line item would be the same. I tried to do the Chr
function,
but I'm not sure how to use it. And I'm not sure what a ranking query
is.

:

I have provided SQL for a ranking query, and have asked several
questions
about the data. You have given no indication you have made any
attempt
to
implement the suggestions, and you have not answered the questions.
If
each
record has the same data there is no basis for ranking. Even if you
could
assign numbers the "ranking" would be arbitrary and meaningless.

Each record would have the same data, but I wouldnt be adding new
data
to
the
queary. Do you know the VBA code I could use on this.

:

The only way to do this is manually, UNLESS you have some way to
distinguish the several records from each other.

Is there a datetime field for each of the invoice records that is
different for the group? Or some other way you can uniquely sort
them
in
order.

72661020
72661020
72661020
72661020

Or you could use VBA code to step through an ordered recordset to
do
this. That would fix the current records, but would not take care
of
records that would be added.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Bob sg wrote:
Could you show me how I should use the chr function in a query to
put a
"A","B","C", etc. after the invoice number. There is no primary
key
in
this
table just to let you know.

Invoice Number
72661020
72661020
72661020
72661020

 

[Bob sg]

In the design view in the query, I'm putting the Chr(inv_cm#]+65) in the
field box.

I tried putting in Chr([inv_cm#]+65) and I got this error: #error. All my
invoices start with 6 or a 7. Not sure what I'm doing wrong.

Help has more information about the Chr function. Essentially, characters
may be identified by a number. To go to the immediate code window, open any
database and press Ctrl + G. In the Immediate window type:
?Chr(65)
The letter A will appear. Chr(66) = B, etc., then Chr(96) is a.
To see the code for a character, in the immediate window type:
?Asc("A")
This should return 65.
If you have a record with the number 1 in a field, Chr([SomeField] + 64)
will return the letter A. This is part of what John showed in his example
with the use of Chr(iChr).

A ranking query is one name for a query that starts with this:

1000
1000
1000
1006
1006
1010
1012
1012
1012

and returns this:
1000 1
1000 2
1000 3
1006 1
1006 2
1010 1
1012 1
1012 2
1012 3

It can be used for other types of sorting, such as arranging runners by
their race times and numbering the records starting with 1. One of the
assumptions with the example above is that there is a reason for numbering
the "1000" records 1, 2, and 3. What places them in that order in the
query? A date? A number field?

I will leave you to John's part of the thread since you indicated you are
getting close with his suggestion, which is quite different from mine.

I have a query that our system generates so we can invoice our customers.
Somtimes we bill mulitple lines because the customer wants a breakdown of
the
material that we are billing for and sometimes the material is listed more
than once so the line item would be the same. I tried to do the Chr
function,
but I'm not sure how to use it. And I'm not sure what a ranking query is.

:

I have provided SQL for a ranking query, and have asked several questions
about the data. You have given no indication you have made any attempt
to
implement the suggestions, and you have not answered the questions. If
each
record has the same data there is no basis for ranking. Even if you
could
assign numbers the "ranking" would be arbitrary and meaningless.

Each record would have the same data, but I wouldnt be adding new data
to
the
queary. Do you know the VBA code I could use on this.

:

The only way to do this is manually, UNLESS you have some way to
distinguish the several records from each other.

Is there a datetime field for each of the invoice records that is
different for the group? Or some other way you can uniquely sort them
in
order.

72661020
72661020
72661020
72661020

Or you could use VBA code to step through an ordered recordset to do
this. That would fix the current records, but would not take care of
records that would be added.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Bob sg wrote:
Could you show me how I should use the chr function in a query to
put a
"A","B","C", etc. after the invoice number. There is no primary key
in
this
table just to let you know.

Invoice Number
72661020
72661020
72661020
72661020