Re: Bulk Update To Db Tables

Hi, this is the first time I've used a forum, so I didn't want to make my
posts too lengthy. I'll try to provide enough info; my old db just maxed out
at 2gig, so I'm stuck for the moment. I'm importing, and updating, my music
catalog from my distributor. The records have all the standard info of cd,
vinyl, cassette. The fields I think I need to split are format (cd, vinyl,
cassette), category (about 30 choices, rock, pop, etc.), Availability (about
5 choices from in stock to backordered), another possibility would be Record
Label, although this field has hundreds of different entries. When I split
the table, I came up with 8 splits that make sense to me, including the 3
above that have lots of duplicate entries. Right now I'm in the process of
getting everything I have for sale to input. I'm doing about 50,000 per
table. The first table is in, and I'm trying to figure out how to input the
2nd one. So Append Query is probably what I need to do, I'm just not too
sure how. And later, after everything is in the db, I will need to do weekly
updates; changing prices and availability for instance, and deleting items no
longer available. I guess the Update Query would do the trick? Thank you
very much for your replies. If you need more info, just ask. Hoop

"Klatuu" wrote:

There is still not enough information to provide specifics, Hoop, but it
probably will involve a query per table.
As to whether to use append or update queries, it depends on whether you are
adding new rows or making changesto existing rows in your tables. Append
queries add new rows and update queries modify existing data.

My personal practice is to clear the data in the import table before in
import into it just in case there was an error in the previous load and left
old data in the import table.

Then I execute the append queries and if there are any update queries to
reform the data I run those.

I don't clear the import table at this time so if I need to back out the
mods and start over, I don't have to do the import again if it is not
necessary.


As to the order, you need to follow the realtion path. Start with the
highest level parent table, then go to the next level, and if there are more
levels, continue in that order.
"Hoop" <Hoop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F7AFBBAD-4523-47CC-BF91-B395A09E400B@xxxxxxxxxxxxxxxx
Hi Klatuu, thank you for the response. Could you give me a little more
information? Which query would I use, append or update or does it matter.
Do I simply create a query for each table? So if my db split into 8
separate
tables, I create a query for each of the 8 tables, pulling data from the
import table? (which would have ALL of the data in it? And then run each
of
the 8 queries one at a time? And then delete the data in the import
table?
Would it matter which order I run the queries in? When I import into my
master table now, I have the primary key set to "indexed, no duplicates",
and
the query I'm using now simply refuses to import duplicates based on the
primary key. Would I set the primary for each of the 8 tables the same
way
and the 8 queries would do the same thing? I know this is lengthy, but I
appreciate all the help. Thanks, Hoop

"Klatuu" wrote:

Use append and/or update queries.

"Hoop" <Hoop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ADA4C9E3-DB7D-409E-A972-6075424287D0@xxxxxxxxxxxxxxxx
Hi, up to now I have been using a single master table for my inventory,
with
all of the duplicate date. I have figured out how to split the table
so
that
it works quite well, eliminating the duplicate data. But I can't
figure
out
how to add to the tables without doing it one at a time through a form.
I
need to be able to update the tables with thousands of records at a
time.
How do I do this? Thank you, Hoop






.

Relevant Pages

  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I'm calling a function that will set the occurence number. ... If there's a duplicate, ... I used the query wizard to create a "find duplicates" query, now I need to adjust the results of that query to add an occurence counter/number, and I'd be set. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... After playing around with duplicates, and a funtion to generate an occurence number, it looked like you would need a handful of queries per table!! ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ... On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? ...
    (comp.databases.ms-access)