Form for calculation

[kiranair]

Hello guys,
I have the data in excel sheet in which few fields are entered manually , few fields have data that are having reference to another excel sheet. (something like this" ='Design Assumption'!C$21*1.4/20")

For each row that reference is going to change depending on the other fields that are entered manually!!!!! I have to lookup the reference table and select the best option

Finally it ll be a table (T) of data that contain around 25 fields in which 20 fields are having manually entered data and another 5 fields are having reference to other excel sheets.


(Referred excel sheets are having some constant values ). This table(T) is used for calculation

Since my data has grown to such a extent , if I want to edit and save it takes 10 min
so this is my situation!!!!!!

To overcome this I am creating a database in Access.

To enter data into the table I have created a form, but am not getting idea how to enter the referred data into the form ???

Totally I have stuck here!!!
Please need your help!!!

[ranman256]

forms can only access 1 table, so youd have to bind the form to a query.
The query can combine 2 tables AND show the calculated fields.

[CJ_London]

not getting idea how to enter the referred data into the form

Access is not a big excel, it is completely different. Excel stores and calculates data in sheets, access stores data in tables and calculates in queries and/or forms or reports. Excel data tends to be 'wide and short', Access tables are 'tall and narrow'. Excel repeats values on each line, Access stores it only once. Excel uses ranges (e.g. C21) Access uses names, Excel tends to reference the same row, Access looks down a table, etc

It sounds like you have a complex requirement so if you need more detailed help, provide some examples of what you have in Excel (input data, calculation data, required result) and provide names for the columns. You might also want to google 'normalisation' to understand how tables should be constructed and related to each other. In brief it means a) data is stored only once b) calculation results are not stored and c) data is stored vertically (e.g. year1, year2, year3 would each have its own column in Excel, in Access they would each have their own record (row) with an additional field to denote year).

[kiranair]

This is my example data
Parameter Value ,Parameter Legend and shaded columns are my referred fields

[kiranair]

Parameter Value ,Parameter Legend and shaded columns are my referred + calculated fields fields

[CJ_London]

so what is in the design assumption sheet?

[kiranair]

this is my assumption sheet

[CJ_London]

OK - so what is the basis of selecting total surface area for T stringer for row 14 in the first worksheet?

[kiranair]

Basically its a reverse Process , First we select the options available in assumption table like which of these T-stringer , Ω-Stringer ..........we have to build.
Based on the assumption we ll proceed in the first table

[CJ_London]

you've lost me. I can see you are not using excel to derive which column and which row from the assumptions table to use in your parameter value column. I'm asking how you decide? what are your thought processes? what other data do you refer to? what differentiates the value you have selected in row 14 of the techno sheet with the other rows?

is it the fact it is tool preparation so use total surface area or some product info I haven't seen?

[jacquienta]

forgive my ignorance but cant you just import an excel sheet into the database under the External Data label??

[kiranair]

Sorry for responding late!!!!

Basically its a reverse Process , First we select the options available in assumption table like which of these T-stringer , Ω-Stringer ..........we have to build.
Based on the assumption we ll proceed in the first table

sorry its totally wrong !!!!

Basically I start entering the form with Generic Process, Process step, Parameter unit, Parameter quotient
Till here I ll enter manually

When it comes to Parameter value (Assumption sheet)


it should ask me only for the first time which of these (

Upper Cover with T-Stringer

Upper Cover with Ω-Stringer

Lower Cover with T-Stringer

Lower Cover with Ω-Stringer

Z-Upper Cover with T-Stringer

Z-Upper Cover with Ω-Stringer

) have to select, once the selection is made , next it should ask
(Assumption sheet) column B complete (Skin configuration............) I have to select one option, then the value related to it has to be entered into my form

In simple words C21 in assumption sheet or C18 likewise..

Once C21 or anything that has been selected, user ll tell ok c21 has to be multiplied or divided with some number , then final result has to be inside my form!!!!!!!!!

[CJ_London]

OK, not sure how 'has to be multiplied or divided with some number' is defined, but otherwise from that description I think you need a number of tables:


tblGenericProcesses
GenProcPK autonumber
GenProcDesc

[CJ_London]

ignore last post - hit the wrong button

OK, not sure how 'has to be multiplied or divided with some number' is defined, but otherwise from that description I think you need a number of tables:

first some lookup tables

tblGenericProcesses
GenProcPK autonumber
GenProcDesc text (i.e. tool prepration, skin layup)

tblProcessSteps
ProcStepPK autonumber
GenProcFK long - links back to tblGenericProcesses - I assume the steps are unique to a process
ProcStepDesc text (i.e. cleaning by laser)

don't know about parameter quotient and unit, I assume not

then you need some more lookup tables for configuration and measures in assumptions

tblConfigurations
ConfigPK autonumber
ConfigDesc text (i.e. skin, stringer)

tblMeasures
MeasurePK autonumber
ConfigFK long - links back to tblConfigurations - I assume the steps are unique to a process
MeasureDesc text (i.e. material type, material density)


and perhaps you need one for stringers as well

tblStringers
StringerPK autonumber
StringerDesc text (i.e. upper cover with T-stringer)


now moving to assumptions, the table would be something like

tblAssumptions
AssumptionPK autonumber
StringerFK long - links back to tblStringer
ConfigFK long - links back to tblConfig
MeasureFK long - links back to tblMeasures
Item text - this field has a mixture of text and numbers so have defaulted to text, may be more appropriate to have a separate field
Units text - could have a lookup table for this as well, might be useful for also storing conversion factors?

We can now move the techo data

tblTechno
TechnoPK autonumber
GenProcFK long - links back to tblGenericProcesses
ProcStepFK long - links back to tblProcessSteps
ParameterUnit - text
ParameterQuotient - number (double or integer?)
AssumptionFK - long link back to tblAssumptions and bound to
AdjustmentValue - double - this is the number from your 'has to be multiplied or divided with some number'
ParameterLegend - text - don't know where this fits in
sure there is more


So with your input form, based just on tblTechNo - and I would suggest a continuous form or datasheet view

have bound comboboxes to select values for

GenProcFK rowsource would be 'Select GenProcPK, GenProcDesc FROM tblGenericProcesses'
ProcStepFK - rowsource would be 'Select MeasurePK, MeasureDesc FROM tblMeasures WHERE GenProcFK=[GenprocFK]'


then an unbound combo called say cboStringers with a rowsource of 'SELECT StringerPK, StringerDesc FROM tblStringers'

then another bound combo
AssumptionFK - rowsource would be 'SELECT AssumptionPK, ConfigDesc, MeasureDesc, Item, Units FROM (tblAssumptions INNER JOIN tblConfig ON tblAssumptions.ConfigFK=tblConfigs.ConfigPK) INNER JOIN tblMeasures ON tblAssumptions.MeasureFK=tblMeasures.MeasurePK WHERE StringerFK=[cboStringers]'

next you need text boxes for

ParameterUnit
ParameterQuotient
AdjustmentValue
ParameterLegend

you will also need an unbound text field for the calculation

txtParameterValue - control source would be =[adjustmentvalue]*val([assumptionFK].column(3))

other calculations would be based on a similar principle

That's about as far as I can take it - hope it meets your requirements

[kiranair]

Thanks a lot !!!