Geek Answers Handbook

Applying an operation to a data field with irregular date criteria

This is very new to MS Access. So a newbie that I'm not sure how to ask a question about MS Access. Therefore, please forgive any violations.

Let's say I have a database that includes the following fields:

  • Group (text box, two levels A and B)
  • Date (date field, from January 2012 to December 2014).
  • Year (defined as Year: Year ([Date]))
  • Height (number field, centimeter)

Now height is a measurement in centimeter. Say something went wrong with our height measurements, and for some dates and groups, not cm, inches were entered into the database. Say there are too many values ​​to practically change them in the database. Therefore, I need to apply this using the MS Access tool. For simplicity, suppose dividing the value per inch by 0.4 converts units to centimeters.

Heights were mistakenly entered for Group A for all of 2012 and 2013, with the exception of October 4, 2013 and October 5, 2013, where they were correctly entered.

So the question is, how did I (and think about using the criteria field in the query design representation) convert the heights from centimeters to centimeters using the above date criteria (including exceptions for October)?

Even pointing me in the right direction would be very helpful. I'm used to various programming languages, and it bothers me where to start.

Update In my rush to develop a simple example, I missed trying to add this functionality to an existing request. The answer from @winghei is probably perfect. However, it is not clear to me where to add it to my exisitng sql code in my query. Here is the existing code:

 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]))); = 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 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 can I add code from @winghei?

UPDATE 2 Therefore, I still cannot add this 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 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 = 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 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 = 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 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 an error:

Characters found after completing an SQL statement

So, I called the above query Temp_Query , then saved it:

 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

It is processed. However, when viewing this attempt, only the Heightcm column is generated in the data table Heightcm , whereas what I would like to do is add the modified column to all the data as a column.

Any ideas here?

Update 3 Added table definition (SAMPLE) for @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
+7
date ms-access
source share
4 answers

without defining your table, it’s hard to give you a “good” solution.

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

If below your query shows the correct lines. You can use this to update the sample table.

  • Add a unique example table identifier to this query below
  • save the request below as a request, 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]))); = 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 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 request 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)

there is no need for a condition in which your above_query contains only the rows that you are interested in updating. After the update, check that the values ​​are correct; ideally, this is a secondary update to adjust the WEIGHT_NUM field with the correct values.

after you have adjusted the values, you can remove the new_num_weight column, since it was still "temporary".

Publish table definitions so we understand what you're looking for.

+1
source share

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 a centimeter on Height/.4 , then you 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

In MS Access, go to Create -> Query Design . Then in the upper left corner click Result->SQL View . Finally, paste the sql template at the top, and then return to Result->Design View . I assume you will have an idea on how to use the SQL design view in Access. Note: use caution when using fallback names such as Date .

Update. From the query above, you can simply use it as a 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: How to select a request

 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 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 = 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
0
source share

To view the calculation results, 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 if you want to update the value, however, you might want to keep the updated value in another column if something goes wrong.

To update the value, create an update request based on the above request and set the HeightIN value to HeightCM. This may give you an error, although in saying that the recordset is not updatable. In this case, you will need the PC from the SAMPLE table (SPECIES_TYPE_id?) And the new value you want. You can put this in a new table and then join it using SAMPLE and you can do your update.

NTN

your date criteria go to the end of the WHERE , and you do not need a separate UPDATE query, if you are sure of the results, it is better to look at the results before updating, in case the results are not what you expected.

For your update request, if you are satisfied with the results from SELECT , try

 update UpdateSampleData set HeightIN = HeightCM

but I won’t be surprised if he says 'recordset not updateable'

Since your record set is not really being updated, you need to change the SELECT query to a MAKE TABLE query, which will bring the results to a new table. However, be sure to add the SAMPLE primary key field (s). You can then update SAMPLE with the associated value from your new table. Or, even better, first add a new field to your SAMPLE table for the new value, and then update the new field instead of overwriting the existing value.

0
source share

You are almost there. Assuming your original query from update 1 is working, you can create a query that references it and your Temp_query from update 2. This way, you won’t have to rewrite the original query, which is as complicated as it is. Instead, simply drag in the Heightcm column. You can do it this way if you add your unique identifier 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 can be as simple as shown below if you are satisfied with the result of Temp_Query, except for the absence of Heightcm:

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

More articles:


All Articles