Query - Form - Table

[Tezdread]

Hi all,

Not sure if I've done the right thing but I'm part way there...

Ok, what I'm trying to achieve is this: To have a form with various text fields, some are for input but a few would be calculating what values are entered into the forst text boxes, but this data should also be stored in the same table.

So, this is what i've done so far: I created a query with a few fields (1-4)from my table and another field that calculates these fields that looks like this.

IIF([name]>=2 And [department]>=1 And [role]<=5,"Yes","No"

I then created a form based on this query, I can now enter data into the text boxes for fields 1-3 and the text box that does the calculations works a treat.

The problem is getting the auto calculated value to be stored 'automatically' in the same table.

Can anyone advise, either an alternative to what I've done, or how I can get this field saved?

 

[Mile-O]

a few would be calculating what values are entered into the forst text boxes, but this data should also be stored in the same table.

ER, no it shouldn't. If you can calculate it then you have absolutely no need to store it in a table. This is a violation of Third Norml Form that requires the elimination on non-key redundancies.

 

[Tezdread]

ummmm....

Maybe technically this isn't what I should be doing? Could you offer any advice then on how I can achieve my requirements?

E.g. I want to be able to find out if someone is eligable for a free gift offer, before this is confirmed, details would be calculated:

1. How long have they been a customer
2. Do they make regular orders
3. Are the orders over a certain amount

All three of these would give a Yes or No response. What I also need to do, is calculate how much the free offer will be for and this is the value that I need to be stored in the table.

E.g. if the score is over 5 they get a Yes with an offer of £10, if they get over a 10 they get a Yes and an offer of £15.

So, is it possible to have a text field that shows how much the offer will be for (based on the query results) and also store this value in a table?

 

[Tezdread]

hi, I'm not getting anywhere with this... is the only thing I can do is to manually input the value that is automatically calculated?

 

[Mile-O]

Could you offer any advice then on how I can achieve my requirements?

1. How long have they been a customer
2. Do they make regular orders
3. Are the orders over a certain amount

1. In days? Months? Years?
2. Define "regular"
3. What amount?

What I also need to do, is calculate how much the free offer will be for and this is the value that I need to be stored in the table.

I take it you mean discount as a free offer would be....well, free.

E.g. if the score is over 5 they get a Yes with an offer of £10, if they get over a 10 they get a Yes and an offer of £15.

Then you need a new offers table containing these limits.

 

[Tezdread]

Thanks SJ (for your help and patience)

The example I used might not of helped

I've got a solution for the query part and it's doing the correct calculations and this calculated value is showing on the Form in a text box when I enter values into the Form. (will update the attached database)

The query is:

IIF([Customer]=Y And [Score] Between 1 And 5,"Discount1" IIF([Score] Between 6 And 10,"Discount2" IIF([Score] Between 11 And 15,"Super Discount","No Discount))

The issue now is, can this outcome that is showing on the Form (whether it's Discount1/2/Super Or Declined) be stored automatically in a table Or because it's calculated when data is entered into the other fields it has to be manually entered into the table?

Thanks in advance

 

[Mile-O]

You're still missing the point. The idea of designing a database (called normalisation) is that you don't store calculated values ever in a table. These calculations are one of the main reasons that we use queries.

 

[Tezdread]

SJ, what can I say...

I sort of see the point in this and understand a bit about normalisation but even so, I would have thought this would be a requirement in so many cases...

Would it be possible using DLookup (not that I have any more experience/knowledge with this) This way the text field on the Form isn't doing the calculations as it's just getting the results from the query...But then how do I run the query...Or should I just accept that the calculated values will show in one text box and I will have to copy and paste it into another text field for it to be stored in the table.

Cheers

 

[Rich]

You're still missing the point, use a calculated field in a query, base the form on the query and your calculation will display, you can use the same query for Reports/exporting etc. there is no need to store a calculated value without a very good reason

 

[KenHigg]

Tez, If you try to store this value, anytime [name], [department] or [role] changes value(s), you'll have to build logic into your form(s) to catch it and recalculate it. Otherwise the value you store will be useless.

If you need to see it on your form, or in a report, just plug your formula in an unbound text box and let it calculate it on the fly...

Listen to SJ...
Ken

 

[Tezdread]

thanks all, this all came about because the current calculations are done in excel and I was hoping for some similar functionality...manual input it is then ;-)

 

[nikki_k_85]

Here you go.. I fixed it up using a combo box for yes and no.. let me know what you think