Append Record with Next Consecutive Number (not Autonumber)

Hi,

I have a table with two columns: ID, Version.

I would like to make a query that will append a record with the same
ID, but the next consecutive version number.

Example:
ID-Version
1-1
2-1
2-2

If I want to create a new version for ID 1, then the query will create
a new record: 1-2
If I want to create a new version for ID 2, then the query will create
a new record: 2-3

Any help would be appreciated. Thanks!
0
shm135
4/2/2010 5:14:39 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1615 Views

Similar Articles

[PageSpeed] 34

shm135 wrote:
>I have a table with two columns: ID, Version.
>
>I would like to make a query that will append a record with the same
>ID, but the next consecutive version number.
>
>Example:
>ID-Version
>1-1
>2-1
>2-2
>
>If I want to create a new version for ID 1, then the query will create
>a new record: 1-2
>If I want to create a new version for ID 2, then the query will create
>a new record: 2-3
>

INSERT INTO table VALUES([new record ID], Nz(DMax("Version",
"table", "ID = " & [new record ID])) + 1)

-- 
Marsh
MVP [MS Access]
0
Marshall
4/2/2010 6:07:42 PM
Reply:

Similar Artilces:

Excel 2003 random number generator
Hi, I'm using Excel 2003 to do some actuarial work. I notice that if a very large number of random number is generated(say 10000), and the spreadsheet is run by Macro for a large number of times(say 1000), sometimes, Excel 2003 will generate some negative random numbers, which is nonsense. Some of my friends told me earlier versions of Excel didn't have that problem. I'm sort of wondering what is going on with Excel 2003 random number generator and if Microsoft is going to develop a update to fix this problem. ---------------- This post is a suggestion for Microsoft, and Mic...

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

Find Missing Number?
Ok probably a easy answer here, but I'm a excel Novice. what I have is a series of numbers and what I want to find is what Number are missing in between all the different numbers to complete a whole set of number. Example I have: 1 2 6 7 9 13 How could I find whats missing as to make it straight count up from 1 to 13 And return from this example 3 4 5 8 10 11 12 Thanks for any help Hi there, one possibility is in an additional column or sheet, i would create the whole series (type A1: 1, A2: = A1+1, then copy downwards until you have the required series) then, next to this, so...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

converting number to string in hex
Hello there I have table with bigint values. I need to convert them to string as hex for example: 209 = C9 when i do select convert(binary(3), 209) it set on screen 0x0000C9 which is what i want. but when i try to enter it to string it set an empty string. Is there a way to enter "0000C9" to string? Here are two methods: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx -- Plamen Ratchev http://www.SQLStudio.com On Tue, 27 Jul 2010 17:40:55 +0300, "Roy Goldhammer" <royg@yahoo....

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

How do I insert numbers in a row?
I'm using Excel 2003 and have 1800 rows. I need to number them 1 through 1800. Is there a way to number each row (colum A) automatically? Thanks. a really easy way is to goto cell a2 type in 1>goto a3 type in 2> select both>drag down. -- Don Guillett SalesAid Software donaldb@281.com "Newbie" <spam@yahoo.com> wrote in message news:uJl1i$sZFHA.3808@TK2MSFTNGP09.phx.gbl... > I'm using Excel 2003 and have 1800 rows. I need to number them 1 through > 1800. Is there a way to number each row (colum A) automatically? > > Thanks. > > En...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

Append and Append To priviliges
Anybody know what these two priviliges do for a record?? Jay Append allows a user to be able to add items to an object for example you can append an address to an account. Append to allows a user to append this object to something else. You would need append to priv on the address object. They work together. "Jay Mehta" <jay.mehta@conexio.com> wrote in message news:b6caea5f.0310311043.682d756c@posting.google.com... > Anybody know what these two priviliges do for a record?? > > Jay So, in the example you gave of appending an Address object to an Account object,...

