Bulk Update To Db Tables

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.TakeThisOut@discussions.microsoft.com> wrote in message
> news:ADA4C9E3-DB7D-409E-A972-6075424287D0@microsoft.com...
> > 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
>
>
>

On Mon, 10 Nov 2008 16:34:01 -0800, Hoop
<Hoop.TakeThisOut@discussions.microsoft.com> wrote:

You don't provide a lot of information. Generally speaking: yes, this
can be done using Append and Update queries.

-Tom.
Microsoft Access MVP


>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

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.RemoveThis@discussions.microsoft.com> wrote in message
news:F7AFBBAD-4523-47CC-BF91-B395A09E400B@microsoft.com...
> 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.RemoveThis@discussions.microsoft.com> wrote in message
>> news:ADA4C9E3-DB7D-409E-A972-6075424287D0@microsoft.com...
>> > 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
>>
>>
>>

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.DeleteThis@discussions.microsoft.com> wrote in message
> news:F7AFBBAD-4523-47CC-BF91-B395A09E400B@microsoft.com...
> > 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.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:ADA4C9E3-DB7D-409E-A972-6075424287D0@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>

On Wed, 12 Nov 2008 14:16:04 -0800, Hoop <Hoop RemoveThis @discussions.microsoft.com>
wrote:

>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.

Have you Compacted the database? That's absolutely essential if you're doing
lots of deletes and reloading, since Access will not free the space occupied
by emptied tables.
--

John W. Vinson [MVP]

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.RemoveThis@discussions.microsoft.com> wrote in message
news:2490BF21-37F1-4557-8A3D-CC668EE9E2D4@microsoft.com...
> 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.RemoveThis@discussions.microsoft.com> wrote in message
>> news:F7AFBBAD-4523-47CC-BF91-B395A09E400B@microsoft.com...
>> > 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.RemoveThis@discussions.microsoft.com> wrote in message
>> >> news:ADA4C9E3-DB7D-409E-A972-6075424287D0@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>