Applying an operation to a field of data with irregular date criteria

Question

This is a very novice question regarding MS Access. So novice that I am not sure how to properly ask a question about MS Access. So please forgive any transgressions.

Say I have a database that includes the following fields:

• Group (text field; two levels A and B)
• Date (date field; January 2012 to December 2014)
• Year (defined as Year: Year([Date]))
• Height (numeric field; centimeter)

Now height is a measurement in centimeter. Say something went wrong with our height measurements and for some Dates and Groups, rather than cm, inches were inputted into the database. Say there are too many values to practically change them within the database. So I need to apply this using MS Access tool. For simplicity, let say that dividing an inch value by 0.4 will convert the units to centimeters.

Heights were wrongly inputted for Group A for all of 2012 and 2013 except for October 4, 2013 and October 5, 2013 where they were correctly inputted.

So the question is, how do I (and my thought was to use the criteria field in the design view of a query) convert the heights from inches to centimeter using the date criteria specified above (including the October exceptions)?

Even pointing me in the right direction would be very helpful. I am used to different programming languages and this has me stumped where to start.

Update In my haste to develop a simple example I omitted that I was trying to add this functionality to an existing query. The answer from @winghei is likely perfect. However it is unclear to me where to add it to my exisitng sql code in my query. Here is the code that exists already:

SELECT LAKE.NAME_TXT AS [Group], TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date, Year([TRAWL_TRIP_DAT]) AS [Year], SAMPLE.WEIGHT_NUM AS Height
FROM (((LAKE INNER JOIN STATION ON LAKE.LAKE_ID = STATION.LAKE_ID) INNER JOIN (NET_TYPE RIGHT JOIN (TRAWL_TYPE RIGHT JOIN TRAWL_TRIP ON TRAWL_TYPE.TRAWL_TYPE_ID = TRAWL_TRIP.TRAWL_TYPE_ID) ON NET_TYPE.NET_TYPE_ID = TRAWL_TRIP.NET_TYPE_ID) ON STATION.STATION_ID = TRAWL_TRIP.STATION_ID) INNER JOIN TRAWL ON TRAWL_TRIP.TRAWL_TRIP_ID = TRAWL.TRAWL_TRIP_ID) INNER JOIN (MATURITY_TYPE RIGHT JOIN (SPECIES_TYPE INNER JOIN SAMPLE ON SPECIES_TYPE.SPECIES_TYPE_ID = SAMPLE.SPECIES_TYPE_ID) ON MATURITY_TYPE.MATURITY_TYPE_ID = SAMPLE.MATURITY_TYPE_ID) ON TRAWL.TRAWL_ID = SAMPLE.TRAWL_ID
WHERE (((TRAWL_TRIP.TRAWL_TRIP_DAT)>=Nz([Forms]![frmReport]![txtTrawlDateFrom],[TRAWL_TRIP_DAT]) And (TRAWL_TRIP.TRAWL_TRIP_DAT)<=Nz([Forms]![frmReport]![txtTrawlDateTo],[TRAWL_TRIP_DAT])));

So now the question is, where do I add the code from @winghei?

UPDATE 2 So I still am unable to add this query in.

SELECT LAKE.NAME_TXT AS [Group], TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date, Year([TRAWL_TRIP_DAT]) AS [Year], SAMPLE.WEIGHT_NUM AS Height
    FROM (((LAKE INNER JOIN STATION ON LAKE.LAKE_ID = STATION.LAKE_ID) INNER JOIN (NET_TYPE RIGHT JOIN (TRAWL_TYPE RIGHT JOIN TRAWL_TRIP ON TRAWL_TYPE.TRAWL_TYPE_ID = TRAWL_TRIP.TRAWL_TYPE_ID) ON NET_TYPE.NET_TYPE_ID = TRAWL_TRIP.NET_TYPE_ID) ON STATION.STATION_ID = TRAWL_TRIP.STATION_ID) INNER JOIN TRAWL ON TRAWL_TRIP.TRAWL_TRIP_ID = TRAWL.TRAWL_TRIP_ID) INNER JOIN (MATURITY_TYPE RIGHT JOIN (SPECIES_TYPE INNER JOIN SAMPLE ON SPECIES_TYPE.SPECIES_TYPE_ID = SAMPLE.SPECIES_TYPE_ID) ON MATURITY_TYPE.MATURITY_TYPE_ID = SAMPLE.MATURITY_TYPE_ID) ON TRAWL.TRAWL_ID = SAMPLE.TRAWL_ID
    WHERE (((TRAWL_TRIP.TRAWL_TRIP_DAT)>=Nz([Forms]![frmReport]![txtTrawlDateFrom],[TRAWL_TRIP_DAT]) And (TRAWL_TRIP.TRAWL_TRIP_DAT)<=Nz([Forms]![frmReport]![txtTrawlDateTo],[TRAWL_TRIP_DAT])));

