Carefull Planning (Re-Designing DB to Access 2003)

[Patentinv]

I’m re-designing an access 97 db to Access 2003 and adding a few changes. I’m using option buttons (But I’m not sure if these are the correct object boxes for what I’m trying to accomplish) on my form in Access 2003 to calculate a bid/estimate for possible customers. There are approximately 5-6 option buttons and each one will define a different part of a roof. Only one option button should be able, to be chosen at a time. (This is why I’m concerned that option buttons are not the right choice.) For instance Valley, Ridge, Hip are the names of some the option buttons. When the user makes his choice by selecting one of the option buttons he will need to input his numbers into 2 text boxes we’ll call these: text box 1 and text box 2, these two text boxes will be multiplied or added together for a total.

For example: Lets say option button (Ridge) is chosen, the user will enter his numbers into text box 1 and into text box 2. For instance: (text box 1 he enters 23) + (text box 2 he enters 10) and hits enter, when the user hits enter these values that were entered into text box1 and into text box 2 will need to show in another box (maybe) a list box we’ll call it (item details box) and added together for a total. For instance (23 + 10 = 33) this is what should show up in the (item details box). Let’s say the user enters more values into text box 1 and text box 2 with the Ridge option box still chosen. Lets say he enters (10 into text box 1) + (20 into text box 2) (10 + 20 = 30) is what should show up in the (item details box).
The item details box will show all the numbers of text box 1 and text box 2 and the total of the two, but it will only show the numbers for the option box that is chosen. This item details box will then have a total at the bottom of it that adds all the totals of each entry shown in it. For example:
23 + 10 = 33
10 + 20 = 30

Total = 63

After the user hits enter text box 1 and text box 2 will need to clear to either blank or zero waiting for another entry of numbers until the user has completed his input of numbers into the chosen option box in this case (Ridge). Then he will choose another option box and perform the same routine again.

Is access capable of performing this task? My big problem is how will the 2 text boxes, text box 1 and text box 2 that the user will be inputting the numbers into, be able to store/save these numbers into separate fields/columns, besides the fields/columns that they have as there control source? Unless there is a way to have the text boxes change there names each time the user hits enter and subsequently change their control source. Allowing them to save their numbers in a separate field/column with each entry. Is there any other type of box that will handle this function?

Also each time the user selects a different option button the two mentioned text boxes will subsequently have to change to a different control source one that is associated with the option button selected, And the numbers listed in the item details box will have to clear allowing new numbers to be stored, but not before storing/saving the total of these numbers in it.

I also have to consider how I’ll get the numbers to display in the other box called the item details box, after each entry. I was thinking maybe the Dlookup command would work for this function?

I’ve been reading about how to use careful planning. That is the intention of these questions.

I realize this is a lot of questions and quite a big project.

Thanks--Any help would be very much appreciated.

 

[TheAceMan1]

How are ya Patentinv . . . . .

The easiest approach I see so far is a subForm based on a table (many side) with the following fields:
[ol][li][purple]PrimaryKey[/purple][/li]
[li][purple]ForeignKey[/purple] to parent table.[/li]
[li][purple]GroupID[/purple] for [blue]tagging group calculations.[/blue] Updating can be triggered by a button to set the new (next higher) default.[/li]
[li][purple]Opt[/purple] as text datatype. This holds the options that will be [blue]selected from a bound (single column) combobox[/blue] instead the option group.[/li]
[li][purple]Text1[/purple] as Long Iteger[/li]
[li][purple]Text2[/purple] as Long Iteger[/li][/ol]
With this, the [blue]user enters data directly into the subform.[/blue] Since calculations are not normally stored in a table, this is all you'd need. Saving is no problem in this way and [blue]the GroupID allows you to select any group you like for display.[/blue]

As a first shot at this, I see the table & subform alot easier and less time consuming then what appears to be a sizable amount of manipulative code.

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Patentinv]

Thanks Aceman1, I like the way it sounds, sure hope it will work the way i planned on. I have a couple questions.

I don't understand how or where the numbers that the user inputs into the text boxes gets shown? You mention the GroupID allows you to select any group you like for display.
Can this be done on the fly, for instance each time the user inputs there numbers into the 2 text boxes can they be instantly shown in another box and totaled, for the users viewing, this way the user can see what numbers they have entered and see how many they have left to enter with out missing any. These numbers will also have to be totaled.

If they cannot be viewed instantly when they are inputted, when the user does decide to view them to see if they have not missed any numbers, how will they be shown, will they be shown as just numbers side by side without any operands (+, x) and also how will this GroupID be able to sort the various numbers into groups according to each different choose that is chosen in the combobox, and displayed accordingly to each chose in the combobox.

For instance if Ridge is chosen in the combobox and the user wants to see all the numbers he has entered into ridge, how will this be shown and where, in what type of box? Also will the user have to click like a command button to make this happen?

Sorry about all the questions, I’m just concerned it might not work like I might hope it will.