Copying autonumbered paragraphs and retaining original numbers
I need to copy numbered paragraphs from multiple documents. The paragraphs are inconsistent and may be numbered using text, MS Word multi-level list styles or listnum fields. I can search the documents, find the needed paragraphs and copy them to a new document, but when I do, the numbering resets according to the destination document's contents. How do I retain the source document's numbering in the destination document so the reader can determine where in the source document the paragraph came from? Can I convert all the numbering to text before pasting without affe...

cannot delete record...
hi, i have received an error when i tried to delete a closed invoice; "the record cannot be deleted because it is read-only" any ideas? Do you have permissin to delete? "Orkun Goze" <orkung@nospam.mostint.com> wrote in message news:OtgRKZioDHA.1672@TK2MSFTNGP09.phx.gbl... > hi, > i have received an error when i tried to delete a closed invoice; "the > record cannot be deleted because it is read-only" > any ideas? > > Yes Darrin. I am the system admin. "Darrin Bishop" <dbishop@lrs.no.sp.am.com> wrote in message news:eI...

Using form to add record to table where records are limited by lis
Hi all... Bit hard to explain what I am doing here but I'll have a go :-) In Excel if I want to limit the number of records shown in rows I apply a filter for example to col1 which reduces all remaining cols. Then filter on col2 to reduce further etc etc until I have only a few records left to look at. I want to do this in access on a form BUT... I want to create a table of records that has an ID, Date stamp, a part number, qty I want this table to get its information from a form. I want the form to use a master table which includes the "part number" from above but where ...

Recording annual leave
I notice that there are some excellent sample timesheets out there (eg, Walkenbach and McRitchie). I was wondering if there is anything similar which would enable me to keep track of my annual leave? MS has an Employee Absence XL file at: http://officeupdate.microsoft.com/TemplateGallery/templates/6/tp1268.asp?i=3&l=1099,930,944,1268,1560,1269,1266,1454,1267,919,931,1435,408,518,289,288,&RC=4&M=16&mh=20&qu=&ct=&cid=0.75.77 -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "C Tate" <colin@nobodyhere.mrcrtate.fsnet.co.uk> wrote in...

Create Company fails on next to last step
I have posted previously about deleting a company and reusing the same ID. The create a company within GP Utilities appears to be OK until the next to last step, Creating new MS Dynamics GP Company where it fails. The GP Utilities then fail at Upgrade companies. It hangs with a check box and nothing else. I can clear the company from the DB_upgrade table in the Dyamics database in SQL Server and regain control, however if I try to install a company, any new company whether it existed before or not, everything runs fine until the next to last step referenced above. thanks in advance,...

Fraction number in Access
Is anybody can tell me, if MS Access can accept fraction number like Excel?. I had a project before, the user had to enter with fraction number. I still can't figure it out untul now. KF -- Message posted via http://www.accessmonster.com That depends... do you want Access to merely "accept" a string of characters, or will you be wanting to "do math" on the value that string of characters represents? If the latter, one way to do this would be to build a function that takes a "text" input (e.g., "1/5") and converts it to a decimal equivalent. ...

Automatically move down to the next row
I have a macro that automatically inserts the time and date in certain columns. Is there a way as part of the macro once the date and time have been populated, I can get to the start of the next row? Any help appreciated. You know the row in which you are adding the time/date. Just add 1 to it and select the first column. if you show your macro, it would be easier to help. - Mangesh "Pank Mehta" <Pank@discussions.microsoft.com> wrote in message news:25808B8C-BCDF-44EA-969F-E02E1CA52339@microsoft.com... > I have a macro that automatically inserts the time and date i...

Help
I don't know if this is a known problem or something peculiar but I am using Excel 2002 with win 2000 at work and at home. I brought home file from work and opened it and all the whole numbers in one column are divided by 100 ie 230 became 2.3 - so, of course, all the subsequent calculations are adrift. I'v tried clearing all the formats and reformating the column but same thing keeps happening. Any ideas any-one ? Regards Scott Scott, Go in tog Tools>Options and on the Edit tab, check to see if the Fixed Decimal places checkbox is set. If it is, unset it. -- HTH Bob P...

