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!!!
I am trying to create a form that creates an automatic identifier with a value one greater than the previous identifier. I was able to create something that updates in batches of two: That is, I end up with identifiers that go 1, 2, 2, 3, 3, 4, 4, etc. How do I get it to create consecutive identifiers (1, 2, 3, 4, etc.)?
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?
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.
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, etc.
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.
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?
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:
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.
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.
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.
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?
I have an existing table with a field labelled Job Number (17 Job Numbers). Each month I import a new table and match each record via another field (serial number). For each new record, one or many, I want to attach a sequential Job Number. In this example the new job numbers need to start at 18 then 19 etc. How is this possible? I am fairly new to Access so please be gentle!. Cheers
I have a form to enter RMA data. I need to start my numbering at 4000 without using an autonumber. The ID field is text and cannot be changed. The Customer_Order table is a linked table. A letter is going to be added infront of the ID depending what the user chooses from a combobox. The letter(R,A,C) signifies what type of transaction it is. Here is an example -
R4000 A4001 A4002
Currently there are about 100,000 records in the database but none with an ID between 4000 and 10000. The majority of the records have a letter preceeding the number. Any ideas on how to start numbering at 4000?
I have the following code for the text field (Before Update)
If IsNull(Me![DyNo]) Then Me![DyNo] = Format(Nz(DMax("[DyNo]", "[tblAllDet]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000") End If Me![DyNo] = Format([DyNo], "00000")
The code starts giving numbers from 00001, 00002, 00003 and so on. The problem was that when I have to search, I have to type the zeros before the number i.e. 00007, 00008 etc. otherwise the search form doesn’t show the relevant record. Ideally, I would like the numbering system to be 1, 2, 3, etc. (without the zeros). I tried changing the code to “0” but with the single “0”, I am unable to insert records beyond No.10. Is there a way to change this code so that I get only 1, 2, 3 etc and not with preceding zeros ? Grateful for help.
If I have taken a program to creat a new program......I have cleared all the tables and what not. When I enter my first record in the auto format number starts at 963....... How do I get this starting back at zero?
Not sure as to whether or not this query shoulsd go here or else where? But here is the problem. I have been asked to repair a database that has lost the autonumbering facility. Apparently this was caused by somebody leaving the database open when the network crashed.
Each team can access the database via forms but now when the team responsible for registring new pieces of work tries to enter a new piece of work the autonumber reverts to one already within the database.
I was told that the only fix to this problem was the DMax function which I am unfortunatley not familar with. Can anyboby explain how to go abouit this and where it should be used.
*I know that this may not be the best way of resolving the problem but this is not the actual problem but my way to simplify of explaining the "problem".
I have a Table with 3 fields. Field 1 - Auto Number Field 2 - First Name Field 3 - Last Name
Initially, the Table was sorted via First Name, but now, there is a change in requirement to sort by Last Name.
Next requirement is to number (Auto Number) according to the Last Name.
Current Sorted via First Name Auto Number - First Name - Last Name 1 - Ben - Young 2 - Chuck - Norris 3 - Dennis - Johnson 4 - Ernest - Anderson
If sorted via Last Name 4 - Ernest - Anderson 3 - Dennis - Johnson 2 - Chuck - Norris 1 - Ben - Young
Desired result to Sorted via Last Name & (Auto Number) via sort. Auto Number - First Name - Last Name 1 - Ernest - Anderson 2 - Dennis - Johnson 3 - Chuck - Norris 4 - Ben - Young
Is there a way to do renumber / resort the auto number this?
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) ) AND (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:
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....
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:
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...
