Adding a formula in data tables???? help a noob?

Basically I need to auto populate columns based on previous data in other columns. Can this be done?
Take a look at the file.

s3_1 will be populated with 1 and 2.

1 = simple quote
2 = complex quote

then take a look at s4_1

if s3_1 = simple quote and s4_1 value is 5 or less the s4_1b should equal "1" (1 = True) if more then 5 then = "2" (false)
also
if s3_1 = complex quote and s4_1 value is 12 or less the s4_1b should equal "1" (1 = True) - if more then 12 then = "2" (false)

Can this be done?

piedpiper said:

Basically I need to auto populate columns based on previous data in other columns. can this be done?

Click to expand...

Yes can be done, but shouldnt...

A simple calculated value like this should be "dynamicaly" calculated on the fly in a query... NOT stored in a table...

namliam said:

Yes can be done, but shouldnt...

A simple calculated value like this should be "dynamicaly" calculated on the fly in a query... NOT stored in a table...

Click to expand...

? - I'm a little confused. However, this whole setup is unique as the forms and data tables and how they are structure is not really important. What is, is that we plan on exporting the data from the datatable and import it into SPSS... that's where the magic happens. This is strictly a simple method of collecting data. Trust me I know I have a lot to learn about database design.

That being said, you did mention that it’s possible, any way you can guide me through it?


Thanks in advance!!!

NO I will NOT guide you through something that is BAD, WRONG and HARDER than a simple query...

Simply send the query to SPSS instead of the table... Or make a "make table query" if you (for what ever reason) need it to be a table...

A calculated value should not and will not be stored in a table ! The export is really no good reason to store it at all!

namliam said:

NO I will NOT guide you through something that is BAD, WRONG and HARDER than a simple query...

Simply send the query to SPSS instead of the table... Or make a "make table query" if you (for what ever reason) need it to be a table...

A calculated value should not and will not be stored in a table ! The export is really no good reason to store it at all!

Click to expand...

Ok, thanks... as long as I can send the values to SPSS as 1 and 2 that would be great, would they match or align with the "ID" field in the data table?

BTW usually 0 is false, not 2.. Then again usually 1 = true while anything else is false... So it shouldnt matter... but still....

Note: I dont know SPSS at all!

What do you mean "would they match or align with the "ID" field "??? I dont quite follow???
If you are asking will a 1 or 2 show up on each line with the right 1 or 2 on the correct lines?? Then yes it will... lookup the IIF function in the access help, that should get you where you want to go.

I'm lost.

I'm not a programmer or a Access database designer and I don't know VB.

All you have to do is look at the file and see how clueless I am.

However, with the support from this site I did manage to create a almost complete system where it is doing what I need...

I'm taking about rows in the table...when someone creates a new record and starts inputting data in the forms (yes I know I've made too many forms, and should have use normalization) the row with the field called "id" is constant, and data from column to column and from form to form stays in that row.

All I'm trying to do is apply the formula above.. and keep it on the active row. or should I call it current record. then I will export the datatable into excel and from there I can inport into SPSS.

That's why (if you look at the attached) I left blank columns such as s4_1b...because the 1 and 2 created from the formula needs to be aligned with the current record or active row.

I tried looking at your database, I may be a bit harsch but ... ITS A MESS... and a BIG ONE at that...

Then again you probably know better now and next time hope you do it different.

A value that depends on other values SHOULD NOT AND WILL NOT be stored in a table!!!

If you are looking for me to GIVE you an answer, you found the wrong person... I am not going to....
What you NEED to do is
1) Find out what IIF is
2) Find out what a query is

Combine the knowledge of the two and do what you want to achieve....
Ex:
Quick example for you:
Make a query, add your Main_Database table to the query.
Now paste this in the first field column:
IIFTest: IIF(s4_1 > 2; "More than 2"; "Less than or equal to 2" )

That should give you an idea...

namliam said:

I tried looking at your database, I may be a bit harsch but ... ITS A MESS... and a BIG ONE at that...

Then again you probably know better now and next time hope you do it different.

A value that depends on other values SHOULD NOT AND WILL NOT be stored in a table!!!

If you are looking for me to GIVE you an answer, you found the wrong person... I am not going to....
What you NEED to do is
1) Find out what IIF is
2) Find out what a query is

Combine the knowledge of the two and do what you want to achieve....
Ex:
Quick example for you:
Make a query, add your Main_Database table to the query.
Now paste this in the first field column:
IIFTest: IIF(s4_1 > 2; "More than 2"; "Less than or equal to 2" )

That should give you an idea...

Click to expand...

Thank you so much - I will work with this... I'll let you know if I have any questions?

piedpiper said:

I'll let you know if I have any questions?

Click to expand...

Sure I will help you if you have any problems with the IIF...

Good luck

namliam said:

Sure I will help you if you have any problems with the IIF...

Good luck

Click to expand...

Man I feel stupid... I'm such a noob and a non-programmer that I'm just not getting this stuff... I was also embarrassed about my poorly designed database. - I have been working hard all week on it, and many design choice were based on what was requested of me. I did learn a lot, and I thank everyone that has helped me make this happen. However, there is only 2 pieces left that I would love to learn and implement.


