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

Deipnosophist

You need to remove the ";" on your subquery '],[TRAWL_TRIP_DAT])));)' to resolve Characters found after end of SQL statement. I am not sure what you meant by here 'However, when looking at this attempt in datasheet view, only the Heightcm Column is generated where as I can to add it to all exisitng columns.'. The UPDATE query above when shown on datasheet view will be like SELECT Heighcm from Temp_Query WHERE.... I guess you are only interested on SELECT Query. In that case check the updated answer below – Synesthesia

what are you really trying to do?? add a new column with the correct value? or update existing values? did you do your backup job? – Brattishing

I am trying to create a new column (B) that equals column A divided by 0.4 for a certain set of date and group criteria. So column B would have all the same values as column A except those that meet the date and group criteria which would be divided by 0.4. – Deipnosophist

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.

Brattishing

Thank you for this clear response. The unique_identifier is not totally clear to me. If I include SAMPLE.its_unique_identifier I receive this error: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Now I do have an exisitng unique identifier in the database (SAMPLE_ID) for each row of data. Would that suffice? Just not feeling comfortable with my understanding of the use of the unique identifier. Table definition posted above. – Deipnosophist

Yes, you should replace the unique_identifier with whatever row Id the sample table has – Brattishing

Ok - i feel like I am almost there. When I use this code for the update query: UPDATE SAMPLE inner join qry_expSampleData2 on SAMPLE.SAMPLE_ID on qry_expSampleData2.SAMPLE_ID SET new_WEIGHT_NUM = (WEIGHT_NUM / 0.4), the receive this error message: Syntax error (missing operator) in query expression SAMPLE.SAMPLE_ID on qry_expSampleData2.SAMPLE_ID`. Haven't been able to diagnose that yet. Any ideas? – Deipnosophist

does the sample table really have sample_id? have you added sample.sample_id to the qry_exp_sampledata2? I suggest you to read more about rational databases, there is nothing we could do if you don't understand the concept of it. – Brattishing

The sample table does have a sample_id. And yes I have added the sample.sample_id to the qry_exp_sampledata2. Isn't that what you were asking for when you gave me this line in the above answer sample.sample_table_unique_id? We are SOOO close. I have done reading and am trying to get up to speed. – Deipnosophist

i've edited the update query, check edited answer @Deipnosophist – Brattishing

Let us continue this discussion in chat. – Brattishing

@Deipnosophist what's not working? I thought you nearly had it. – Brattishing

I still receive a syntax error on this statement: UPDATE SAMPLE inner join qry_expSampleData2 on SAMPLE.[SAMPLE_ID] on qry_expSampleData2.[SAMPLE_ID] SET new_WEIGHT_NUM = (Height / 0.4) – Deipnosophist

Right now I am uncertain exactly what this is doing: ` SAMPLE.[SAMPLE_ID]` in this statement. Here is my select statement from qry_expSampleDate2: SELECT LAKE.NAME_TXT AS [Lake], TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date, Year([TRAWL_TRIP_DAT]) AS [Year], SAMPLE.WEIGHT_NUM AS Height, SAMPLE.SAMPLE_ID – Deipnosophist

@Deipnosophist did you see my edited update? Update table1 inner join table2 on table1.field = tabl2.field – Brattishing

Answer 2

Assuming you have the following criteria: DATE <> 10/4/2013 , DATE <> 10/5/2013, GROUP = "A", Year(Date) = 2012, Year(Date) = 2013 and you want to update Height to centimeter by Height/.4 then you'll have to use the query:

UPDATE [table_name] 
   SET `Height`=`Height`/.4 
   WHERE `DATE` <> #10/4/2013# AND `DATE` <> #10/5/2013# AND `GROUP` = "A" AND Year(`Date`) = 2012 AND Year(`Date`) = 2013

On MS Access, go to Create -> Query Design. Then On upper left, click Result->SQL View. Finally, paste the sql pattern from above then switch back Result->Design View. I guess from here you'll have an idea on how to use the design view of SQL in Access. Note: Please be cautious using reserve names like Date.

Update: From the query above, you can just have it as subquery.

UPDATE (paste_the_query_above_here) 
   SET `Height`=`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

Update 2: As Select Query

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.WEIGHT_NUM/.4 AS Heigthcm
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]) AND TRAWL_TRIP.TRAWL_TRIP_DAT <> #10/4/2013# AND TRAWL_TRIP.TRAWL_TRIP_DAT <> #10/5/2013# AND LAKE.NAME_TXT = "A" AND Year(TRAWL_TRIP.TRAWL_TRIP_DAT) = 2012 AND Year(TRAWL_TRIP.TRAWL_TRIP_DAT) = 2013

Synesthesia

I have updated my question because I didn't have enough space to post code in the comment section. Quick clarification: Does <> mean "don't include this value"? – Deipnosophist

<>' is not equal` so,yeah, don't include the record if it is not equal. – Synesthesia

Sorry for the long delay is responding. I've updated the question because the solution doesn't seem to be working for me. – Deipnosophist

