How to save new form records

[aytee111]

Your explanation in post # 41 does not say anything about the BigOppDetail. Change it to include this table so that its purpose is clearly defined.

I am thinking these two as separate opportunity types

Now we are talking about opp types? Do you see how confusing it becomes? That is why you should call them by two completely separate names and don't ever refer to them as anything else. A house contains rooms which contains doors - these are all completely different items and no way can they be interchanged. Begin to think in these terms.

[sud2017]

Yes, again pardon me for jumping into forms.

For example, I am thinking that one OppDetailT contains many parts detail as shown below as Parts1,2,3, in quote form:

OppID = 123, Opp Desc = Fan Assembly
Quote ID = Q123, Customer = xyz

Part 1 (blower), qty 1, unit cost $3
Part 2 (motor), qty 1, unit cost $4
Part 3 (reducer), qty 1, unit cost $3

MarkUp = 25%
Total Price = $xxx

This is what i meant when i said SmallOpps Details Contain Many Parts. Hope i didn't confuse further.

One more question:


One detail can have many parts associated?

[sud2017]

How about this?

BigOppID 'House Construction'
which has many
BigOppDetails
which has many
SmallOppIDs (roof, floor, doors etc.)
Which has many
SmallOpps Details
that contains many
Parts/items

Your explanation in post # 41 does not say anything about the BigOppDetail. Change it to include this table so that its purpose is clearly defined.



Now we are talking about opp types? Do you see how confusing it becomes? That is why you should call them by two completely separate names and don't ever refer to them as anything else. A house contains rooms which contains doors - these are all completely different items and no way can they be interchanged. Begin to think in these terms.

[aytee111]

Getting there. Still no mention of BigOppDetails. And from SmallOppDetails (roof, etc) we have many SmallOpps Details - what are these? And this is new, one SmallOps Detail containing many parts/items. I was under the impressions from your earlier posts that small opp details were parts, but it seems these are further broken down? (picture post # 35)

[sud2017]

Got few things sorted out. Yes, SmallOppDetails are Parts, your understanding is right, sorry i was confused. Also want to verify our understanding on this - one OppID will have many Parts (or many parts belong to same OppID).

I am still a bit confused about BigOppDetailT, as to whether i need this table or not, to achieve what i am trying with BigOpps.

Getting there. Still no mention of BigOppDetails. And from SmallOppDetails (roof, etc) we have many SmallOpps Details - what are these? And this is new, one SmallOps Detail containing many parts/items. I was under the impressions from your earlier posts that small opp details were parts, but it seems these are further broken down? (picture post # 35)

[aytee111]

Your drawing in post # 35 was correct, then. If you are getting confused, stop and get what you have done straight in your mind before continuing. So far we have been progressing along the right track, this new thing which you are calling BigOpp is an add-on to it. Go back to your house example and work thru how the tables are going to be (leaving out quotes for now).

[sud2017]

After thinking for quite sometime, I feel that first I need an OppID for such large 'house construction' opps. Because this OppID (House Construction) contains few other OppIDs as roof, door etc. so possibly I need a table that can store my OppID for house construction, and corresponding other OppIDs for roof, door etc.

I possibly don't need a details table for such opps (as house construction) because I am NOT storing any value in details but only calling TotalCost values from other Opps such as roof, door etc.

If the above doesn't make sense, then i think i need some brain exercise

[aytee111]

You are definitely on the right track. Don't forget, however, to use a different word for it, not Opp.

So you will have a table that contains the ID's for "house construction". The OppID table already exists which will carry this new table's ID (is it optional or will all Opp's belong to a higher item?).

[sud2017]

Thanks for validating my direction. Some motivation

All opps wont belong to a higher item, so it is only applicable to some Opps. I will share an updated Table Design later today. Thanks again

(is it optional or will all Opp's belong to a higher item?).

[sud2017]

So I have added another Table to accommodate the big opportunities I talked about. I have given it a different name JobHeaderT to avoid confusion. Does this look stable? Thank you!

[aytee111]

There will be three options when entering a quote:
1 - by selecting a job
all OppDetailT records for this jobID will be copied to ItemsT

2 - by selecting an opp (can a quote contain more than one opp?)
all OppDetailT records for this oppID will be copied to ItemsT

3 - by selecting a part (can a quote contain more than one part?)
all data will be entered manually

Do your tables cater to each of these? How will these be handled by the table structure? Can you work back and forth along links to get the information you need - both when entering a quote and when creating reports (such as total cost)? Are fields repeated? Are fields missing?

[sud2017]

Few clarifications:-

2 - by selecting an opp (can a quote contain more than one opp?)
all OppDetailT records for this oppID will be copied to ItemsT

No, when an opp is selected from quote, it should only contain one opp (except for a job, where multiple opps are present)

3 - by selecting a part (can a quote contain more than one part?)
all data will be entered manually

I didn't plan to give this selection (of only 1 part) to user. But I want user to be able to manually add parts to existing list if an opp is selected.

Do your tables cater to each of these? How will these be handled by the table structure? Can you work back and forth along links to get the information you need - both when entering a quote and when creating reports (such as total cost)? Are fields repeated? Are fields missing?

Thinking about it.

[sud2017]

I have added JobID to OppHeaderT, OppDetailT and QuoteHeaderT tables. Not sure if it should be in ItemsT Table too. Hope I'm not way off.

1 - by selecting a job
all OppDetailT records for this jobID will be copied to ItemsT

Same changes as above

2 - by selecting an opp (can a quote contain more than one opp?)
all OppDetailT records for this oppID will be copied to ItemsT

I'm sure tables will need more updates

Do your tables cater to each of these? How will these be handled by the table structure? Can you work back and forth along links to get the information you need - both when entering a quote and when creating reports (such as total cost)? Are fields repeated? Are fields missing?

Could you please review my updated table structure. Have tried to organize it as its getting busier. Thank you very much or your time and help. Greatly appreciated.

[aytee111]

Excellent!

Quote type 3 - this was to cater for quotes where neither a job nor an opp is selected. I thought somewhere you had said this could be the case (needs clarification, affects form design only). Regardless, the ability to add additional part numbers to the quote will handle this.

The link from job to detail is extraneous, jobs do not have details - you will never enter details against a job ID.

Can you type in records into the details portion of the quote, or must it always be a valid part number? If the latter then there is no need to carry the description on the ItemsT.

Is "No. Assembly" on the QuoteHeader the quantity?

[aytee111]

The next thing is to write down the steps in the process of adding a new quote. We will use this process later on to design a form that will do all that you want.

Adding a quote:
1: (optional) user selects job ID
- show list of opps associated with this job
- run a query to add all details for this job to the Items table
- refresh the screen data
- do not allow entry of opp ID

2: if no job entered, user selects opp ID
- run a query to add all details for this opp to the Items table
- refresh the screen data
- do not allow entry of job ID

3: (optional) user changes quantity
- run an update query against ItemsT to change the quantities

4: Changes to existing quotes
- changes are allowed ....
- changes are not allowed ....

Add as much detail as you can think of so that when it comes to creating the form the decisions have already been made and you can just dive in and start doing it. You will find things missing at this stage (such as a global system table to store default values, e.g. markup pct, or a field to contain quote terms/duration) and may rearrange some things. This stage is getting deeper into the detail design. Hopefully your company already has a quote that you can use as a reference.