|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 27th, 2009, 01:32 PM
posted to microsoft.public.access
|
|||
|
|||
running sum
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. 
|
|
#2
January 27th, 2009, 06:10 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
|
|
#4
January 27th, 2009, 06:57 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
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]
|
|
#5
January 27th, 2009, 07:09 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
|
|
#6
January 27th, 2009, 07:12 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
|
|
#7
January 27th, 2009, 07:16 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
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" 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]
|
|
#8
January 27th, 2009, 08:40 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
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. "Bob sg" wrote in message ... 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" 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]
|
|
#9
January 28th, 2009, 02:01 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
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]
|
|
#10
January 28th, 2009, 02:54 PM
posted to microsoft.public.access
|
|||
|
|||
|
running sum
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
