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 |
#1
|
|||
|
|||
Is it possible to start again with old data?
I have built a tool inventory database in access 2007.
I admit I dind't plan it very well, as I just built tables for each type of tool we use; 9 in total, with no actual relation between any of them. The fields for each consist of: Manufacturer Product Range or Drive (Tool type dependant) Increment (again same as above) ManufactureSerialNo OtherSerialNo AssetNo LastTestDate NextTestDate CertificateNo Location Notes Now I would like to build this as a *proper* database and not someting that resembles excel spreadsheets. The forms and queries are there for each table as well. I can't have a PK in every table for any of the said fieldnames because some are either blank or duplicated. The only way I could see to have a PK for each type of tool would be to use AutoNumber each record. Anyway, I would be grateful for ideas on what would be the best way to improve this database I have built, with nearly 2000 records. Thanks |
#2
|
|||
|
|||
Is it possible to start again with old data?
On Sat, 08 Nov 2008 19:50:54 +0000, Bob H wrote:
I have built a tool inventory database in access 2007. I admit I dind't plan it very well, as I just built tables for each type of tool we use; 9 in total, with no actual relation between any of them. The fields for each consist of: Manufacturer Product Range or Drive (Tool type dependant) Increment (again same as above) ManufactureSerialNo OtherSerialNo AssetNo LastTestDate NextTestDate CertificateNo Location Notes Now I would like to build this as a *proper* database and not someting that resembles excel spreadsheets. The forms and queries are there for each table as well. I can't have a PK in every table for any of the said fieldnames because some are either blank or duplicated. The only way I could see to have a PK for each type of tool would be to use AutoNumber each record. Anyway, I would be grateful for ideas on what would be the best way to improve this database I have built, with nearly 2000 records. Thanks BUild a new Tools table, with an autonumber Primary Key and all these same fields. Run nine Append queries to append the data in your subtables into the Tools table. You might want to add one more field, ToolType; your Append query for (say) drills could have ToolType: "Drill" in a vacant Field cell to append into the ToolType field. You'll then need to pick one good instance of each of your nine forms and nine (or more) queries and modify it to accommodate the new normalized table. Once everything is working nicely you can delete the nine tables and compact the database to free up the space they occupied. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|