Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Numbering Records In Query

Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.

I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.

The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers

IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:

SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);

What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:

tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311

I would like to do something like this:

tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1

Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....

Any help would be appreciated

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Sequentially Numbering The Records In A Query
I need to add a column to a sorted query which effectively numbers from 1 to N. It is intended as a ranking field for later statistical analysis.

I can do this manually by saving the query as a table, then introducing a new autonumber field.

However, I need to do this automatically, as this is just one query out of many in a large and complex setup. Is it possible to add an autonumber field to a query?

View Replies !   View Related
Numbering Records
Hi I obviously have a table with records in it sorted in assending order. What I need is to know how to make it add into one of the table fields the number it is in the table. IE

Name Time Position Number
Mr Jones - 12:30 - 1
Mr Evans - 12:45 - 2

So if i added 12:35 it would change mr evans position number to 3 and make the new record position number 2.

View Replies !   View Related
Numbering The Subform's Records
Dear All,

Maybe my question is not too difficult.

I have a form-subform structure and my aim is to number certain records of the subform. These records would contain value: 1,2,...
And when changing the record on the main form, the subform's record should have the values 1,2,... again.
(These values are shown on the bottom of the subform, where we can step the subform's record.)

If I use an autonumber field, the numbering goes continously.

Please help me!

Thanks you in advance.


View Replies !   View Related
How To Update Records In A Form W/auto Numbering
Is there a way I could insert a record in a continuous form and have the numbering field updates itself? I currently have the CounterID set to(number) instead of auto number. (e.g.) If I want to insert data in row # 200, I want the original data in row 200 update to row 201 (just like in excel).

Can this be done?

Thanks. :confused:

View Replies !   View Related
Numbering In A Query
I want to distribute certain materials (found in a query) to several warehouse locations in a sequential order.

So the first found material needs to be put in warehouse location 001,
the second found material needs to be put in warehouse location 002,

How can I number the lines in a query?

I donít want to run a create-table-query with an auto number field because the warehouse locations are limited and every time the query runs the numbering has to start at 001.

View Replies !   View Related
Numbering In An Query
I would like to create an expression that would number each item in sequential order in a query .
I cannot use autonumber on the table because I am pulling it from SQL and do not have clearance to change the actual tables.
I have been searching through this forum for the last 2 hours and have not come across anything similar to this, so I hope I'm not covering a topic that has already been covered.

Any help will be greatly appreciated.


View Replies !   View Related
Sequential Numbering Of Query
I need to generate line numbers for an access query. This query is run via VB code. After it runs, a Dlookup is performed to search for the line number of a particular order detail id. When it finds it, it puts the line number along with other unique order detail information into a text file to be used by a computerized saw that cuts the parts out. Here's a sample query:

DetailID SortID Frmlgnth Stilelgnth OrderID SeqNo

The query is supposed to be sorted with SortID Descending, Frmlgth Ascending, Stilelgth Ascending - in that order. The sort is working fine. However, the Sequential numbering is not. The OrderdetailID has NO bearing on the sort order. Here is my SQL code:

SELECT OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID, (SELECT count(*) FROM OrderDetails As x WHERE x.orderdetailid <= OrderDetails.orderdetailid AND x.orderid = orderdetails.orderid) AS SeqNo
FROM OrderDetails INNER JOIN ProductTypes ON OrderDetails.ProductType = ProductTypes.ProductType
GROUP BY OrderDetails.OrderDetailID, ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual, OrderDetails.FrameRailLengthActual, OrderDetails.OrderID
HAVING (((OrderDetails.OrderID)=[forms]![orders]![orderid]))
ORDER BY ProductTypes.ProductTypeSortOrder, OrderDetails.FrameStileLengthActual DESC , OrderDetails.FrameRailLengthActual DESC;

Does anyone have any suggestions? I'm not too good with SQL, but I can figure it out (I think!). I've searched all the formus, but all the reference I can find regarding this don't work for me in my application...

I greatly appreciate any help you can give!

View Replies !   View Related
Numbering Lines In A Query
How do I number the lines in a query? I have a 'Top 50' query, and I want to number the lines 1 through 50.

View Replies !   View Related
Auto Numbering A Select Query -
Can this be done ??

I have a query on a timer that basically looks at a linked table every minute and tells me whose in the database. I want to number the rows in a column so I can reference this to a form that has 7 spaces. The number of 'users' could vary from 0 - 23, although this will soon be limited to 7 (hence the 7 spaces) to aid performance and speed. I am then going to reference the number to the unbound space on the form, iif(isnull()) 'ing the blank cells so it isnt an error.

I thought of an append query, but the sequence will always start at the next number, not from 1 each time - or as far as im aware it will - so I cant reference the cell then.

Can any one help please.

View Replies !   View Related
Adding Sequential Numbering To A Query

Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.

I've done some searches for sequential numbering but couldnt find anything so far.

Thanks, j

View Replies !   View Related

I have one main table containing most data, that table stores data which is identifies by a unique ID (currently 1-96000), there is also an issue number, I need another numbering system per issue (so you could have issue 166, record 26), any ideas?

View Replies !   View Related
Is it possible to do the following?

I have a column with a heading EventNo.
The Event number must be 06-00000 The first 2 digits is the year.

In the Input Mast I have - 00/-00000 and the Default value set as 06-00000

How can I set it up so it can go to 07-00000,08-00000 and so on for next years? The Event number must start at 00001 every year.

It will be impossible to change the default value every year.

View Replies !   View Related
Sequential Numbering (HELP)
I have a table (tbl_Forms) which contains the following columns
ID, Form Number, Descrip, Next Number to Print

First Issue.
I have a form that based on the above table and includes an unbound text box labeled forms requested.
I need the next number to print field to increment by the number put into the forms requested text box. I also need this value to be stored in the tbl_forms table once the printing is complete. So that the next time the form is printed this number is show.

Second Issue.
I need the sequential numbers to print of the bottom of the pages.

I need some serious help

Thanks in advance

View Replies !   View Related
Numbering 00001
Is there a way I can number records like 00001 , 00002 ...... 00100 .. . .. ..

I want to alot batch nombers to production lots . so the batch number should look like 000001 and not just 1.

View Replies !   View Related
Sequential Numbering
Shall be grateful for clarifications for these two :

1. I have two fields - TheYear (set to take the current year) and another for sequential numbering. In fact I created this to replace the autonumber field. As suggested by a member I created a Generate button with the criteria as under

Private Sub Generate_Click()
If IsNull(Me![NumFld]) Then
Me![NumFld] = Format(Nz(DMax("[NumFld]", "[DiaryTable]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000")
End If
Me![NumFld] = Format([NumFld], "00000")

End Sub

It works fine. and when the year is changed, the numbering starts from 1 again. My question is how do I make it to auto generate the number so that the user doesnt have to click the Generate button everytime to get the number. Suggestions please.

2. Is it possible to change this sequential numbering midway i.e. to start from a different number and increment by one?

Grateful for help

View Replies !   View Related
Auto Numbering....
We have a part numbering system that is currently like this, 11Y22 where 22 increments by one until it gets to 99 where it then goes back to 0, once it goes to 0 the letter increments to Z in this case, now once the last three characters get to Z99, then the 11 gets incremented eg, 11Z99 will become 12A00. Hope that is clear :)

My question is, can this system be implemented into the auto numbering in Access?:confused:

Thanks in advance


View Replies !   View Related
Auto Numbering.....again
Is it possible to start auto numbering from a number other than 0? Say 2000 for example.

Thanks in advance


View Replies !   View Related
Row (record) Numbering
Hi there
I'm sure that this must be easy, so go gently on an old codger!
I have a query for which I wish to add another field containing a running total of the number of records produced by the query (after all sorting etc.).

In other words, if my query produces n records, that new field would contain the value 1 in the first record, 2 in the next and so on to n.

Any ideas would be much appreciated

View Replies !   View Related
Numbering Groups
I have created a work order application, and the process is that it takes total number ordered of an item, divides it by a factor, and then prints x number of work orders, example qty ordered 300 = 5 work orders, 4@72 and 1@12, my question is, now I want to appended the work order number with a count number, example wo555-1, wo555-2 and so on, grouping on the work order number, so each work order start again at 1. I am stumped, can anyone help.

View Replies !   View Related
About Auto-Numbering
can I make a field that can auto-number which the format that I want??

As I know that there is two format for auto-number
but I don't like them

I want to number my own format


XXX is the category number that would be selected in the form connecting to table
YYYYMM is the date year and month
ZZZZ is another auto numbering

Can I do so

View Replies !   View Related
Auto Numbering
How do I get a auto Number to start a 1001?


View Replies !   View Related
Automatic Numbering
Hi I am a newbie on here!!!!! Anyways, now that I got that out of the way.... I need help on my Access Database. Hopefully this isn't a stupid question..... I would like to have one of my cells in my form to automatically count from 1000-9999 when I go to the next form. when it hits 9999, I need it to start over, but not delete any of the previous entries that I have in there. Anyone willing to help me out?

Greatly Appreciated, I am sure I will have more questions!!!

View Replies !   View Related
Numbering Invoices
I have a report that produces invoices and gets its data from a multi-table based query. The query has multiple lines for each customer and the report groups and totals them. How can I have the invoices numbered sequentially? Any help please?

View Replies !   View Related

Copyright © 2005-08, All rights reserved