Normalise an unusuall relationship 2

[MattThat]

I have two entities in an other wise fantastic DB plan: Task and Estimate.

One task may have many estimates. One of those estimate must double as the selected task_to_do_list. The others become history at that point. The problem is the users require the ability to set up the estimate, change it (make a new one based on the old), and then go back to an earlier one later should they feel like it. The whole DB hinges on this ability (and the final selected estimate). The problem is the estimate has many examples of products many summeries and a handfull of other lists.

I can delete the unwanted record sets later (I think) but I need to keep track of the current estimate which might be switching all over the place. There maybe as many as 250 jobs going each with many estimates (I doubt it'll get much higher) and up to half of those will be running (ie useing one estimate to work from).

something seems very wrong and I can buy only a little more time before commiting this massive project rightly or wrongly to a set of entity relationships.

I hope that can be followed.

-Matt

 

[randysmid]

Matt,
I think you need 3 tables. Here is a listing of them, and what they will contain:
1) Task - as you indicated, this will be a top level table. It will probably contain Task_ID, Customer_ID, Original Start Date.
2) Estimate - This will have a combination key of Task_ID, and Estimate_ID. It will also have the date created attached to it, and any other info appropriate at that level.
3) TE_Products - Combination key of Task_ID(T), Estimate_ID(E), and Product_ID (hence the table name of TE_Products). The summaries are not needed here, since they can be created with a report.

FORMS:
1) Obviously, a form needs to be created that will start a Task. A command button can be added that will jump to a form that will allow the entry of a new starting Estimate. 2) With the new starting Estimate form, the user will need to select the products from a list box, which allows multiple selections. A command button is added to this form which might be called "Create Estimate Details". The code behind this command button will read the selected Products, and write the records to the TE_Product table. Here is how that code might look:
'open recordset
Set db = CurrentDb.OpenRecordset("Select * from TE_Product&quot
'declare variables needed to loop through
' selected Products
Dim varProducts as variant
' presume that list box is called lstProducts
For each varProducts in Me!lstProducts.ItemsSelected
db.AddNew
db![Task_ID] = txtTask_ID ' from form
db![Estimate_ID] = txtEstimateID 'from form
db![Product_ID] = varProducts
db![Product_qty] = 0 'initialize the quantity
db.Update
Next

In another form, you will display all the TE_Products records, and allow edits and deletes. This is also the time that the user can enter quantities. Of course, you can use code similar to this that will copy from one Estimate to another.

Make sense? Is this enough to get you started???


HTH,
Randy Smith
California Teachers Association

 

[Darrylles]

Hi,

After a quick read, would a boolean field not do this for you in the Estimate table called SelectedTask?

If one record has SelectedTask set true then ignore the others in one circumstance, bring them all back disregarding the 'SelectedTask' field when required in other circumstances.

If you're not sure - then delay you must. Don't commit to a long-term strategy unless you're absolutely sure (bearing in mind it's a 'massive project).

Re-post as many times as necessary.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[MattThat]

I know that to meet to the fullest degree user needs accross all departments I should use sugestion 1 (randysmid) but I know that No. 2 (Darrylles) is the easier to do.

Further, due to the itrative process of generating a final plan, (for the client), it would require the storage of up to 20 lots of around 30 to 50 records (erm... 600 to 1000) of which only one lot of 30(ish) will ever be used for the finnished offer.

Although the multiplyer value from Example_Of_Product may change I doubt it would be valuable enough a time saver to record so much data just to be able to see how many Square meters of swimming pool were in the first draft.

That leaves only the on-off idea of a boolean value. something that I can do eyes closed.

Thanks to both for the ideas.