Late Database Design - Problems

[Yesideez]

Hi,

A friend has a business and has asked me to help modernise things a little and although I've been using MySQL databases for almost 10 years Access is new and I'm not sure what would be the best course of action here.

He currently has a database with two tables:

properties
6,687 records
35 fields



worklog
12,828 records
22 fields

Currently don't have a relationship (he set this database up himself when he first started his business several years ago and didn't know about relationships) but along with normalising the data and linking worklog to properties using Property ID I'm not sure what would be the best and possibly easiest way to go about "fixing" all his current data.

The big problem is, as he's been using this database over the years when he's wanted to link a property with the maintenance work logs he's manually used "Find" to find them using postcode and house name/number - VERY tedious as you can imagine.

The property address in the properties table is currently stored in one memo field - I'm wanting to split this into address (memo) and postcode (text) and build the relationship by Property ID but not sure what would be the best way to do this.

The only way I can think of so far is to export to MySQL and write a PHP script to run through and re-populate two new tables (as they're wanted in the new Access database then import into Access.

Is there an easier way than this?

[pkstormy]

What are all the fields in each table that could be used to create a concatened key that would match in both tables? Typically if you want to link on a field, you don't want to make it a memo type (difficult to do a join on).

I would find 2 (or 3 or 4 fields) in each table (that are in both tables) which when they are concatenated together, would create a primary type unique key to identify that record for both tables (this new concatened field would be a text type field). Run a query on each table to then concatenate the 2 (or 3 fields together). Sometimes taking the first 3 or 4 characters of a field helps making the concatenations unique also.

For example: Say address and zip code (ie. Postcode) are in both tables. Create a field called AddressZipCode in both tables and take all or the left 5 characters of address and zipcode and combine them in an expression in a query to update the AddressZipCode field in both tables. Again, if need be, use 3 fields or 4 fields to make up the unique concateneted key consistent in both tables.

In the 'Main' table ("Properties"), you'll then have a PropertyID (autonumber type) - (create the PropertyID autonumber if you don't have one in the Properties table). Create a PropertyID field (integer type) to the Worklog table. Now match the 2 concatenated fields to then populate (update) the PropertyID field in the Worklog table. When done, then run an unmatched query against PropertyID in both tables to find which ones in the Worklog table that still didn't match (some may need to be updated manually if the concenated field didn't produce a unique match.)

Establish relationships once all PropertyID values are populated in the Worklog table and then setup a form/subform so that new entries will populate PropertyID as it should.

Example when done:
Properties table:
Address........PostCode............NewConcat...... ....PropertyID (autonumber)
4 W Oak.........55522..............4 W Oak55522...........1
23 N Brook......55523..............23 N Brook55523........2
12 Some.........55533.............12 Some55533...........3

WorkLog
Address........PostCode............NewConcat...... ....PropertyID (integer)
4 W Oak.........55522..............4 W Oak55522...........1
23 N Brook......55523..............23 N Brook55523........2
12 Some.........55533.............12 Some55533...........3

After you've gotten the correct PropertyID for the relational table (ie. Worklog), you can then delete the concatenated fields.

[Yesideez]

Thanks for your detailed reply but I've managed so solve it.

Once I managed to export from Access to MySQL I first normalised the data (2 unlinked tables became 6 tables) and then the mammoth task of writing a PHP script to pick out the matching data from properties table depending on the search critera from the worklog table (what they were doing manually before) and I've linked the two original tables by means of the property ID field.