calculated fields cant see in the database (1 Viewer)

[isladogs]

YOU choose.
You have been given advice already.

And arnel - I assume you are referring to yourself with the 'old folk' comment

 

[plog]

You should remove all the calculated fields from your table and then replicate them in a query like so:

TotaleAdulti: [adulto]*[adulti]

That does not go in the criteria section, but the top line of the Query Design form. That will produces the calculation you need. Follow that example to replace all your caluculated fields.

 

[Dhanu]

YOU choose.
You have been given advice already.

And arnel - I assume you are referring to yourself with the 'old folk' comment

ok, guys, i will go to plog choice.

so kindly explain me what should I do now? (in the beginning)

 

[arnelgp]

why is it so hard fir tou guys hard to accept new changes in access. if this is mssql its okey to have new data type. you always advise to stay away from the new data type. it has been there for many years and i think ms has no intention of depracating them. it is there for a reason.

 

[Dhanu]

You should remove all the calculated fields from your table and then replicate them in a query like so:

TotaleAdulti: [adulto]*[adulti]

That does not go in the criteria section, but the top line of the Query Design form. That will produces the calculation you need. Follow that example to replace all your caluculated fields.

yes, thanks. You are a genius now i can see my net total in my query .great
now i have to make a new table because this table is a little bit messed up.
i hope i can manage the situation now.

big thanks from italy for every one those who helped me.

 

[Minty]

In fairness - Calculated fields have been in SQL Server for 10 years.
I use them occasionally instead of making another query on a table. Age and Service years in an employee table for instance.

 

[Pat Hartman]

why is it so hard fir tou guys hard to accept new changes in access

Calculated fields are quite limited. We can teach two techniques. Calculated Fields in tables that works sometimes or calculated fields in queries that works all the time. It is simply too confusing to newbies. When you jump in with conflicting advice, you might mention that you do not agree with the advice given so far and are offering a different approach. It is quite alright to offer an alternative but please try to not cause chaos when you do it. We're up to 26 posts in this thread that should have ended after 9.

you always advise to stay away from the new data type

Only the abomination data types like multi-valued fields. And the Append Only property of Long Text and the List/combo options for Short Text. The reason we advise against using these data types is because in some cases, you have to learn new SQL Syntax to use them, in others they mask the actual data value causing nothing but problems in queries and code and in others (hyperlink and the Append Only property), they simply violate first normal form which is so basic that it is hard to even believe that someone at MS thought it was a good idea.

None of these data types offers anything actually new. They are simply crutches for people who don't know how to write code or create queries or create properly normalized schemas. AND, most CANNOT be upsized to SQL Server should you ever need to do that.

 

[arnelgp]

what chaos are you talking about? by the time up sizing will come , the op will be fully equip by then. we dont even know do we.

 

[plog]

Please dear lord let this thread die.

what chaos are you talking about?

It is quite alright to offer an alternative but please try to not cause chaos when you do it.

He was talking to both of us, you and me. You offered one valid solution, I offered another. While both valid they were not simutaneously valid. The initial poster had to choose between the two, but he wasn't. He was actively trying to implement both our solutions, that in itself was not going to be a valid solution.

I now unsubscribe.

 

[Dhanu]

You should remove all the calculated fields from your table and then replicate them in a query like so:

TotaleAdulti: [adulto]*[adulti]

That does not go in the criteria section, but the top line of the Query Design form. That will produces the calculation you need. Follow that example to replace all your caluculated fields.

hi, i want to deduct 25% commision from quota_totale[TotaleAdulti]+[TotaleRagazzi]+[TotaleBambini]) and that answer will be the final total.

can you explain what formula and where I should put it?
thanks

 

[Mark_]

Quota_Totale: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*.75

This will give your your result less the 25% commission.

Quota_Commision: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*.25

Would return just the commission amount.

 

[Dhanu]

Quota_Totale: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*.75

This will give your your result less the 25% commission.

Quota_Commision: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*.25

Would return just the commission amount.

hi i entered above first formula to query. i got en error message like this, the expression you entered contains invalid syntax

 

[JANR]

You're window's setting for decimal seperator is most likely a comma and not a period, just replace .75 with 0,75

Quota_Totale: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*[COLOR="Red"]0,75[/COLOR]

JanR

 

[Dhanu]

You're window's setting for decimal seperator is most likely a comma and not a period, just replace .75 with 0,75

Quota_Totale: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*[COLOR="Red"]0,75[/COLOR]

JanR

it works fine thanks

 

[Dhanu]

You're window's setting for decimal seperator is most likely a comma and not a period, just replace .75 with 0,75

Quota_Totale: ([TotaleAdulti]+[TotaleRagazzi]+[TataleBambini])*[COLOR="Red"]0,75[/COLOR]

JanR

hi, above mentioned commission rate (,75) is changing agent to agent (20%,25% 0%...etc). so is there any other way to generate this query (especially commision). I mean these all calculated fields using a form or any other method?

thanks.

 

[plog]

above mentioned commission rate (,75) is changing agent to agent

You would need a new table for this. Then you would no longer hard code ,75 and instead use the field in which the commision resides.

 

[Pat Hartman]

Open the query in the QBE. In an empty column, type the name of the calculated field followed by a colon ":". Then type the expression.

Keep in mind that if any of the columns might be null, you will have to use the Nz() for each.

quota_totale:Nz([TotaleAdulti],0) + Nz([TotaleRagazzi],0) + Nz([TotaleBambini],0)