Update query creation

[Azidano]

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.

 

[Douglas J Steele]

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.

 

[Azidano]

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?

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

(no e-mails, please!)

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.

 

[Douglas J Steele]

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

(no e-mails, please!)

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?

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

(no e-mails, please!)

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.

 

[Azidano]

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.

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

(no e-mails, please!)

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?

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

(no e-mails, please!)


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.