I realize this has been covered a billion times, I've read them all and still can't get it. My table looks like this:
DOCUMENT-SER.NO
MC006343 -SST-24569
MC006344 -SST-27209
MC006344 -SST-27210
MC006397 -SST-25019
MC006397 -SST-24562
MC006397 -SST-24562
MC006397 -SST-99173
MC006397 -SST-99173
DOCUMENT-SER.NO.-NEED THIS
MC006343 -SST-24569-0001
MC006344 -SST-27209-0001
MC006344 -SST-27210-0002
MC006397 -SST-25019-0001
MC006397 -SST-24562-0002
MC006397 -SST-24562-0003
MC006397 -SST-99173-0004
MC006397 -SST-99173-0005
At every change in Document Number, start the numbering over.
Using an Update Query, I need to update a field (named LINE) showing the numbers in the "NEED THIS" column above. 0001, 0001, 0002, 0001, 0002, 0003, 0004, 0005...etc.
This would give me a different line number for each document...
I've read all the autonumber and dmax posts, and still can't get it.
NOVICE Access User...can use queries pretty good, but not SQL.
MSAccess 2003, Windows XP Pro...
Thanks in advance!
2 1726
I'm sure there is a more elegant way to do this, but I've done this by breaking the task into a sequence of queries. Query 1:
Create Table TEMP_TABLE (
[DOCUMENT-SER-NO] Text(50),
Document Text(50),
rowid AutoIncrement,
sequence integer
); Query 2:
INSERT INTO TEMP_TABLE ( Document, [DOCUMENT-SER-NO] )
SELECT Left([DOCUMENT-SER-NO],InStr([DOCUMENT-SER-NO]," ")-1) AS doc, BaseTable.[DOCUMENT-SER-NO]
FROM BaseTable
ORDER BY Left([DOCUMENT-SER-NO],InStr([DOCUMENT-SER-NO]," ")-1), BaseTable.[DOCUMENT-SER-NO]; Query 3:
SELECT TEMP_TABLE.Docu ment, Min(TEMP_TABLE. rowid) AS MinOfrowid INTO MinList
FROM TEMP_TABLE
GROUP BY TEMP_TABLE.Docu ment; Query 4:
UPDATE MinList INNER JOIN TEMP_TABLE ON MinList.Documen t = TEMP_TABLE.Docu ment SET TEMP_TABLE.sequ ence = [rowid]-[MinOfRowid]+1;
Now, Table [TEMP_TABLE] looks like:
DOCUMENT-SER-NO Document rowid sequence
MC006343 -SST-24569 MC006343 1 1
MC006344 -SST-27209 MC006344 2 1
MC006344 -SST-27210 MC006344 3 2
MC006397 -SST-24562 MC006397 4 1
MC006397 -SST-24562 MC006397 5 2
MC006397 -SST-25019 MC006397 6 3
MC006397 -SST-99173 MC006397 7 4
MC006397 -SST-99173 MC006397 8 5
Then, you can either do an update query or whatever you need to using this new table. Your new document ID is
Trim([DOCUMENT-SER-No]) & "-" & Format([sequence],"0000")
If you want to add drop table queries to your sequence, they would look like:
drop table [MinList];
drop table [TEMP_TABLE];
Thanks...this works fine.
Thanks for the clairity...I've been reading all the autonumber posts and they were just making me crazy.
Thanks again
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: deko |
last post by:
Is there a way to reset the AutoNumber sequence?
I have several tables that use the AutoNumber field as the Primary Key, and
I'd like to somehow do an Import/Export that will make remove the breaks in
the sequence. A few breaks in sequence is not a big deal, but I have one
table with under 200 records, but the last AutoNumber PK ID field is over
1500 - due to a lot of edits....
|
by: Apple |
last post by:
1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.
2. I had create a query by making relation to a table & query, but I
can't update record in query or in form. I believe the problem is due
to the source query.
In source query, there is a filter to show the incomplete record
("is null" in delivery date)], but I need to re-use the job no. if the
job is...
|
by: junkaccount |
last post by:
Hello,
I currently have a field named QuoteNumber in a table named Quotes.
The field is set as autonumber and is used to assign sequential numbers
as users enter information in the table through a form. This number is
mainly used later as reference when a customer calls our office with
questions. We use the quote number to retrieve their related
information through a query. This QuoteNumber field is NOT a primary
key. It is not...
|
by: rdemyan |
last post by:
I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.
For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.
So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and...
|
by: PeteCresswell |
last post by:
Got a table with "RecordID"=AutoNumber=PrimaryKey.
Call it "tblIssuerRating".
Ran a query that appends to that table from a work table.
Inadvertantly appended from the work table's "RecordID", which was not
autonumber and, in fact, contained a zero.
The query ran successfully and I would up with a tblIssuerRating
| |
by: john |
last post by:
A record of a table with an autonumber field was accidentally deleted. From
the backup table I made a new table with only the deleted record. Then with
an add query I added that record to the original table. After that, when
adding a new record, the autonumber continued with the last ID value (from
the add query) + 1 there by overriding already existing records.
Is this WAD?
What should be best to do in such a situation?
|
by: Wayne |
last post by:
I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:
SELECT CDUGActuals.CDUGActID, CDUGActuals.ActualTonnes, +1
AS NextID
FROM CDUGActuals;
This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the contents of the form is inserted into the MS Access database.
The Customer table in the database already has 30 records (with CustomerIDs 1 - 30) in it (from when the database was first created). The CustomerID field in the database is an...
|
by: lawitt |
last post by:
I have a MakeTable query whose resulting table i'm using in a crosstab query. The fields in the crosstab are constantly changing (weekly), so my ultimate goal has been to somehow create a dynamic report that takes into consideration the ever changing fields. that's been VERY tricky.
I've managed to get the MakeTable query to only display my 'top 10' fields and i've created a 'static' report that references a calculated field (Number) in that...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |