Bulk Update To Db Tables

[Hoop]

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

 

[Klatuu]

Use append and/or update queries.

 

[Hoop]

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

 

[Tom van Stiphout]

On Mon, 10 Nov 2008 16:34:01 -0800, Hoop

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

-Tom.
Microsoft Access MVP

 

[Klatuu]

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]

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]

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

 

[John W. Vinson]

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.

 

[Hoop]

Hi, I didn't even know about compacting the db. I just did it and now it
reduced to 1.03gb. So do you think I still need to split? I've been using
access for years and always assumed I was using it wrong. I used to use it
to crunch baseball stats and I didn't split tables then either. So if it
works like this, should I keep using it this way? Thanks, Hoop

 

[Hoop]

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

 

[Hoop]

Hi, I just compacted the db and it reduced it to 1.03gb. Do you think I
still need to split? It does seem redundant to list the words, cd, vinyl,
cassette hundreds of thousands of times instead of 3. Thank you, Hoop

 

[John W. Vinson]

Hi, I just compacted the db and it reduced it to 1.03gb. Do you think I
still need to split? It does seem redundant to list the words, cd, vinyl,
cassette hundreds of thousands of times instead of 3. Thank you, Hoop

That has absolutely nothing to do with splitting the database, and everything
to do with proper normalization of your table structure. Care to post the
names, fieldnames and datatypes, and relationships of your tables?

 

[Hoop]

Hi, I haven't defined any relationships yet. I have two types of tables, the
inport table called T Listings Complete. The fields are ID (which is simply
the distributors ID; I don't use it for anything), Title, Artist,
Availability, Category, Format, Release Date, Label, UPC, tracks, number of
discs. All fields are text, except for Tracks which is Memo (because it is
lengthy). The UPC field is my primary key and is set to Text, indexed with
no duplicates.
The other type of table is the Master Tables. I have one for each format;
CD, Vinyl, Cassette (which are the 3 I'm using right now), DVD, VHS (which I
haven't added any entries yet, but will shortly as soon as I get this part to
work). The fields are Title, Artist, Format, UPC; all text, with UPC
indexed, no duplicates.
The Master tables are a listing of everything that I carry, whether it is in
stock or not, which is why it only has the basic information for each item.
The Complete table is everything that I am currently selling and is in stock.
Right now the two sets of tables are exactly the same, because I haven't
made any changes yet. After everything is input into the two sets of tables,
I planned to define relationships, and then do queries. I list my items on
different sites on the internet, which requires a different upload template.
I plan to do a query for each site I upload to, which allows me to pick the
fields I need for the template, in the correct order, as well as setting
criteria like, how many I have in stock to be able to upload a particular
item, etc. Am I going about this the right way? It has always worked in the
past with about 100,000 items, but now I am expanding it a lot. Also, I just
traded in Office 97 for Office 2007, so I am getting used to this new
version. Thank you for your help, Hoop

 

[John W. Vinson]

Hi, I haven't defined any relationships yet.

Well... then you don't have a database; you have some spreadsheets. A database
without relationships is like an Excel spreadsheet without any formulas... not
using the program for what it's designed to do!

I have two types of tables, the
inport table called T Listings Complete. The fields are ID (which is simply
the distributors ID; I don't use it for anything), Title, Artist,
Availability, Category, Format, Release Date, Label, UPC, tracks, number of
discs. All fields are text, except for Tracks which is Memo (because it is
lengthy). The UPC field is my primary key and is set to Text, indexed with
no duplicates.

If you ever want to be able to search tracks (efficiently), sort by track
(e.g. find all albums containing a version of Gloomy Sunday), etc. then you
really should have a one to many relationship from Albums to Tracks.

The other type of table is the Master Tables. I have one for each format;
CD, Vinyl, Cassette (which are the 3 I'm using right now), DVD, VHS (which I
haven't added any entries yet, but will shortly as soon as I get this part to
work). The fields are Title, Artist, Format, UPC; all text, with UPC
indexed, no duplicates.

Now here I'd say you ARE on the wrong track. The format of an album is an
attribute of the album! I'd really have *one* big table of albums, with a
field for Format. If you have a given album available on all three media, then
there would simply be three records for it. You could use Queries to generate
a form or report showing just CD's or just MP3's or whatever you end up
carrying.

To fully normalize this you will want to consider adding several more tables:

Artists
ArtistID
LastName
FirstName
<any biographical data you want to consider>

AlbumArtists <which artists are featured on which album>
UPC <which album are you talking about>
ArtistID
<any info about this artist on this album>

Format <just a lookup table for data entry/editing>
Format <Text, Primary Key> <e.g. DVD, CD, 45rpm, LP, ...>

Labels
LabelID
LabelName <e.g. "Deutesche Grammophon Gesellschaft", "Stax">
<information about the label, e.g. date range in business, ...>

Genres
Genre <Text, Primary Key>

AlbumGenres
UPC
Genre <a given album might be both "Jazz" and "Rhythm & Blues" for
example, you would use multiple records in this table to define them>

The Master tables are a listing of everything that I carry, whether it is in
stock or not, which is why it only has the basic information for each item.
The Complete table is everything that I am currently selling and is in stock.
Right now the two sets of tables are exactly the same, because I haven't
made any changes yet. After everything is input into the two sets of tables,
I planned to define relationships, and then do queries.

STOP!

Any time you have two tables, or two sets of tables, with identical field
designs, *your structure is WRONG*. I would suggest that your master table
should simply have a field or fields for in-stock items; these would be NULL
if the item is not in stock, and searchable if they are. These might be fields
for number in stock, shelf location, whatever is appropriate.

I list my items on
different sites on the internet, which requires a different upload template.

These would be Queries and/or Reports and would not affect your table
structure. Build the table structure to hold the data - and use Queries to
upload the listings. So you're on the right track there.