Bulk Update To Db Tables

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

"Tom van Stiphout" wrote:

> On Mon, 10 Nov 2008 16:34:01 -0800, Hoop
> <Hoop.DeleteThis@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
>

On Thu, 13 Nov 2008 20:01:28 -0800, Hoop <Hoop RemoveThis @discussions.microsoft.com>
wrote:

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

John W. Vinson [MVP]

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" wrote:

> On Thu, 13 Nov 2008 20:01:28 -0800, Hoop <Hoop.DeleteThis@discussions.microsoft.com>
> wrote:
>
> >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?
> --
>
> John W. Vinson [MVP]
>

On Fri, 14 Nov 2008 01:00:01 -0800, Hoop <Hoop.DeleteThis@discussions.microsoft.com>
wrote:

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

>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 [MVP]