cbishopcgr
asked on
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
Field1 Field2
20130603 10
20130604 10
This is what I would like it to be
Field1 Field2
20130603 10
20130604 11
ASKER
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.
NextNumberQuery.xlsx
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.
NextNumberQuery.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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.
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.
ASKER
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 it....it 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
cbishopcgr,
oops, ...Sorry for the confusion.
My post here:
https://www.experts-exchange.com/questions/28258176/Access-Query-add-2-to-the-next-row-in-a-record-field.html?anchorAnswerId=39547511#a39547511
...was actually meant for another question...
;-)
Jeff
oops, ...Sorry for the confusion.
My post here:
https://www.experts-exchange.com/questions/28258176/Access-Query-add-2-to-the-next-row-in-a-record-field.html?anchorAnswerId=39547511#a39547511
...was actually meant for another question...
;-)
Jeff
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...