Is it possible to store calculations in a table with the operands showing (+, x). It is kind of important that the user knows how the numbers they input are being totaled.

This program will be generating estimates/bids any where from $3,000.00 up to $50,000.00. Depending upon the type of roof and the size of it. So the input of the numbers into the text boxes is the key to the whole bid. If this is not inputted correctly the estimator could really loose a lot of money.

Thanks Aceman1--I really appreciate your help.

 

[TheAceMan1]

Patentinv . . . . . .

Realize there's alot under the hood we don't know about. This makes answering your questions a little ambiguous (guessing). So, in the interest of [blue]Carefull Planning,[/blue] have a look at the following:

Fundamentals of Relational Database Design

Normalizing Tables

. . . and be sure to include the link provided by [blue]PHV[/blue].

Its imperative you fully understand these links now and for all future purposes. These links are the heart & soul of any DB! and dictate how easy/hard the design will be. A single change here can cause redesign of the entire DB. So get your favorite beverage and really read these guys.

Is any case, some idea of tables & relationships would be a great help here. This should become evident as you read the links.

See Ya! . . . . . .

 

[Patentinv]

Hi Aceman1, I understand the impact a change can make thats ok. I'll work around it. I need to know if the design you suggested using the subform will work. Here's the same questions again. Do you see away I cam make this work?

I don't understand how or where the numbers that the user inputs into the text boxes gets shown? You mention the GroupID allows you to select any group you like for display.
Can this be done on the fly, for instance each time the user inputs there numbers into the 2 text boxes can they be instantly shown in another box and totaled, for the users viewing, this way the user can see what numbers they have entered and see how many they have left to enter with out missing any. These numbers will also have to be totaled.

If they cannot be viewed instantly when they are inputted, when the user does decide to view them to see if they have not missed any numbers, how will they be shown, will they be shown as just numbers side by side without any operands (+, x) and also how will this GroupID be able to sort the various numbers into groups according to each different choose that is chosen in the combobox, and displayed accordingly to each chose in the combobox.

For instance if Ridge is chosen in the combobox and the user wants to see all the numbers he has entered into ridge, how will this be shown and where, in what type of box? Also will the user have to click like a command button to make this happen?

Sorry about all the questions, I’m just concerned it might not work like I might hope it will.

Is it possible to store calculations in a table with the operands showing (+, x). It is kind of important that the user knows how the numbers they input are being totaled.

This program will be generating estimates/bids any where from $3,000.00 up to $50,000.00. Depending upon the type of roof and the size of it. So the input of the numbers into the text boxes is the key to the whole bid. If this is not inputted correctly the estimator could really loose a lot of money.

Thanks Aceman1--I really appreciate your help. Thanks again!

 

[TheAceMan1]

Patentinv . . . . .

Sorry to get back so late. This will work, but its going to take an SQL with dynamic criteria. Example:

The user hits a button that starts a new calculation group. The GroupID [blue]DefaultValue[/blue] is incremented to the next higher value and [blue]remains until the button is hit again.[/blue] The SQL criteria for GroupID is changed to:

[blue]Forms!MainFormName!subFormName.Form!GroupID[/blue]

This sets the subform to show only the current group calculation. Upon updating the recordsource of the subform, it appears as follows:

[blue][tt]GroupID Option Text1 Text2 subTotal
        Combo              = (Text1 + Text2)
******* ****** ***** ***** ********
   10 

SubfomFooter                Total = Sum(subTotal)
                            *****[/tt][/blue]

The user then simply enters the data

[blue][tt]GroupID Option Text1 Text2 subTotal
        Combo              = (Text1 + Text2)
******* ****** ***** ***** ********
   10   Valley  23    10      33
   10   Valley  10    20      30
   10   Valley  55    22      77

SubfomFooter                Total = Sum(subTotal)
                            *****
                             140[/tt][/blue]

A new group calculation of is instantiated by hitting the button.

An unbnound combobox with the group ID can be used to view any group via the criteria for GroupID changed to look at that ID selected.

Note: GroupID can be a name to more easily identify groups.

See Ya! . . . . . .

 

[Patentinv]

Hi Aceman1, This looks really good? Wow Iron point SQL is expensive. Maybe I'll scrap this approach, for time being and I'll make it a little easier and accomplish the same thing.
Imagine this approach I'll have 10 sets of text boxe's called measurements each set will conatin 3 text boxes textbox1, textbox2 and textbox3.

Textbox1 will be be multipled(x) or added(+) to textbox2, and the total will show in textbox 3. I'll call this set of 3 text boxes measurement1. I'll have 10 measurements 1-10.
The 2 text boxe's that are added or multiplied together will have a control source, bound to a field/column on the forms bound table to store/save the numbers, unless a sub form will work better, and the total text box will have code like this in it(=[Meas1]*[Meas2]) for a total. then I'll have another text box called sum total that will just add all the totals of each set for a sum total.

