Re: Append query to multiple tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 02/01/05 

Date: Mon, 31 Jan 2005 21:37:06 -0500

I am not familiar with a web site that has lots of "teach yourself VBA" type
stuff....there are tons of sites with info about different features, etc.

For a start, go here and you can visit the websites that are linked there:
http://www.cadellsoftware.org/WebSiteLinks.htm 

-- 
        Ken Snell
<MS ACCESS MVP>
"LotsOimports" <LotsOimports@discussions.microsoft.com> wrote in message 
news:81B561C4-59B7-4CBE-9C3D-181E81DB1506@microsoft.com...
> 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!?
>> >>
>> >>
>> >>
>>
>>
>>
Quantcast