Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculated field as table field?

Discussion in 'Information Technology' started by Graeme at Raptup, Jul 28, 2006.

  1. Graeme at Raptup

    Graeme at Raptup
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    India Reading Calculated Banis Breaking News Mar 6, 2013
    Canada Huddersfield Sikh community leader Bakhshish Singh Bhullar dies... Breaking News Jan 27, 2014
    The ‘Flying Sikh’ on India’s lamentable track-and-field prowess Sports & Fitness Oct 4, 2010
    India India Can Never Beat China in Any Field Breaking News May 22, 2010
    US Defense Department Hiring Resident Aliens in Medical and Language Fields Business, Lifestyle & Leisure Dec 6, 2008

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Graeme at Raptup" <GraemeatRaptup@discussions.microsoft.com> wrote in
    message news:AFE0E8BD-DF0F-40E9-9926-0E3E056FB606@microsoft.com...
    > 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.
     
  4. Graeme at Raptup

    Graeme at Raptup
    Expand Collapse
    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?

    "Douglas J Steele" wrote:

    > 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
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Graeme at Raptup" <GraemeatRaptup@discussions.microsoft.com> wrote in
    > message news:AFE0E8BD-DF0F-40E9-9926-0E3E056FB606@microsoft.com...
    > > 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.

    >
    >
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 10 May 2006 05:03:01 -0700, Graeme at Raptup
    <GraemeatRaptup@discussions.microsoft.com> wrote:

    >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]
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    > Am I making sense?

    Not really. <g>

    Try John's suggestion.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Graeme at Raptup" <GraemeatRaptup@discussions.microsoft.com> wrote in
    message news:4296F6F4-BAF9-4761-A2C0-34D75DC2663F@microsoft.com...
    > 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?
    >
    > "Douglas J Steele" wrote:
    >
    >> 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
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Graeme at Raptup" <GraemeatRaptup@discussions.microsoft.com> wrote in
    >> message news:AFE0E8BD-DF0F-40E9-9926-0E3E056FB606@microsoft.com...
    >> > 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.

    >>
    >>
    >>
     

Share This Page