My program will need to use the total sum text box for figuring the amount of materials needed And to figure material costs. The other text boxes are irrelevant except for to give data to the total sum box, besides allowing the users to see the values they input.

My question is this, Is there a way that I can use the same 1-10 measurement sets for each option. Ridge, Hip, Starter, Valley, Field listed in combo or option box. Weather its through a sub form or using columns on forms bound table or even possibly a frame.

How will I have these 20 text boxes that the user enters there numbers into change its control source so it can save the new numbers in a different field/column for each of the Ridge, Hip, Starter, Valley, Field listed in combo or option box.

Thanks--Any suggestions would be very much appreciated.

 

[TheAceMan1]

[blue]Is there a way that I can use the same 1-10 measurement sets for each option.[/blue]

1st . . . because you'll certainly be using a [blue]continuous subform[/blue] to log the measurements per option, your Idea of 1 to 10 measurements is ambiguous as [blue]the subform will show as few or as many measurements the user has entered.[/blue]

Now . . . [blue]since you want seperate calculations per option[/blue], this is indicitive of an additional table, [blue]Options[/blue]. This table will be on the one side of a relationship to a table holding the calculation data (the many side and source for the subform). This would make it easy to extract the calculation data at a later time.

The problem you face is how to fit/relate the Options table in your table schema. Since you've stated you'll be summing the grand total of all options, I'm assuming all the options as a group are related to say . . . a Job. If you have the equivalent of a Job table, then this would be the place to relate tables (Jobs/Options).

[blue]How will I have these 20 text boxes that the user enters there numbers into [purple]change its control source so it can save the new numbers in a different field/column[/purple] . . .[/blue]

Your knowledge of tables & relationships is showing here. The calculation data [blue]via table relationships[/blue] is always stored/viewed properly. Your going to have something like this:
[ol][li][blue]MainForm[/blue] (equivalent to jobs)[/li]
[li]Subform [blue]Options[/blue] on MainForm.[/li]
[li]Subform [blue]CalculationData[/blue] on subform Options[/li][/ol]
Get the picture! . . . So when you change Options, the proper CalculationData follows along.

Your thoughts? . . .

See Ya! . . . . . .

 

[Patentinv]

Hello Aceman1, Sorry about not responding right away. I've been reading about tables and there relationships. To better understand database design. Hopefully my examples of tables and there relationships as they relate to my database make sense.

(PrimaryBid_Master) Table, will be the Main Forms record source, also called (PrimaryBid_Master/form). PrimaryBid_Master will have a primary key called
(Invoice#. Auto generated) this table will have many other fields/columns related to each job/estimate given.

(PrimaryBidSub _CalculationData) Table, will be the Sub forms record source, it is also called (PrimaryBidSub _CalculationData/form) This will be a subform of (PrimaryBid_Master). With two foreign keys (Invoice#) with a one to many to Primarybid_Master, one being on primarybid_master side, many being on the PrimaryBidSub _CalculationData side, and (GroupID) With a one to many relationship to CalculationDataSub_Options one being on CalculationDataSub_Options side many being on PrimaryBidSub _CalculationData side.

PrimaryBidSub _CalculationData Table
Invoice# GroupID Text1 Text2 subTotal
(Foreign Key)(Foreign Key) = (Text1 + Text2)
******* ******* ****** ***** ********
1 10 23 10 33
10 10 20 30
55 22 77
SubfomFooter

Total = Sum(subTotal)
***** 140

(CalculationDataSub_Options) Table, will be the Sub forms record source also called (CalculationDataSub_Options/form) this will be a sub form of
PrimaryBidSub _CalculationData Primary key (GroupID).

CODE
GroupID Option
(Primary Key)
******* ****
10 Valley
11 Starter
12 Field

Overview of how the sub forms will work.
(PrimaryBidSub _CalculationData) sub form is bound to
(PrimaryBidSub _CalculationData) Table On this sub form there will be (text box1) and (text box2) for entering measurement numbers into, there (control source) this is where these numbers will be saved/stored is in the (PrimaryBidSub _CalculationData) Table in the field’s text1 text2.
There will also be a combo box called options it’s row source = (CalculationDataSub_Options) Table, which will display all the options, and its control source = (GroupID) a Field/column in the (PrimaryBidSub _CalculationData) table this is where it saves the options GroupID that was chosen.

Questions
When the user enters the numbers into the two text boxes and hits enter what will happen? Will the text boxes clear for the next numbers to be entered? If so, how will they be shown? Will they be shown on the sub/sub form? And if so, how? This has got me confused.

Quote: Since you've stated you'll be summing the grand total of all options.
I won't need a grand total sum of all the options. Just a total of each individual option. I hope this makes it easier.

I'm currently reading about subforms trying to figure out how to do all this, I don't understand continious subforms.

I’m sorry if I can’t quite understand all of this, I’m really trying.


Thanks--Aceman1 For your time and help, I really appreciate it!!!