Welcome to SPN

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

Sign Up Now!

Update query creation

Discussion in 'Information Technology' started by Azidano, Jul 28, 2006.

  1. Azidano

    Azidano
    Expand Collapse
    Guest

    I have one table called "tblData" with two fields as follows:

    Field1 Field2
    Apple A
    Apple B
    Apple C
    Pears A
    Pears B
    Pears C
    etc.

    Currently there's no data in Field2. Please help me with creating an
    update query that will do the following when Field2 is null:

    If Field1 is similar to "Apple", then update Field2 with
    "Apple"
    If Field1 is similar to "Pears", then update Field 2 with
    "Pears"
    etc.

    Thanks in advance.
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Why? That would be introducing redundant data. And if you changed Field1
    from Pears A to Apple A and forgot to update Field2, how would you know
    which was right?

    It sounds as though Field2 can be computed from Field1. Computed fields
    should never be stored in tables: they should always be computed.

    Based on the sample data you gave, you can create a query and add a computed
    field to it that returns

    Left([Field1], InStr([Field1], " ") - 1)

    If the actual calculation is more complicated than your example, post back
    with details.

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


    "Azidano" <azidano@gmail.com> wrote in message
    news:1152714449.263690.81380@b28g2000cwb.googlegroups.com...
    > I have one table called "tblData" with two fields as follows:
    >
    > Field1 Field2
    > Apple A
    > Apple B
    > Apple C
    > Pears A
    > Pears B
    > Pears C
    > etc.
    >
    > Currently there's no data in Field2. Please help me with creating an
    > update query that will do the following when Field2 is null:
    >
    > If Field1 is similar to "Apple", then update Field2 with
    > "Apple"
    > If Field1 is similar to "Pears", then update Field 2 with
    > "Pears"
    > etc.
    >
    > Thanks in advance.
    >
     
  4. Azidano

    Azidano
    Expand Collapse
    Guest

    My database table (tblData) has multiple fields, including a Field3
    that stores amounts, like this:

    Field1 Field2 Field3
    AppleA 400
    AppleA 100
    AppleA2 150
    AppleA2 150
    AppleCC1 100
    PearsB 125
    PearsB 75
    PearsB6 200
    ..
    ..
    Etc.

    I want to create a report that will subtotal by fruit group rather than
    individual fruit type. In the above table, "Apple" group subtotal
    would be 900, "Pears" group subtotal 400.

    I thought the easiest way to do this would be to create a separate
    field called "Field2" that would hold data in order to group these
    individual fruit groups.

    Is there an update query that will compute Field2 based on Field1?

    Douglas J Steele wrote:
    > Why? That would be introducing redundant data. And if you changed Field1
    > from Pears A to Apple A and forgot to update Field2, how would you know
    > which was right?
    >
    > It sounds as though Field2 can be computed from Field1. Computed fields
    > should never be stored in tables: they should always be computed.
    >
    > Based on the sample data you gave, you can create a query and add a computed
    > field to it that returns
    >
    > Left([Field1], InStr([Field1], " ") - 1)
    >
    > If the actual calculation is more complicated than your example, post back
    > with details.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Azidano" <azidano@gmail.com> wrote in message
    > news:1152714449.263690.81380@b28g2000cwb.googlegroups.com...
    > > I have one table called "tblData" with two fields as follows:
    > >
    > > Field1 Field2
    > > Apple A
    > > Apple B
    > > Apple C
    > > Pears A
    > > Pears B
    > > Pears C
    > > etc.
    > >
    > > Currently there's no data in Field2. Please help me with creating an
    > > update query that will do the following when Field2 is null:
    > >
    > > If Field1 is similar to "Apple", then update Field2 with
    > > "Apple"
    > > If Field1 is similar to "Pears", then update Field 2 with
    > > "Pears"
    > > etc.
    > >
    > > Thanks in advance.
    > >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Do you have a table that contains the valid values for Field1? If so, that
    table should have an additional field in it indicating how each entry should
    be grouped. Once you have that, it becomes straightforward to get the totals
    for which you're looking.

    As I indicated earlier, storing the results of a calculation in a table is
    an error.

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


    "Azidano" <azidano@gmail.com> wrote in message
    news:1152720627.781789.132030@i42g2000cwa.googlegroups.com...
    > My database table (tblData) has multiple fields, including a Field3
    > that stores amounts, like this:
    >
    > Field1 Field2 Field3
    > AppleA 400
    > AppleA 100
    > AppleA2 150
    > AppleA2 150
    > AppleCC1 100
    > PearsB 125
    > PearsB 75
    > PearsB6 200
    > .
    > .
    > Etc.
    >
    > I want to create a report that will subtotal by fruit group rather than
    > individual fruit type. In the above table, "Apple" group subtotal
    > would be 900, "Pears" group subtotal 400.
    >
    > I thought the easiest way to do this would be to create a separate
    > field called "Field2" that would hold data in order to group these
    > individual fruit groups.
    >
    > Is there an update query that will compute Field2 based on Field1?
    >
    > Douglas J Steele wrote:
    > > Why? That would be introducing redundant data. And if you changed Field1
    > > from Pears A to Apple A and forgot to update Field2, how would you know
    > > which was right?
    > >
    > > It sounds as though Field2 can be computed from Field1. Computed fields
    > > should never be stored in tables: they should always be computed.
    > >
    > > Based on the sample data you gave, you can create a query and add a

    computed
    > > field to it that returns
    > >
    > > Left([Field1], InStr([Field1], " ") - 1)
    > >
    > > If the actual calculation is more complicated than your example, post

    back
    > > with details.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Azidano" <azidano@gmail.com> wrote in message
    > > news:1152714449.263690.81380@b28g2000cwb.googlegroups.com...
    > > > I have one table called "tblData" with two fields as follows:
    > > >
    > > > Field1 Field2
    > > > Apple A
    > > > Apple B
    > > > Apple C
    > > > Pears A
    > > > Pears B
    > > > Pears C
    > > > etc.
    > > >
    > > > Currently there's no data in Field2. Please help me with creating an
    > > > update query that will do the following when Field2 is null:
    > > >
    > > > If Field1 is similar to "Apple", then update Field2 with
    > > > "Apple"
    > > > If Field1 is similar to "Pears", then update Field 2 with
    > > > "Pears"
    > > > etc.
    > > >
    > > > Thanks in advance.
    > > >

    >
     
  6. Azidano

    Azidano
    Expand Collapse
    Guest

    I created a new table as follows:

    table name: tblGroups
    field name: FieldGroup
    Field Group Values: Apple, Pear, Peach, etc.

    How do I group these to my original data in Field1 and table tblData to
    get the totals that I am looking for? I'm using Microsoft Access 2003.

    Douglas J Steele wrote:
    > Do you have a table that contains the valid values for Field1? If so, that
    > table should have an additional field in it indicating how each entry should
    > be grouped. Once you have that, it becomes straightforward to get the totals
    > for which you're looking.
    >
    > As I indicated earlier, storing the results of a calculation in a table is
    > an error.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Azidano" <azidano@gmail.com> wrote in message
    > news:1152720627.781789.132030@i42g2000cwa.googlegroups.com...
    > > My database table (tblData) has multiple fields, including a Field3
    > > that stores amounts, like this:
    > >
    > > Field1 Field2 Field3
    > > AppleA 400
    > > AppleA 100
    > > AppleA2 150
    > > AppleA2 150
    > > AppleCC1 100
    > > PearsB 125
    > > PearsB 75
    > > PearsB6 200
    > > .
    > > .
    > > Etc.
    > >
    > > I want to create a report that will subtotal by fruit group rather than
    > > individual fruit type. In the above table, "Apple" group subtotal
    > > would be 900, "Pears" group subtotal 400.
    > >
    > > I thought the easiest way to do this would be to create a separate
    > > field called "Field2" that would hold data in order to group these
    > > individual fruit groups.
    > >
    > > Is there an update query that will compute Field2 based on Field1?
    > >
    > > Douglas J Steele wrote:
    > > > Why? That would be introducing redundant data. And if you changed Field1
    > > > from Pears A to Apple A and forgot to update Field2, how would you know
    > > > which was right?
    > > >
    > > > It sounds as though Field2 can be computed from Field1. Computed fields
    > > > should never be stored in tables: they should always be computed.
    > > >
    > > > Based on the sample data you gave, you can create a query and add a

    > computed
    > > > field to it that returns
    > > >
    > > > Left([Field1], InStr([Field1], " ") - 1)
    > > >
    > > > If the actual calculation is more complicated than your example, post

    > back
    > > > with details.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Azidano" <azidano@gmail.com> wrote in message
    > > > news:1152714449.263690.81380@b28g2000cwb.googlegroups.com...
    > > > > I have one table called "tblData" with two fields as follows:
    > > > >
    > > > > Field1 Field2
    > > > > Apple A
    > > > > Apple B
    > > > > Apple C
    > > > > Pears A
    > > > > Pears B
    > > > > Pears C
    > > > > etc.
    > > > >
    > > > > Currently there's no data in Field2. Please help me with creating an
    > > > > update query that will do the following when Field2 is null:
    > > > >
    > > > > If Field1 is similar to "Apple", then update Field2 with
    > > > > "Apple"
    > > > > If Field1 is similar to "Pears", then update Field 2 with
    > > > > "Pears"
    > > > > etc.
    > > > >
    > > > > Thanks in advance.
    > > > >

    > >
     

Share This Page