Looking to combine data

[brentagade]

Hello,



Just registered. I apologize if this is in the wrong group so feel free to move it to the appropriate one.

What I'd like to do is take data from 3 different fields that is entered in a form and combine it in a new field in a table.

Something like this:

Form:

Month Day Year Control Number
03 23 2016 (auto assign a sequence number, i.e. 001, 002, 003)

When this data goes to a table I'd like it to do this in the table:

ID Month Day Year
20160323001 03 23 2016
20160323002 03 23 2016


Is this possible?

I'm using Access 2013.

Any info or suggestions would be greatly appreciated.

Thanks
Brent

[rpeare]

You don't need a new table, the only thing you're missing is a sequence number which you can store in your original table and generate the 'id' field whenever necessary. Unless you have a specific reason to create a new table with the exact same data in a different layout I would try to stay away from creating temp tables because every time you add data you'll be, potentially, re-sequencing data which may not be what you're after.

So let's say you have

tblTest
T_ID  T_Date      T_Desc
1     3/23/2016   AAA
2     3/23/2016   CCC

and you run your procedure to sequence these in a new table then you add a new record

tblTest
T_ID  T_Date      T_Desc
1     3/23/2016   AAA
2     3/23/2016   BBB
3     3/23/2016   CCC

Unless you have a reliable way to sort your records (for instance going on the T_ID field) you may get a result where in the first example you'll get a sequence number of 1 for the AAA record and 2 for the CCC record, in the second example you'd get a sequence number of 1 for the AAA, 2 for the BBB and 3 for the CCC record.

It'd be far easier to determine the sequence number at the time of data entry rather than what you're trying to do.

That being said

you could use something like

dim rst as recordset
dim iSeq as long
dim dPrevDate as date
dim dCurrDate as date

set rst = currentdb.openrecordset("SELECT * FROM tblTEST ORDER BY T_Date, T_ID")
iSeq = 0
dPrevDate = rst!T_Date
do while rst.eof <> true
    dCurrDate = rst!T_Date
    if dCurrDate <> dPrevDate Then
        iSeq = 1
    else
        iSeq = iSeq + 1
    end if
    currentdb.execute ("INSERT INTO tblTemp (SequenceConverted) VALUES ('" & datepart("yyyy", rst!T_Date) & format(datepart("m", rst!T_Date), 00) & format(datepart("d",rst!T_Date), 00) & format(iseq, "000") & "'")
    rst.movenext
loop
set rst = nothing

[brentagade]

Hello,
Thanks for replying. I may not have been clear in what I would like to do so let me try to restate.
I do not want to create 2 tables. When the data is entered into the form which would be a manually entered date and a auto generated sequence number, it will go to a table. In that same table is it possible for the information to be combined into a new field in a specific format.

Form
Date > "03/23/2016" or whatever format.
Sequence number > auto genned with new record.

If I pull up the historical data in the table, there is a field that took the above generation and made > 20160323001, 20160323002, etc...

Thanks

[rpeare]

I think that was my original point, you just need a sequence number in your table, you don't have any need to store the formatted field, you can generate it with any query you run.

There are dozens of ways to do this, are you using a bound form (bound forms are tied directly to queries or tables) or an unbound form? if it's an bound form does the sequencing have to happen on the main form or a subform? if there's no subform is your main form a continuous form or a single record form? The approach depends on how your data entry is set up.