First, let me help you avoid a longer term problem that may occur and
cause
no end of grief. Do not use Name, Address, etc. as the name of a table.
It
and many other words are reserved words in ACCESS, and can create serious
confusion for ACCESS and Jet. See these Knowledge Base articles for more
information:
List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266
Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763
Second, may I encourage you not to put spaces in table or field names?
Doing
that means you must always use [ ] characters to delimit the names, and
when
you forget.... well, ACCESS and Jet will revolt on you! < g >
OK - now on to your situation. What I think is giving you concern is "How
do
I find out what the NameID and AddressID values are for the records that
I
add into my Name and Address tables, so that I can use those values as
the
values for the foreign key fields in the License table?" Right?
Actually, what you seek to do is not overly difficult, but may be a bit
daunting if you're not reasonably familiar with handling recordsets and
action queries in VBA code. I probably would do all the data appending
via
VBA programming, because it's very easy to get the primary key value when
you are adding the new record (appending the record).
(Note that it can be done in normal queries by using the DLookup function
to
find the two primary key values from the Name and Address tables -- *if*
you
can uniquely identify the newly added records in the Name and Address
tables
*without* knowing the primary key value. This may or may not be possible
for
you, so I would use the VBA approach.)
So, what I would do is to approach the appending work this way in a VBA
code
procedure (general statements here):
1. Read in an EXCEL record. (Note: you can import the EXCEL data into a
temporary table and then loop through the record in the temporary table
2. Parse all the data as needed from the EXCEL record into variables so
that
I can easily use them for adding new records to my tables.
3. Add a new record to the Name table. While I am adding it, store the
primary key value (NameID) that is created when the record is being added
into a variable. This will allow the correct appending of data to the
License table. (Note that this step assumes that you don't need to worry
about whether the person already exists in the Name table. If you need to
check that first, your code will need to do a search of the Name table to
see if there already exists a record with the same data. If yes, then
obtain
that primary key value and store it into the variable and don't add
another
record.)
4. Add a new record to the Address table. While I am adding it, store the
primary key value (AddressID) that is created when the record is being
added
into a variable. This will allow the correct appending of data to the
License table. (Note that this step assumes that you don't need to worry
about whether the address already exists in the Address table. If you
need
to check that first, your code will need to do a search of the Address
table
to see if there already exists a record with the same data. If yes, then
obtain that primary key value and store it into the variable and don't
add
another record.)
5. Add a new record to the License table, using the stored primary key
values and the other EXCEL data.
6. Continue the process (steps 1 - 5) for each EXCEL record that you read
in.
Does this make sense as a conceptual approach? Coding it will take some
time, and goes a bit beyond what a newsgroup typically would provide, but
.... well, let's see what your thoughts are to what I've posted so far.
--
Ken Snell
<MS ACCESS MVP>
LotsOimports said:
Thank you for your reply. And I was mistaken about multiple primary
keys.
Let
me go more into depth about what I am importing.
The data that this database is going to contain is license numbers for
various states and professions. Every inividual person on this database
may
must have 1 (or more) license numbers (a one to many relationship).
Each
license number will have board, state, and privilige information. Each
license will also have an address associated with it. Please note that
a
license number by itself is not unique, it requires board, state, and
privilige information to distinguish it form other records.
Although it would be easier to show a relational diagram for the
database,
text will have to do.
Table 1: Name
Last Name
First Name
Middle Name
NameID (Primary Key)
Table 2: Address
AddressID (Primary key)
Address1
Address2
Address3
City
State
Zip
Table 3: License
License# (primary key)
Expiration
Board (primary key)
State (primary key)
Privilege (primary key)
NameID(related to Name.NameID
AddressID(related to Address.AddressID)
I hope I gave you a good picture of the database and of the
relationships
between these tables.
The data is being imported through Excel spreadsheets. It contains the
essential information required for the database (some sources give more
information then others, but all data required for the relationships to
work
are provided).
An example of a typical record would be:
Last Name: Smith
First: John
SLN: APN009764KA
License_State: PA
Board: Nursing
Privilege: Nurse Practitioner
Address1: 223 town ln
City: Philadelphia
State: PA
Zip: 19045
This person may also have a RN license in PA, connected to the same
address.
I'm OK in terms of designing the database, as I have designed the
tables
and
relationships already. I have also written queries which insert data
not
provided (for example, license state). My key problem is append
queries,
which I have never used before. My question is, how do I use an append
quer(ies) to insert this data from one source (i.e an excel
spreadsheet)
to
multiple tables in my database, keeping my referential integrity
intact.
Currently, I have gotten as far as importing a spreadsheet to one table
in
my
database and creating an append query which doesn't work. I have tried
creating relationships in the query with the import table and the
perminant
tables in my database, but I keep recieving error messages that fields
do
not
exist (and they do).
Any help that could be provided would be appreciated...
:
You're going to need to do multiple append queries, because an append
query
can add to only one table. As you've discovered.
However, I am completely mystified by your comment that you can't do
that
because you have multiple primary keys for each record. A record, by
definition in a relational database, can have only one primary key.
That
primary key may consist of a single field, or it may consist of
multiple
fields, but there still is only one primary key.
Thus, I must assume that you're using multiple fields as a composite
primary
key in your table. Which is normal.
So, why will an append query not work in this situation? How else will
a
relational database work if you cannot put the primary key value into
each
record?
I think you need to tell us a lot more information about what you're
doing
so that we can assist. Perhaps you can give us some data examples as
well.
--
Ken Snell
<MS ACCESS MVP>
message
I would like to import data from one source and export it to multiple
existing tables in my database. I have already created these tables
and
have
defined relationships for them. The basic information/ tables are
license
numbers, names, and addresses. I am importing them from exel
spreadsheets.
When I attempt to create an append query, it will only allow me to
append
to
one table in my database. This creates a headache because creating
multiple
append queries does not work because my data does not have a single
unique
IDs, but multiple primary keys for each record. Creating an
autonumber
will
not work since my data already in the tables has a autonumber field,
and
this
will conflict with the IDs already in the table.
Access does allow me to create tables and relationships during a
import
wizard, but this does not work since I will be consistanly
importing/updating
data in my already existing tables. Can anyone help me!?
