Merging new data to existing table

Hello all,

I am new to Access and need some guidance. I have an .mdb file that currently
has 1 table and data within that table. I will be getting data from people in
both excel format, as well as other .mdb files that contain the same field
names. I am to import the new data into the existing .mdb file. The criteria
when importing this new data states that a new unique ID must be assigned to
each row of data.


Here is the tricky part. The unique ID's that are created must start at the
next highest number above the existing number in the existing mdb file. So if
the last number is 5000 in the current existing mdb file, the new data being
imported must start being issued ID's at 5001. Keeping in mind that sometimes,
the ID's may be given to me, in that case, I would still need to merge the
data without generating ID's for them if they already exist.

So far, on my own, I am only able to import new data into an existing table
but do not know how to set up that ID generation I just described.

The second thing that needs to be done is something similar to the
concatenate feature in Excel. Where I have one blank column in the mdb table
that needs to be populated with concatenated values based on 2 different
fields, so in theory, I would like to set a conditional statement that says,
if row D = 1, then C = Custom Value+ID Field( + Indicates concatenation),
else (C= Custom Value+ID Field).

The conditional statementes I listed I would have no idea where to put them
or the syntax for them.

Any and all help greatly appreciated.

Very easy. Set up an autonumber field in the new table. When you append the
data to this table, leave the autonumber field alone. It will automatically
fill in the number as required.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"newyorkfonzie" u49288@uwe wrote in message news:911bb676a1096@uwe...
Hello all,

I am new to Access and need some guidance. I have an .mdb file that
currently
has 1 table and data within that table. I will be getting data from people
in
both excel format, as well as other .mdb files that contain the same field
names. I am to import the new data into the existing .mdb file. The
criteria
when importing this new data states that a new unique ID must be assigned
to
each row of data.


Here is the tricky part. The unique ID's that are created must start at
the
next highest number above the existing number in the existing mdb file. So
if
the last number is 5000 in the current existing mdb file, the new data
being
imported must start being issued ID's at 5001. Keeping in mind that
sometimes,
the ID's may be given to me, in that case, I would still need to merge
the
data without generating ID's for them if they already exist.

So far, on my own, I am only able to import new data into an existing
table
but do not know how to set up that ID generation I just described.

The second thing that needs to be done is something similar to the
concatenate feature in Excel. Where I have one blank column in the mdb
table
that needs to be populated with concatenated values based on 2 different
fields, so in theory, I would like to set a conditional statement that
says,
if row D = 1, then C = Custom Value+ID Field( + Indicates concatenation),
else (C= Custom Value+ID Field).

The conditional statementes I listed I would have no idea where to put
them
or the syntax for them.

Any and all help greatly appreciated.

Arvin Meyer [MVP] wrote:
Very easy. Set up an autonumber field in the new table. When you append the
data to this table, leave the autonumber field alone. It will automatically
fill in the number as required.
Hello all,

[quoted text clipped - 39 lines]

Any and all help greatly appreciated.


That solution doesn't work. A new error table is created showing that the
imported data had a null value for all rows in the ID field.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200902/1