MX record question #2
I have one windows 2003 and one exchange on the same server need to receive the email from Internet. Did I need to setup a MX record in my own DNS server point to my own server. Or only need to add a MX record in the ISP to point to my server Internet IP address ? or both needed? thanks in advice. lamlam wrote: > I have one windows 2003 and one exchange on the same server need to > receive the email from Internet. Did I need to setup a MX record in > my own DNS server point to my own server. Or only need to add a MX > record in the ISP to point to my server Internet IP addre...

sorting text & numbers
I have a large list that I want sorted like this: Ward 1 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 Ward 10 When I sort it, it says: Ward 1 Ward 10 Ward 2 & 3 Ward 4 Ward 5 Wards 6-8 Ward 9 If I just take out the words Ward and Wards, it makes some of the numbers into dates, and does something else to some other cells that returns a big number. Anything I can do? -- billjr ------------------------------------------------------------------------ billjr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25927 View this thread: http://www.excelforum.com/sh...

Preventing duplicate records
The title sounds deceptively easy to solve, but I can't figure out how to make a table refuse to allow a new record that doesn't duplicate a combination of fields. I already have a primary key (autonumber) and a date and a location field. The ID field can't be duplicated, but it's ok for the date OR for the location to be duplicated. However, it is NOT ok for the combination of the date and the location to be entered. Any ideas how to implement that constraint? Thanks, Gina Use a unique index on the combination of the date + location. 1. Open the table in design view....

Status of Opportunity record wont change
Hi, I am trying to update the Status of an opportunity record using sdk 4.0.The application doesnt throw any exceptions even if "statecode" or "statecodename" is used as attributename.But the Status state wont change.It remains "Open". Please provide information on how to change the Status state through sdk 4.0. Regards, Pradyumna.N.V. Check out the following link for setting the Opportunity StateCode using crmService: http://msdn.microsoft.com/en-us/library/bb959545.aspx You may also want to look at the SetStateOpportunityClose if you need to create an Opp...

append query with dtae and username
I am using an append query to add a new line to my table - one of the field in this table is "Update_Date" and one is "Updated_By". How via this query I can populated those two fields with the current date&Time and current username of the user . Many thanks, Dan Please ignore- wrong Group, sorry "Dan" wrote: > I am using an append query to add a new line to my table - one of the field > in this table is "Update_Date" and one is "Updated_By". > How via this query I can populated those two fields with the current ...

Unlock new records?
Hi. I have a form that I am trying to protect from accidental changes. I used the code from Allen Browne's tip sheet "Locking bound controls" at http://allenbrowne.com/ser-56.html. It works perfectly. However, I would prefer each individual record to always stay locked unless a user clicks on the cmdLock button. I would also like for the record to lock back automatically when a user exits the record. I accomplished this by putting the following line in the OnCurrent property instead of the OnLoad property: =LockBoundControls ([Form],True) This works great, too. Now to the prob...

How does one change number zero to the zero with diagonal line.
I want to be able to type the numbe zero, but with the line through the character as often used in computer generated text. Jackson wrote: > I want to be able to type the numbe zero, but with the line through > the character as often used in computer generated text. The preferred way is to use a font that has the line through the zero character. One such font is Consolas, which comes with Office 2007 (or, if you have an earlier version of Office, download the Compatibility Pack which includes the new fonts). You may find other fonts with this character on the Web. A l...

VBA Open Record Set multiple tables
Dear All: I am trying to open two tables and read the data in VBA. One is a master table that contains key info, needed to grab data from the second. I am having trouble with the OpenRecordSet Commands. Set rst = db.OpenRecordset("Eweek", dbOpenTable) I want to Loop each record from "Eweek" - [ENO] and compare it to another table that has a same field [ENO]. (loop all records in the second table and calculate a function) if the [ENO] field matches. IF the data matches, I want to post the final results in a third table for reporting purposes. I am geting erro...