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

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]