Append query steps

  • Thread starter Thread starter L. T. Portella
  • Start date Start date
L

L. T. Portella

Append query steps


1. Using Access 2000
2. Have two tables Mtable and Ctable
3. Both tables have identical fields i.e. name and address
4. Three different people in their own computers input daily info in their
own copy of Ctable
5. At the end of the day I want to append the three different ctables (which
of course have different info) into my Mtable in my own computer

What is the easiest way of doing this without coding? I need a step by step
help as I am a newbie. Thank you
 
If you have a network, split the database and put the backend on the server and
the front end on each of the three computers. Change your data entry form so
that everyone is entering data into Mtable and all your problems go away.
 
Let's go back a couple of steps before we move forward.

Step 1:
In an Access table (unlike an Excel spreadsheet) it's important for each
record to be unique in some way.
You'll usually use a single field (or sometimes a combination of fields)
which is different for each record. This is your Primary Key. Although
Access permits you to set up a table without a Primary Key, much of the
power of Access depends on these keys, so you lose a lot by not doing this.
Forgive me if the above is familiar territory to you - you don't mention
a primary key among your fields.

Step 2:
One common way of setting up a primary key is to add a field and set its
datatype to AutoNumber. This will provide a unique identifier for each
record.
The problem comes when you go to combine tables - your primary keys will
be unique on each copy of the mdb, but that doesn't mean they won't repeat
among your 3 computers.
So you'll need to either
a. set up a scheme whereby each computer generates primary key
numbers different from any other key, or
b. renumber your records when they are transferred to the "master"
mdb file.

Which brings me to Step 3:
Since you'll be transferring the data every night, I'm wondering whether
your computers are networked. (If not, how will the data physically get to
you? Are people bringing you floppies? E-mail?)
If your computers are networked, I'd strongly suggest putting the mdb in
a place where everyone can "see it" (on a shared drive) - that way the data
will be entered once, directly into the database.

Please post back if you have difficulty accomplishing Step 3.
(Even if you have a network, I'd still strongly recommend using a Primary
Key, as in Step 1.)

HTH
- Turtle
 
No, this won't do. We have a peertopeer network. Do you know of any other
way for a stand alone situation?
 
Thank you for your time.
1. I deliberately oversimplified my example. Each record will have a primary
key (Autonumber)
2. As you pointed out the problem will be that some primary keys may be
duplicated when the various tables are consolidated. I am not sure I know
how to renumber them when they are consolidated in the mastertable.
3. what should I do if the computers are stand alone stations/
than you
 
Still same recommendation ---

Split the database and put the backend on a common drive to all computers and
the front end on the C:\ drive on each of the three computers. Change your data
entry form so that everyone is entering data into Mtable and all your problems
go away.

Steve
PC Datasheet
 
Since you have revealed to other posters that you have a peer-to-peer
network, I'd strongly recommend that solution, unless you don't want your
various users to see each others' entries.

If renumbering in the master database is OK, you can construct your Append
Query so that it doesn't explicitly add any values to your autonumber PK.
Access will supply unique IDs for each row.

HTH
- Turtle
 
Back
Top