UPDATE(SELECT LAKE.NAME_TXT AS [Group], TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date, Year([TRAWL_TRIP_DAT]) AS [Year], SAMPLE.WEIGHT_NUM AS Height
    FROM (((LAKE INNER JOIN STATION ON LAKE.LAKE_ID = STATION.LAKE_ID) INNER JOIN (NET_TYPE RIGHT JOIN (TRAWL_TYPE RIGHT JOIN TRAWL_TRIP ON TRAWL_TYPE.TRAWL_TYPE_ID = TRAWL_TRIP.TRAWL_TYPE_ID) ON NET_TYPE.NET_TYPE_ID = TRAWL_TRIP.NET_TYPE_ID) ON STATION.STATION_ID = TRAWL_TRIP.STATION_ID) INNER JOIN TRAWL ON TRAWL_TRIP.TRAWL_TRIP_ID = TRAWL.TRAWL_TRIP_ID) INNER JOIN (MATURITY_TYPE RIGHT JOIN (SPECIES_TYPE INNER JOIN SAMPLE ON SPECIES_TYPE.SPECIES_TYPE_ID = SAMPLE.SPECIES_TYPE_ID) ON MATURITY_TYPE.MATURITY_TYPE_ID = SAMPLE.MATURITY_TYPE_ID) ON TRAWL.TRAWL_ID = SAMPLE.TRAWL_ID
    WHERE (((TRAWL_TRIP.TRAWL_TRIP_DAT)>=Nz([Forms]![frmReport]![txtTrawlDateFrom],[TRAWL_TRIP_DAT]) And (TRAWL_TRIP.TRAWL_TRIP_DAT)<=Nz([Forms]![frmReport]![txtTrawlDateTo],[TRAWL_TRIP_DAT])));)
   SET `Heightcm`=`Height`/.4 
   WHERE `Trawl_Date` <> #10/4/2013# AND `Trawl_Date` <> #10/5/2013# AND `GROUP` = "A" AND Year(`Trawl_Date`) = 2012 AND Year(`Trawl_Date`) = 2013

This results in this error:

Characters found after end of SQL statement

So then I named the above query Temp_Query, saved it then tried this:

UPDATE(Temp_Query)
   SET `Heightcm`=`Height`/.4 
   WHERE `Trawl_Date` <> #10/4/2013# AND `Trawl_Date` <> #10/5/2013# AND `GROUP` = "A" AND Year(`Trawl_Date`) = 2012 AND Year(`Trawl_Date`) = 2013

This processed. However, when looking at this attempt in datasheet view, only a Heightcm Column is generated whereas what I would like to do is add the modified column to the whole data as an column.

Any ideas here?

Update 3 Posted a table definition (SAMPLE) by request of @krish KM

Table: SAMPLE                                                                                        Page: 1



Properties

AlternateBackShade:       100                         AlternateBackThemeC       -1
AlternateBackTint:        100                         BackShade:                100
BackTint:                 100                         DatasheetForeTheme        -1

DatasheetGridlinesTh      -1                          DateCreated:              2013-02-27 8:14:59 PM
DefaultView:              2                           DisplayViewsOnShare       1
FilterOnLoad:             False                       GUID:                     {guid {9ADCA5B5-
                                                                                DCFF-49DB-BBC2-

HideNewField:             False                       LastUpdated:              2016-10-12 9:39:47 AM
NameMap:                  Long binary data            OrderByOn:                False
OrderByOnLoad:            True                        Orientation:              Left-to-Right

PublishToWeb:             1                           ReadOnlyWhenDiscon        False
RecordCount:              55907                       ThemeFontIndex:           -1
TotalsRow:                False                       Updatable:                True

Answer 1

without your table definition its hard to give you the "good" solution.

1. open your sample table and add a new column "new_WEIGHT_NUM" (this will be your new column for your correct value)

If below query of yours is showing you the correct rows. You can use this to update the sample table.

1. Add the unique identifier of sample table in this below query
2. save below query as a query as you already did.

**

SELECT 
    LAKE.NAME_TXT AS [Group],
    TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date,
    Year([TRAWL_TRIP_DAT]) AS [Year], 
    SAMPLE.WEIGHT_NUM AS Height

    SAMPLE.its_unique_identifier

FROM (((LAKE INNER JOIN STATION ON LAKE.LAKE_ID = STATION.LAKE_ID) INNER JOIN (NET_TYPE RIGHT JOIN (TRAWL_TYPE RIGHT JOIN TRAWL_TRIP ON TRAWL_TYPE.TRAWL_TYPE_ID = TRAWL_TRIP.TRAWL_TYPE_ID) ON NET_TYPE.NET_TYPE_ID = TRAWL_TRIP.NET_TYPE_ID) ON STATION.STATION_ID = TRAWL_TRIP.STATION_ID) INNER JOIN TRAWL ON TRAWL_TRIP.TRAWL_TRIP_ID = TRAWL.TRAWL_TRIP_ID) INNER JOIN (MATURITY_TYPE RIGHT JOIN (SPECIES_TYPE INNER JOIN SAMPLE ON SPECIES_TYPE.SPECIES_TYPE_ID = SAMPLE.SPECIES_TYPE_ID) ON MATURITY_TYPE.MATURITY_TYPE_ID = SAMPLE.MATURITY_TYPE_ID) ON TRAWL.TRAWL_ID = SAMPLE.TRAWL_ID
WHERE (((TRAWL_TRIP.TRAWL_TRIP_DAT)>=Nz([Forms]![frmReport]![txtTrawlDateFrom],[TRAWL_TRIP_DAT]) And (TRAWL_TRIP.TRAWL_TRIP_DAT)<=Nz([Forms]![frmReport]![txtTrawlDateTo],[TRAWL_TRIP_DAT])));

your update query should look like this: EDIT

UPDATE SAMPLE 
inner join qry_exp_sampledata2 on sample.sample_id = qry_exp_sampledata2.sample_id
SET sample.new_WEIGHT_NUM = (WEIGHT_NUM / 0.4)

no need for where condition as your above_query contains only the rows you are interested in to update. After updating check if the values are correct, ideally a secondary update to correct the WEIGHT_NUM field with the correct values.

after you have corrected the values you can delete the new_num_weight column as this was a "temporary" anyway.

Post your table definitions so we get some more idea what you are looking for.