First (puzzle #1)

How to autofill formulated fields that are hidden but deposits their results in the data table? (Again, because of SPSS it needs to function this way...) is it possible?

Here is the scenario (see attachment)

In this form “p1_Form3_Special_Order” at the bottom highlighted by a big red box (near footer) there are 10 text boxes linked to the main data table. I.e. I plan on hiding these boxes when calculations work.
What I would like to have happen is:

Part one,

When a user enters a numeric figure in this field “fp1_f3_1a_Special_Order” the hidden field “p1_f3_1b_Special_Order” does the following calculation, and the Main_Database captures the result
The calculations for the field “p1_f3_1b_Special_Order” needs to perform are:
________

IF “p1_Form2a_Special_Order”, “Frame84” = a value of “1” (Simple quote (5 or less)) AND field “p1_f3_1a_Special_Order” = <5 (5 or less) then, field “p1_f3_1b_Special_Order” should calculate and result a “1” ß or “True” otherwise if sum is greater than ”5” then it should calculate and result in a “2” ß “False”

AND/OR

IF “p1_Form2a_Special_Order”, “Frame84” = a value of “2” (Complex quote (6-12) AND field “p1_f3_1a_Special_Order” = >5 but <12 (12 or less) then, field “p1_f3_1b_Special_Order” should calculate and result a “1” ß or “True” otherwise if sum is greater than ”12” then it should calculate and result in a “2” ß “False”
_______


Part Two,

When a user enters a numeric figure in this field “p1_f3_2a_Special_Order” the hidden field “p1_f3_2b_Special_Order” does the following calculation, and the Main_Database captures the result
The calculations for the field “p1_f3_2b_Special_Order” needs to perform are:
________

“p1_f3_2a_Special_Order” = 1 then, field “p1_f3_2b_Special_Order” should calculate and result a “1” ß or “True” otherwise if sum is greater than ”1” then it should calculate and result in a “2” ß “False”
________



Second (puzzle #2)

The following forms (listed below) all have optional text boxes based on a response…

Example
Yes
No –> if no, please specify: ____________________

Is there any way to hide the “please specify” text box and label for it until they select “No” from the Yes/No options. This would eliminate clutter on the form, and would prevent false data, incase user click on “Yes” then proceed to enter data in the “if no, please specify:” text box.

I think this is basic questionnaire practice, but I do not see any options to make this happen?

This situation happens in the following forms

· p1_Form1_Stock_Order
· p1_Form2b_Special_Order
· p1_Form5_Special_Order
· p1_Form6_Special_Order
· p1_Form7_Special_Order
· p1_Form8_Special_Order
· p2_Form1_Special Order

Again, I’m a noob, and ignorant to VB and database design principles. I think it’s not too bad for my very first database.

Please do not think I’m not helping myself, I have been working on this since Monday, and I’m sure most of you could have done this in a couple of hours… my plan is to finish this one quick, as it’s needed right away… then rebuild it from the ground up with a better understanding of design or normalization, and I will most definitely pick up a book or two and get right into this, although, it has got me a few headaches, I really enjoyed this… again thanks for your help!

-Pied piper!

piedpiper said:

and many design choice were based on what was requested of me

Click to expand...

I doubt the person who gave you this assignment went into details of database design. It is highly likely you were told you need this and that to go to SPSS. Design choices are always yours, never the requestor...
If the person tells you how to make your DB, let them make it themselves and save you the headache of making (sorry to be harch, but ... ) CRAP.

piedpiper said:

How to autofill formulated fields that are hidden but deposits their results in the data table? (Again, because of SPSS it needs to function this way...) is it possible?

Click to expand...

Anything is possible, it is IMPOSSIBLE that SPSS requires this...
I will not help you do this, I told you what to do... Use IIF and Query, storing depending/calculated results in a table is a NO NO anywhere anytime anywhere.

piedpiper said:

Part one,

Click to expand...

Query and an IIF...
Query and an IIF...
Query and an IIF...
Query and an IIF...
Query and an IIF...
Query and an IIF...
No matter how often you ask this same question, probably from different people too.... you will get the same answer over and over

piedpiper said:

Part Two,

Click to expand...

Query and an IIF...Query and an IIF...Query and an IIF...Query and an IIF...Query and an IIF...

piedpiper said:

Second (puzzle #2)

Click to expand...

You can set the "visible" property of the textbox to true or false.
The use the "on click" event of the yes and no buttons to alter the visibility of the textbox to what you like.

piedpiper said:

Again, I’m a noob, and ignorant to VB and database design principles. I think it’s not too bad for my very first database.

Click to expand...

You should first learn database design principles before you even consider building a database.
If you think its not too bad for your first database, well go learn the basics first. Man your trying to run 100m in 6 seconds flat, yet you cannot even crawl!

Go to the basics, first get the basics right... then worry about the rest...

piedpiper said:

Please do not think I’m not helping myself, I have been working on this since Monday, and I’m sure most of you could have done this in a couple of hours… my plan is to finish this one quick, as it’s needed right away… then rebuild it from the ground up with a better understanding of design or normalization, and I will most definitely pick up a book or two and get right into this, although, it has got me a few headaches, I really enjoyed this… again thanks for your help!

Click to expand...

WRONG WAY AROUND, though sad truth in many cases...

Excel, word those are things you can mess around with. Access requires solid knowledge before you even touch it if you want to do anything semi decent.

Without solid understanding of databases and their working...
Without "blaming" the requestor...
You can work on this till XMas 2010 and still have nothing.... go back to basics....