If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 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 |
#12
|
|||
|
|||
Bulk Update To Db Tables
On Thu, 13 Nov 2008 20:01:28 -0800, Hoop
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] |
#13
|
|||
|
|||
Bulk Update To Db Tables
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 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] |
#14
|
|||
|
|||
Bulk Update To Db Tables
On Fri, 14 Nov 2008 01:00:01 -0800, Hoop
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] |
|
Thread Tools | |
Display Modes | |
|
|