Need advice on the best way of altering my database.

[jdenver]

Can someone give me some advice on how to carry out the following.

I run a self-employed service company and the database I created years ago still works great, It consists of two tables: Customers and Jobs. I can see a customer and a list of jobs done for that customer.

Its just that when I created the jobs table, I only created two 'date' fields; one for 'date booked', the other for 'date completed'.

This is ok if the date booked is the call out date and the date completed is the finished date, but some calls require more than two visits and work carried out on those visits needs to be recorded.

I'm not sure how to approach this, whether I need to create more 'date' fields in the job table, the only problem with this, is I never know how many visits I'm going to make on each job. Or create a new job for each visit, an idea I'm not keen on as I would rather the visits relating to the same job be under one job number.

Thanks in anticipation.

[doughemi]

You need a join table. Create a table Jobs Worked. It will contain a Customer ID field, a Job ID field, and a Date Worked field. Relate this table to the Customer table by Customer::Customer ID = Jobs Worked::Customer ID. Relate the Jobs Worked table to Jobs by Jobs::Job ID = Jobs Worked::Job ID .

Now you can create a portal in Customers or jobs to show all jobs and the days worked on those jobs.

See the attached demo.

JobsDemo.fp7.zip

[jdenver]

Thanks for that doughemi, I can see what you've done, I'm now trying to put it into practice.