Changing Fields from One table to another

[campbellhatchard]

I have finally completed a database the has taken me 3 months to develop and refine. One of the people using the database has just brought a problem to my attention which will see me having to rewrite the whole thing.

Basically the problem is this; A field "Despatch Date" appears in the "Order Header" table. Because of fields that hang off it, we need the field "Despatch Date" to appear in the table "Order Details". Unfortunateley some 40 queries, reports and macro's hand off this field so I can't just change it easily.

A Solution

I am hoping that someone out there has had a similar problem and that there may me a simple solution such as a script I can run which will search for the Control Source "[OrderHeaderespatch Date]" to "[OrderDetailsespatchDate]"

Now that you have had an opportunity to have a laugh, I hope someone has a solution. If I can make such a silly error in table setups, I may need a bit of help running a solution.

Thanks in advance

 

[JerryDennison]

I'm going to assume that the order detail is linked to the order header record that has the date field. If so, then in those places where you need the date field linked to each order detail record, simply include the order header table with the order detail table linked via the appropriate fields. You will then have the date field available for each order detail record. You can test this by creating a query with both tables. Drag the date field from the order header table to the QBE grid then drag all the fields you need from the order detail table to the QBE grid. Run the query and you'll see the date repeated for each detail record.

 

[campbellhatchard]

Jerry,

Thanks for the tip however my problem is that the Despatch Date held in the Order Header Field relates to many entries in in the Order Details Table.

What we have found is that if one order is not despatched to one of the multiple stores we cannot change its despatch date alone as it will change all despatch dates linked to that header. I need to change what table the field Despatch Date is in so that a despatch date can be entered for each record rather than in the header.

 

[JerryDennison]

IC the problem now. This will be an issue. You must change your table structure to accomodate this. Each order detail must have it's own dispatch date. There is no easy way to do this except just do it. As for you reports/queries/etc., they will have to be changed as this is a fundamental issue not something that will be readily resolved.

 

[campbellhatchard]

Jerry,

Yes, this is what I was afraid of!! THanks for your assistance in any case

 

[JerryDennison]

You're welcome.