Answer 3

To view the results of the calculation, try:

SELECT  
    LAKE.NAME_TXT AS [GROUP], 
    TRAWL_TRIP.TRAWL_TRIP_DAT AS Trawl_Date, 
    YEAR([TRAWL_TRIP_DAT]) AS [YEAR],
    SAMPLE.WEIGHT_NUM AS HeightIN,
    SAMPLE.WEIGHT_NUM / 0.4 AS HeightCM,
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]))) and    
    TRAWL_TRIP.TRAWL_TRIP_DAT <> #10/4/2013# AND 
    TRAWL_TRIP.TRAWL_TRIP_DAT <> #10/5/2013# AND 
    LAKE.NAME_TXT = "A" AND 
    (Year(TRAWL_TRIP.TRAWL_TRIP_DAT) = 2012 or
    Year(TRAWL_TRIP.TRAWL_TRIP_DAT) = 2013)

I'm not sure you want to update the value, though, you may want to store the updated value in another column in case something goes wrong.

To update the value, create an update query based on the query above and update HeightIN to the value HeightCM. It might give you an error, though, saying the recordset is not updateable. If that's the case, you'll need the PK from your SAMPLE table (SPECIES_TYPE_id?) and the new value you want. You can put that in a new table and then join it with SAMPLE and you'll be able to do your update.

HTH

---

your date criteria goes at the end of the WHERE clause and you don't need a separate UPDATE query if you're confident in the results, it's just nicer to view the results before the update, in case the results aren't what you expected.

---

For your update query, if you're happy with the results from SELECT, try

update UpdateSampleData set HeightIN = HeightCM

but I wouldn't be surprised if it said 'recordset not updateable'

---

Since your recordset is indeed not updateable, you'll need to change your SELECT query to a MAKE TABLE query, which will put the results into a new table. Make sure to add the SAMPLEs primary key field(s), though. Then you should be able to update SAMPLE with the related value from your new table. Or, even better, add a new field to your SAMPLE table first for the new value, then update the new field instead of overwriting the existing value.

Nada

You are right. I need another column. However, I'm a little confused here. In the query outlined above, we are already making the conversion from inches to centimeters. So why do I need to create another update query? Also in your example where would I specify the date criteria? Those questions may be related. Sorry but as a beginner I think I missed a few steps from your (probably very awesome) answer. – Deipnosophist

"So why do I need to create another update query?"- Because the query above is a SELECT query, it does not perform any change on the stored data it just calculates and displays it. An UPDATE query can take that data and store it in your table. – Nonobedience

"Also in your example where would I specify the date criteria?" - They are specified in the WHERE clause of the query. I believe Beth has already included the criteria from your question there. – Nonobedience

Just be careful checking your years, Year(TRAWL_TRIP.TRAWL_TRIP_DAT) will never equal both 2012 and 2013, so I changed that conjunction to OR – Nada

I am questioning why I am doing a query here. I think it would make sense to add this unit conversion as data the database itself. Once I make the change it will never be different again. Accomplishing this as a query means that the calculation is performed every time I make a query and I also have to integrate it into other queries for the database as I have to make other query based on the new scaled values. So is it possible to simply add a column directly in the database? – Deipnosophist

no, I didn't mean to retain the calculation in a query, just to use the query to confirm you like the results, then hope you never have to apply the change again or undo it later. You could even add a column to mark the changed rows if you want. After you like the results from the SELECT query, then you can run the UPDATE query once. – Nada

Thank you, that makes sense now. Issue: If I run the above code replacing only SELECT with UPDATE I get a error: Syntax error in UPDATE statement. If I save the SELECT query as "UpdateSampleData" then run UPDATE(UpdateSampleData) in a new query I get the same error. So what am I doing wrong with the update process? – Deipnosophist

recordset not updateable - much like you said. The first time I tried the update, the record were updateable. The second test (using the same query etc), I received your predicted error message. I can't believe this is so complicated. This feels like this should be a relatively trivial task. – Deipnosophist

Answer 4

You're almost there. Assuming your original query from update 1 works, you can build a query referencing it and your Temp_query from update 2. This way you won't have to rewrite your original query, which is complex enough as it is. Instead just pull in your Heightcm column. You can do it like this, as long as you add your unique ID column to Temp_query.

SELECT Temp_query.[Height]/.4 as Heightcm, orig_qry.[IDvar], orig_qry.[Group], orig_qry.[Trawl_Date], orig_qry.[Year], orig_qry.[Height]
    FROM orig_qry
    INNER JOIN Temp_query
    ON orig_qry.[IDvar] = Temp_query.[IDvar]

Or it could be as simple as the below if you're happy with the output of Temp_Query except the lack of Heightcm:

SELECT [Height]/.4 as Heightcm, [Group], [Trawl_Date], [Year], [Height]
FROM Temp_query

Bronez

Thank you for the response. Can you show me where/how I add the ID column to the Temp_query? – Deipnosophist