Re: Is it possible to start again with old data?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Sat, 08 Nov 2008 19:50:54 +0000, Bob H <bob@xxxxxxxxxxxxx> 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]
.

Relevant Pages

  • Re: Query Trap!
    ... How are you retrieving the primary key value from the first append query ... it runs two append queries. ... Then if I choose another control, ...
    (microsoft.public.access.queries)
  • Re: Update Query
    ... This is what you posted in .queries: ... Create an append query whose source is your present Lotto table, ...
    (microsoft.public.access.modulesdaovba)
  • Re: moving entries in a table
    ... You do need two queries to run to do the Append and then the delete. ... You need to be careful that the DELETE query only removes records that have ... and link any tables containing the Foreign Key fields to your Historical ...
    (microsoft.public.access.queries)
  • Re: Bulk Update To Db Tables
    ... above that have lots of duplicate entries. ... I guess the Update Query would do the trick? ... As to whether to use append or update queries, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Automating a database table update
    ... accomplishes the same end result as deleting the table and recreating it ... if you've read the Macros section of the Access Bible, ... Append queries won't update ...
    (microsoft.public.access.macros)