Calculated field as table field?

[Guest]

I have a calculated field that I would like to have as a field in a table.
The calculation refers to fields in different tables and if I try to build a
form & subform from a query this becomes problematic.
My calculated field looks like this;
RoyaltyCalculation:
Switch(IsNull([Category]),0,[Company.Company]="Ackermans",[Sales]*0.08,[Company.Company]="Barloworld",[Sales]*0.1,[Company.Company]="Little
Number/Prestige",[Sales]*0.12,[Category]="Food",[Sales]*0.04,[Category]="Health & Beauty",[Sales]*0.06,True,[Sales]*0.13)

Any suggestions welcome.
Thanks.

 

[Douglas J Steele]

What problems are you having using the query as your recordsource?

That really is the proper way to do it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a calculated field that I would like to have as a field in a table.
The calculation refers to fields in different tables and if I try to build a
form & subform from a query this becomes problematic.
My calculated field looks like this;
RoyaltyCalculation:
Switch(IsNull([Category]),0,[Company.Company]="Ackermans",[Sales]*0.08,[Comp
any.Company]="Barloworld",[Sales]*0.1,[Company.Company]="Little

Number/Prestige",[Sales]*0.12,[Category]="Food",[Sales]*0.04,[Category]="Hea
lth & Beauty",[Sales]*0.06,True,[Sales]*0.13)

 

[Guest]

You mean if I create the form using the query?
If so, using the wizard I am unable split the form so that I have a subform.
So I end up with each record on a separate page. I want, for example, many
royalsty calculations in a subform per company.
Am I making sense?

What problems are you having using the query as your recordsource?

That really is the proper way to do it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a calculated field that I would like to have as a field in a table.
The calculation refers to fields in different tables and if I try to build a
form & subform from a query this becomes problematic.
My calculated field looks like this;
RoyaltyCalculation:
Switch(IsNull([Category]),0,[Company.Company]="Ackermans",[Sales]*0.08,[Comp
any.Company]="Barloworld",[Sales]*0.1,[Company.Company]="Little

Number/Prestige",[Sales]*0.12,[Category]="Food",[Sales]*0.04,[Category]="Hea
lth & Beauty",[Sales]*0.06,True,[Sales]*0.13)

Any suggestions welcome.
Thanks.

 

[John Vinson]

I have a calculated field that I would like to have as a field in a table.
The calculation refers to fields in different tables and if I try to build a
form & subform from a query this becomes problematic.
My calculated field looks like this;
RoyaltyCalculation:
Switch(IsNull([Category]),0,[Company.Company]="Ackermans",[Sales]*0.08,[Company.Company]="Barloworld",[Sales]*0.1,[Company.Company]="Little
Number/Prestige",[Sales]*0.12,[Category]="Food",[Sales]*0.04,[Category]="Health & Beauty",[Sales]*0.06,True,[Sales]*0.13)

Any suggestions welcome.
Thanks.

Rather than using a monstrous (and ill-formed, those should certainly
be [Company].[Company] instead of [Company.company]) Switch statement,
perhaps you should consider adding a three field table with fields
Category, Company, and Royalty; you could either Join this table to
your query, or (perhaps better on a Form) use DLookUp:

RoyaltyCalculation: [Sales] * DLookUp("[Royalty]", "[Royalties]",
"[Category] = '" & [Catgory] & "' AND [Company] = " & [Company])

This will let you add new companies, new categories, change royalties,
etc. without having to get in and change the deeply buried code.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Am I making sense?

Not really. <g>

Try John's suggestion.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You mean if I create the form using the query?
If so, using the wizard I am unable split the form so that I have a
subform.
So I end up with each record on a separate page. I want, for example, many
royalsty calculations in a subform per company.
Am I making sense?

What problems are you having using the query as your recordsource?

That really is the proper way to do it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a calculated field that I would like to have as a field in a
table.
The calculation refers to fields in different tables and if I try to
build a
form & subform from a query this becomes problematic.
My calculated field looks like this;
RoyaltyCalculation:
Switch(IsNull([Category]),0,[Company.Company]="Ackermans",[Sales]*0.08,[Comp
any.Company]="Barloworld",[Sales]*0.1,[Company.Company]="Little

Number/Prestige",[Sales]*0.12,[Category]="Food",[Sales]*0.04,[Category]="Hea
lth & Beauty",[Sales]*0.06,True,[Sales]*0.13)

Any suggestions welcome.
Thanks.