running sum

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?

"Bob sg" wrote in message
...
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

"BruceM" wrote:

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.

"Bob sg" wrote in message
...
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.

"John W. Vinson" wrote:

On Tue, 27 Jan 2009 05:32:02 -0800, Bob sg Bob

wrote:

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

John W. Vinson [MVP]

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.

"John Spencer" wrote:

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

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" wrote in message
news
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.

"John Spencer" wrote:

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

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" wrote:

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" wrote in message
news
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.

"John Spencer" wrote:

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

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

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

"John Spencer" wrote:

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

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" wrote in message
...
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" wrote:

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" wrote in message
news
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.

"John Spencer" wrote:

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

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.

"BruceM" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news 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.

"John Spencer" wrote:

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

Putting it in where? How?

"Bob sg" wrote in message
...
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.

"BruceM" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news 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.

"John Spencer" wrote:

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

I did have the missing bracket. I just didn't have it in this message. Sorry
about that. The Data Type in the table for the inv_cm# is text and this can
not be changed. My question is: How can I add a different letter after the
invoice number for each of the invoices that are listed more than one time in
my query.

Invoices
72680959
72680959
72680959
72690774
72690774
72690774
72690774
72690773
72690773

I need it to look like:
72680959a
72680959b
72680959c
72690774a
72690774b
72690774c
72690774d
72690773a
72690773b



"BruceM" wrote:

You're missing the beginning square bracket:
Chr([inv_cm#]+64)

Other than that, is inv_cm# an integer or long field in the query? Can you
select it as a field by itself? Also, Chr only works with numbers up to
255. I suggested it as a way to convert numbers 1-26 into letters.

Your original question was how to group by invoice number in a query, and
how to number within each group. If the question is now something else, be
specific.

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

"Bob sg" wrote:

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.

"BruceM" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
news 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.

"John Spencer" wrote:

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