Displaying data from one table to another (sort of)

[davesfx]

Hi guys, here's the background:


We run a repair business and have been using paper based/spreadsheet methods to log each items progress. There are several distinct stages to the process and Access will help us input data and generate reports more easily.

1. Item booked in (Customer's details, Item details, Fault Description details and date) Unique Repair Ref generated
2. Item destination decided (Repair ref tied to a destination code - in house, engineer, manufacturer etc)
3. Quoting (Customers details displayed from previous stages and part numbers, cost and labour added)
4. Item returns (completetion date, collection status and collection date)

Have stored each stage in a seperate table so as not to duplicate data, however when designing the forms for the staff to input the data, i'm struggling to get the data from the first table to display in the form as no entry exists in the Quote table.


The 'New Quote' button runs a query to display item details based on the entry in the Book In table. I think I need it to pull up the data from the first table and create a new entry in the Quotes table using the Repair Ref as the primary key. Any ideas?



Thanks in advance

David

[Rainlover]

Each Table should have a Primary Key which you have done correctly.

But in a One to Many Relationship one of the Tables must have a Foregin Key.

If using AutoNumber and have Records 1-10 in one Table you then could have the same Numbers Many Times as the Foreging Key in the Next Table. Data Type Long.

So One Customer could have Many different pieces of equipment.

[davesfx]

Thanks for such a quick reply. I have set the relationships to 1 to 1 on purpose as each item is tracked by a unique repair ref. If the same customer comes back with the same piece of equipment for a re-repair or service, a new repair ref is issued for that trip through the system.

I'm still stuck with my update problem when information regarding a repair becomes available.

Dave

[Rainlover]

You need to do some research.

1 to 1 is not the correct approach. If it were then all could be done in ONE Table.

[davesfx]

I shall go back to the drawing board

Cheers for your help anyway!

D

[Rainlover]

It would appear that you do not like what I wrote.

I am sorry but there is no sugar coated way to tell you the facts.

The only One to One Relationship I have ever used is to add a Memo Field. This is done as Memo fields are prone to corruption.

Your design does not fit this scenario.

Do your research; look at other databases, like the templates as offered by Microsoft and you will soon discover that One to One is incorrect.

[davesfx]

It would appear that you do not like what I wrote.

I am sorry but there is no sugar coated way to tell you the facts.

The only One to One Relationship I have ever used is to add a Memo Field. This is done as Memo fields are prone to corruption.

Your design does not fit this scenario.

Do your research; look at other databases, like the templates as offered by Microsoft and you will soon discover that One to One is incorrect.

On the contrary, I am very grateful for your insight and have already amalgamated all the required fields into one table. I used the seperate tables in an effort to make validation and reporting easier later on but I can work around that in other ways.

Please do not feel that I am not appreciative.

Thanks (honest!)

D