Re: Converting bad table design to good design

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Brendan Reynolds (brenreyn)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 18:10:14 +0100

There isn't really a whole lot to learn about append queries. Apart from the
fact that they append data from one table (or query) to another table (or
query) they are queries like any other. You design them in the same way as a
select query, except that in query design view you select Append Query from
the Query menu. You'll be prompted for the name of the table you want to
append to, and then an "Append To" row will be added to the query design
grid. You use this new row to specify which fields in the source table (or
calculated columns in the query) get appended to which fields in the target
table. 

-- 
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
"Melvis" <Melvis@discussions.microsoft.com> wrote in message 
news:791DF81E-0C8A-4D86-90F7-D1FFA7532680@microsoft.com...
> Thanks a bunch for the insight. One question if you get notified of 
> replies:
> I think we might go with a whole new database schema design, as well as 
> new
> queries, forms, reports, etc. All I would need to do would be to import 
> the
> data into the new schema. I have never used append queries. Where is a 
> good
> place to get information on them in regards to my problem?
>
> "Brendan Reynolds" wrote:
>
>> Sorry, I'm afraid the answer is no, there is no quick and painless way to 
>> do
>> it. The  analyzer is, unfortunately, completely useless, don't waste your
>> time with it.
>>
>> The only solution, in my experience, is to create the new schema and then
>> write a bunch of append queries to copy the data from the old schema into
>> the new one. Because of the amount of work involved in a) transferring 
>> the
>> data and b) modifying queries, forms, reports and code to recognize the 
>> new
>> schema, I've often had to do this kind of job in several stages, rather 
>> than
>> trying to fix everything at once.
>>
>> -- 
>> Brendan Reynolds (MVP)
>> http://brenreyn.blogspot.com
>>
>> The spammers and script-kiddies have succeeded in making it impossible 
>> for
>> me to use a real e-mail address in public newsgroups. E-mail replies to
>> this post will be deleted without being read. Any e-mail claiming to be
>> from brenreyn at indigo dot ie that is not digitally signed by me with a
>> GlobalSign digital certificate is a forgery and should be deleted without
>> being read. Follow-up questions should in general be posted to the
>> newsgroup, but if you have a good reason to send me e-mail, you'll find
>> a useable e-mail address at the URL above.
>>
>>
>> "Melvis" <Melvis@discussions.microsoft.com> wrote in message
>> news:8B566249-8A1F-43D9-8814-564CDBDAC1A9@microsoft.com...
>> >I have acquired the wonderful task of redesigning a database that has 
>> >been
>> >in
>> > use for about 3 years. The person who designed the database did not
>> > normalize
>> > the data, so I have one table with several columns that all represent 
>> > the
>> > same type of data (imagine a student schedule with a column for each
>> > class,
>> > rather than a table to link the student and class tables).
>> >
>> > What my problem is now is that I must either continue to work with the
>> > poor
>> > design, or split the 48,000 or so records into maybe 200,000 records. 
>> > What
>> > I
>> > need to know is if there is a quick and painless way to do this. The
>> > analyzer
>> > seems to only want to split out certain fields, and not the way I would
>> > want
>> > it. Besides, like I said, it's one-to-many, but there are always 3 
>> > columns
>> > for categories and 6 columns for reasons. Let me know if there is any 
>> > more
>> > information you would need to help me out on this one...
>> >
>> > OLD TABLE:
>> > Tracking # (key)
>> > Employee (who dealt with the application)
>> > Date
>> > Customer Name
>> > Category for review
>> > Reason for review
>> > Reason for review 2
>> > Category for review 2
>> > Reason for review 3
>> > Reason for review 4
>> > Category for review 3
>> > Reason for review 5
>> > Reason for review 6
>> > Comments
>> >
>> > NEW TABLES:
>> > APPLICATION
>> > Tracking # (key)
>> > Employee (who dealt with the application)
>> > Date
>> > Customer Name
>> >
>> > REASONS
>> > Reason # (key)
>> > Tracking #
>> > Category for review
>> > Reason for review
>>
>>
>>

Relevant Pages

  • Re: Append Query Very Slow
    ... When you append records, Access has to ensure that indexes are not violated, ... I have an append query that tries to append approx. ... I was wondering if the reason the query may be slow ... > because of the multiple primary key. ...
    (microsoft.public.access.queries)
  • Re: Table has field with duplicate records
    ... Is that the same as duplicate values? ... > to append, but for some reason that did not work. ... > records within my Make Table Query or in the actual table. ...
    (microsoft.public.access.tablesdbdesign)
  • Update To Formulas Gone
    ... I was building an update query to update or populate a number of fields when, ... for some reason, my update formulas dissappeared. ... query and then review the results. ... I could have sworn that the update formulas remained unless you delete them. ...
    (microsoft.public.access.queries)
  • Re: Membership database updates
    ... When you open any Query in Design View, click on the toolbar just next to ... black down-arrow where you can change your query to an Append or Update ... If you have any current members in your Import list, ... You mention Update Queries and Append Queries but I can't find how to ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Assign macro on After update event
    ... I don't see why you can't append the current record next scheduled date to ... it then try using an update query to update the dates in the new record. ... "Anna" wrote: ... maybe you can help me with that: all this macro is ...
    (microsoft.public.access.forms)