Access Query - add 2 to the next row in a record field

I have a query that can have mulitple records in it - one of the rows in the query is a number from a linked table with one record - this number is (as an example) - 10  - I would like to be able to have the number for the next row be 11 - like adding a 1 to it -  so in an example - this is my current query:

Field1                 Field2
20130603          10
20130604          10

This is what I would like it to be

Field1                 Field2
20130603          10
20130604          11
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I'm still not entirely clear on this, or how it will be used.
...and I am almost certain that there is a simple way to do this...
But try this...
Jeffrey CoachmanMIS LiasonCommented:
Despite the simplicty of your request, there are a lot of mysteries here...
What is this data?
Is there only one record and you need to create the second record?
...Or does the second record already exist, and is a 10, and you need to change it to an 11
Or is the secoend record blank in Field2 and you need to insert an 11?
Why is it not sequentially numbered to begin with?
Does it have a primary key?
Will ther ever be more than 1 record...

In other words post an example of the *exact* raw data...
cbishopcgrAuthor Commented:
Ok - what I did was export the 2 tables & 2 queries to a spreadshee that I have attached -in different tabs in the workbook.  I hope I have explained myself well enough,,,,

SS CASH is the table with all the records in it that have the DATE field I am working with - in this case - there are two dates in it - same as I showed you in my example.  I did a group by query called GL Daily Cash Date - which just gives me the two dates and I added a field to link to the other query - you can see the query in the spreadsheet.

I then have the tglJournal table that I get the next number record from - I gave you a tab for the table - but I am really just using one record in that table - I have highlighted that record.

I then Created a query that links the GL Daily Cash Date Query to the tglJournal table by the common JrnlID field....I then get the NextJrnlNo from that table - but I need to advance to the next number in the next record.

On the tab that I have name Query I need - that is what I would like to have as my query result.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanMIS LiasonCommented:
So, ....then you want Page/Report 2, 3 and 4 to print on separate pages (4 pages) first, ...THEN you want Page/Report 1 to print on the first side of sheet 5, then you want Page/Report 5 to print on the bak side of Page 5.

Is this correct?
If so, then this is probably not possible without some serious custom coding.

I may be wrong, so lets see if other experts chime in...
cbishopcgrAuthor Commented:
No report - what I am really doing is creating an export to excel that has to be in a specific format - was able to get that part going - this is for an accounting program and those numbers are Journal Entry Numbers....So each grouping of records for the export - has to have the next journal entry number  - which I can get the 1st one because it will be in the linked table that way - I just need to be able to find a way to have each set of records with a different Journal Entry number.

DC    000000010     20130603    (all records with this date will be assigned 10)
DC    000000011     20130604    (all records with this date will be assinged 11)

If I only have one day's worth of data in the original file - then I have no problem getting the first number - but if there is more than one day - I just need to assign the next JE number to it - but in this case - I am making it up by adding 1 to the field that is in the NextJrlNo field.

Is there some way I can create a record number field (column) in the Query -
     starting with 0, then 1 and so on

Then I can add that field to the NextJrlNo field so:

0     DC    20130603    (10 + 0) = 10
1     DC    20130604    (10 + 1) = 11

I would like to try to do this as a field (column) in the Query - would be easy that way because it is just an export that I need.  I could do something like that in crystal reports - but in a database, I am unsure.
cbishopcgrAuthor Commented:
I did not see the first example you had put in the database 161 - this is really only my second time using this - but that did worked perfectly - so that should complete eveyrthing I need to do - thank you so very much...sorry for the confusion on reading this.....hope you did not have to do anyting additional
Jeffrey CoachmanMIS LiasonCommented:

oops, ...Sorry for the confusion.

My post here:
...was actually meant for another question...


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.