whats the best way forward with related tables (1 Viewer)

[micks55]

Hi all. Great forum and a few of you guys stand out. I am just a lowly idiot who puts in tons of hours and definitely does it the hard and/or wrong way. I've searched and searched but I cant see a 'correct' way to go about this so heres my problem. Its to do with a Quotations/Orders database.
Each quotation gets a record in tblQuotes and the details go into tbl QuoteDetails related on QuoteID. Only a low percentage of quotations are sucessful so the quote details are vague with about 20 fields.
When an order is placed there is about 120 more items of info needed and the original data in tblQuoteDetails must be preserved so I have tblProductionDetails also linked to tblQuote by QuoteID.
A complication is that a quotation for a Quantity = 5 has to be transferred to the tblProductionDetails as five records because each item might be slightly different even though they were quoted at the same price.
I am copying the Quote Details to the Production Details as follows and it dont feel elegant.
Button on Quote main form to...
Open a recordset (of tblProductionDetails) in code
Go to first record in QuoteDetailsSubForm
Copy the data using a loop on quantity
Move to next record on sub form
Check if last record, exit or loop.
Is that the best way? Should I make the tblQuoteDetails larger by 120 fields that will often be empty? I rather have a fat simple solution than a thin overly complex one.
One other tiny thing. I have a Main Menu form with 5 subforms. Access is always asking me if I want to save subforms that I havn't edited and sometimes just throws me out, closes Access and doesn't save my work. Could this be to do my rubbish code because there is miles of it in there.
No doubt you all think I'm nuts. Sorry if this is total rubbish. Thanks for any replys.

 

[Pat Hartman]

I'm having trouble getting past the 120 columns for each item. Can you give us some idea of what you've got there so we can determine if there are repeating groups.

 

[micks55]

Hi Pat, Yes sure. It's manufacturing of special doors. At quote stage there is only a need for simple details like oak, sizes how many leafs (could be four folding doors and how many glass openings. At production stage there is more such as lippings hinges, up to 5 openings each with high, wide, up, in (from non hinge edge) dimensions, type of glass, type of beads etc.etc. Then we move on to the frame because it could be a prehung door. Section of frame, stop or rebate, finish. And to top it all, there is fire rating and acoustic ratings. So I do think that I can't reduce the number of fields. I've just had a count up and actually the production table has 182! I'm shocked as well.
I do see that there is a case for a tblDoors with one record for each door leaf and a tblFrames with one record when there is a frame. Where I am stumped is ensuring that they remain linked and dependant on one another so that the frame or one of the doors in a set can't be deleted accidentaly. I expect it could go further and there could be a tblGlassOpenings with one record for each of them also but the same worries apply. Finanlly, I wouldn't have a clue where to start bringing all that seperated data together in order to make the Work Ticket reports.
Thanks for your time.

 

[Pat Hartman]

I'll start by answering the last question. You would use subreports/subforms for each of the many-side tables.

It is very important to start with properly structured tables. Once the tables are normalized, other things fall into place. Just keep in mind that any time you have more than one of something, you have many and many belongs in its own table with a foreign key that points back to the parent record.

 

[georgedwilkinson]

You might also want to read up on data models for Bill of Materials. I think it could help you understand how best to structure the data.

182 columns is a recipe for disaster.

 

[micks55]

Many thanks for the tips, I'll go back to the drawing board and change the stuctures. Before I go there is one other problem to do with code that is driving me nuts and I can't find this written up anywhere.
I've had to remove every instance of Me.Refresh when used in a parent form (sub forms seem ok). Every time Me.Refresh is executed in a parent form Access hangs and crashing out is the only option. For instance, in OnAfterUpdate of the DeliveryCharge field on the parent form just one line of code Me.Refresh causes the hang every time. Obviously, I have removed it but I'd prefer to know why.
In case I hear no more, once again, many thanks.