Help Transferring Laptop Data To Main Dbase

[MBoozer]

Hi Guys. I was looking for some advice on the best approach to import table data that was compiled using a laptop into the main database that resides on our server. The dbase was designed to conduct a chemical inventory and then barcode each container. The table “Chemicals†is a one to many relationship. You can have only one chemical, but it can be present in many locations. For example, Acetone may be present at 10 different facilities and we don’t want Acetone to appear in the database 10 times. So the chemical is stored in one table with no duplicates. The Chemical table has a unique IDPartNo assigned to each chemical name using an AutoNumber so we don’t have duplicate chemical names. The Location data table also has an AutoNumber field specific to each container at different locations. When location data is added through a form, the AutoNumber assigns a unique identifier for that container at that location. The field is also populated with the IDPartNo of the chemical name like this

Chemical Tabl
IDPartNo(AutoNumber) ChemNam

Location Tabl
IDPartNo(From Chemical Table) IDBarCodeData (AutoNumber) Location Dat

The dbase works great. Here is the problem. We will be using a laptop to go out in the field and actually conduct the inventory. At the end of each workday, the dbase will be transferred to the mainframe computer database. How do I transfer the laptop data into the mainframe database tables each day. Somehow I am afraid that the AutoNumber fields will screw me up. Any help would be greatly appreciated. Thank you

 

[tina]

does the laptop db contain the current Chemicals table each day? are new
chemicals being added to that table on more than one laptop db?
from your description, your tables should be as follows:

tblChemicals
your description sounds good

tblLocations
do you have one?
LocationID
LocationName
LocationStreet
LocationCity
etc, etc, etc.

tblChemicalLocations
ChemLocID (primary key)
ChemicalID (foreign key from tblChemicals)
LocationID (foreign key from tblLocations)
or get rid of field ChemLocID; instead, use the two foreign keys as a
combination primary key.
linking table between tblChemicals and tblLocations
tblChemicals "1 to many" tblChemicalLocations
tblLocations "1 to many" tblChemicalLocations

assuming you have a tblLocations, are records added to that table on more
than one laptop in the field? is the same location added independently to
more than one laptop in the field?

all this makes a difference in how hard or easy it will be to update the
main database.

Hi Guys. I was looking for some advice on the best approach to import

table data that was compiled using a laptop into the main database that
resides on our server. The dbase was designed to conduct a chemical
inventory and then barcode each container. The table "Chemicals" is a one to
many relationship. You can have only one chemical, but it can be present in
many locations. For example, Acetone may be present at 10 different
facilities and we don't want Acetone to appear in the database 10 times. So
the chemical is stored in one table with no duplicates. The Chemical table
has a unique IDPartNo assigned to each chemical name using an AutoNumber so
we don't have duplicate chemical names. The Location data table also has an
AutoNumber field specific to each container at different locations. When
location data is added through a form, the AutoNumber assigns a unique
identifier for that container at that location. The field is also populated
with the IDPartNo of the chemical name like this:

Chemical Table
IDPartNo(AutoNumber) ChemName

Location Table
IDPartNo(From Chemical Table) IDBarCodeData (AutoNumber) Location Data

The dbase works great. Here is the problem. We will be using a laptop to

go out in the field and actually conduct the inventory. At the end of each
workday, the dbase will be transferred to the mainframe computer database.
How do I transfer the laptop data into the mainframe database tables each
day. Somehow I am afraid that the AutoNumber fields will screw me up. Any
help would be greatly appreciated. Thank you.

 

[Tim]

The way I have done this is to create an export facility
to send a csv file with the new data, which can then be
imported to the central d/base. If this is new data it
can be appended to the existing table. If not, I would
import to a new table and then change the exsing data via
an update query.

Alternatively, if the laptop user can log-on to the
server, you can create code to link the central table(s)
and run the update direct from the laptop.

Tim