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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Jeffrey CoachmanMIS 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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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...


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.