Re: Append query to multiple tables

From: LotsOimports (LotsOimports_at_discussions.microsoft.com)
Date: 02/01/05 

Date: Mon, 31 Jan 2005 18:13:01 -0800

Thanks for the help. I now have a good understanding of the symantics of what
it is I need to do. The syntax, however, is an entirely different story. I
was hoping to be able to code in SQL (if not using the Access GUI), but you
gave me a good explaination of why this would not work. My VBA coding ability
is marginal at best, but I suppose this gives me a good reason to teach
myself the language. I appreciate the suggestions in terms of table naming,
but the naming schemas used for this example were simplified versions of the
actual table names and fields (it didn't make sense to keep switching back to
the database to give the actual table and field names).

I understand that there are limits to questions in these public forums, and
I appreciate the in depth help you have provided. I now have a good starting
point to create the queries neccessary to import the data. The only question
I have left is where there are good online resources for VBA programming. I
realize that I am going to eventually have to suck it up and by a book, but
the web is an excellent starting point.

Thanks again for all your help

"Ken Snell [MVP]" wrote:

> 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" <LotsOimports@discussions.microsoft.com> wrote in message
> news:EF189150-BA80-4FC9-928E-30B196A2EE11@microsoft.com...
> > 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 spread***)
> > to
> > multiple tables in my database, keeping my referential integrity intact.
> > Currently, I have gotten as far as importing a spread*** 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...
> >
> >
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> 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>
> >>
> >>
> >> "LotsOimports" <LotsOimports@discussions.microsoft.com> wrote in message
> >> news:38C9D255-28E8-4764-AD7B-351D30B53F77@microsoft.com...
> >> >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!?
> >>
> >>
> >>
>
>
>