starting over

[tcgaines]

i just had a good talking to. My boss was expecting to show him alot more than I did. Please help me if you can.

I work for a hard drive company. Harddrives are divided up into categories and sub categories. The highest level is "Family." Next is "Model Number," then the lowest is "Product Number." All of these are included in each of the worksheets and they all have different heading names.

The hard drives are stored in many different warehouses that are potentially in 3 different regions (AMERICAS, ASIA, EUROPE)

I am concerned with three excel worksheets that contain all or some of the fields listed above. The different worksheets represent different states of the hard drive: "InTransit" "Customer Shipments" and "Inter Company Shipments"

The goal is to consolidate all of this information into one worksheet.

The other 3 Worksheets represent the data I am pulling from. Ive written a module in access to import all of the excel files into their own individual tables. I've also written some queries to pull the info i need, im stumped though, I'm feeling like I should wipe the slate and start from scratch.
What is the best way to go about this?

 

[tcgaines]

i just had a good talking to. My boss was expecting to show him alot more than I did. Please help me if you can.

I work for a hard drive company. Harddrives are divided up into categories and sub categories. The highest level is "Family." Next is "Model Number," then the lowest is "Product Number." All of these are included in each of the worksheets and they all have different heading names.

The hard drives are stored in many different warehouses that are potentially in 3 different regions (AMERICAS, ASIA, EUROPE)

I am concerned with three excel worksheets that contain all or some of the fields listed above. The different worksheets represent different states of the hard drive: "InTransit" "Customer Shipments" and "Inter Company Shipments"

The goal is to consolidate all of this information into one worksheet.

The other 3 Worksheets represent the data I am pulling from. Ive written a module in access to import all of the excel files into their own individual tables. I've also written some queries to pull the info i need, im stumped though, I'm feeling like I should wipe the slate and start from scratch.
What is the best way to go about this?

okay if someones got a paypal account or will accept other means of payment im willing to pay for help.

thanks.

 

[Ron_dK]

In access, create a new Dbase using the template Service Call Management.
Once created , take a look at the structure ( especially relationships) and modify the tables/queries/forms to your need.
This might give you a good idea of how to approach your business application.

 

[Oldsoftboss]

Dont expect to create your first Db overnight. Access table structures take some grasping and time spent re-inforcing this will be time well spent. Once the correct table and relationship structure is in place data is easily accessed via queries.

Dave

 

[neileg]

I agree with the previous comments.

It's not easy going from spreadsheets to a relational database. In a spreadsheet you tend to store and view the data in one sheet. In a database, you store the data once and create as many views of it as you need.

I'm afraid I don't have time to design your db from scratch but here's some ideas:
1. You need one table to store the data on the individual drives. You will have one record per drive. Say this is called tblDrive
2. You will need a field that links to another table that holds the model number, tblModel. Each model number in the new table will have an unique ID known as a Primary Key (PK). There's no need for the users to ever see this and an autonumber will be suitable. You hold the PK in tblDrive for the model of that drive.
3. Another table, tblFamily will be linked to tblModel using the PK from tblFamily. This way you have a three table structure that reflects your category structure.
4. You will need fields in tblDrive to hold the status and the location of the drives, again probably linked to other tables.
5. At this stage you have information that, in individual tables, is incomplete, but by writing queries, you bring all this data together in the way you need, whatever that is.

 

[tcgaines]

wow you guys are awesome. thank you for the suggestions. so here's what ive done:

Main Table:

tblDrive
drvDriveID (PK - Autonum)
drvFamilyName
drvProductDesc
drvModelNum
drvProductNum

Look Up Tables:

tblFamily
famFamilyID (PK - Autonum)
famFamilyName

tblProductDesc
desFamilyDescID (PK - Autonum)
desFamilyDesc

tblModel
modModelID (PK - Autonum)
modModelNum

tblProduct
prProdID (PK - Autonum)
prProdNum

Am I off to a good start?

Thank you.

 

[tcgaines]

actually, since each record is unique in the look up table, there is no need for autonum, correct? the one field in each record is already unique. So in the lookup table "tblFamily," I have one column, named FamilyName, which is the PK, and which also establishes the one-to-many relationship with the Drive table by linking to drvFamilyName. Am I on the right track?

 

[tcgaines]

This is already starting to make alot of sense. Hopefully the sense I'm feeling is correct :/

Main Table:

tblDrive
drvDriveID (PK - Autonum)
drvFamilyName (FK - tblFamily)
drvProductDesc (FK - tblProductDesc)
drvModelNum (FK - tblModel)
drvProductNum (FK - tblProduct)

Look Up Tables:

tblFamily
FamilyName (PK)


tblProductDesc
FamilyDesc (PK)


tblModel
ModelNum (PK)


tblProduct
ProdNum (PK)

 

[tcgaines]

oh yea. this is fun

 

[RV]

You better always use an Autonumber column as your PK.
This way you'll always be sure that your rows are uniquely identified.

Your current table structure is incorrect.
I suggest you re-read neileg's remarks and start looking at sample databases first.
Also, read up on normalisation and relational databases.

RV

 

[neileg]

I agree with RV. As I pointed out, model and family are dependant, so you don't need to store the family in the drive record, you know what it is from the model.

One of the main reasons for holding an ID rather than the name, is that names change. You may have a family called Oak Tree, but then a new family, Oak Tree 2, is launched, and everybody now refers to the original as Oak Tree 1. If you have embedded this into your other tables, then you would have to amend every entry. If you have simply stored the ID, then you only have to change one entry in the family table.