Re: Bulk Update To Db Tables

Hi, no I don't have any images or photos; another poster suggested compacting
the db. I did that and it reduced to 1.03gb. So do I still need to split?
I've been using access for years for crunching baseball stats and never
split, but I've been under the assumption I wasn't doing it right. It does
seem kind of redundant to list the words cd, vinyl, cassette hundreds of
thousands of times instead of 3. Thank you, Hoop

"Klatuu" wrote:

Okay, I don't think splitting the database is what is needed.
If you are hitting the 2 gig limit, the problem is most likely not the
volume of data, it is likely you have some images or photos you are storing
in the database. That is what usually causes mdbs to max out.

I have some mdbs that contain multiple apartment complexes, information on
each resident, all their billing hisorty for all their utilities. I have
seen tables with 1.5 million records.

So post back and let me know if you are storing graphics.

The correct thing to do is store the graphics in folder and use either a
text field or a hyper link field to keep track of them
"Hoop" <Hoop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2490BF21-37F1-4557-8A3D-CC668EE9E2D4@xxxxxxxxxxxxxxxx
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: 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)
  • 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)