Results 1 to 12 of 12
  1. #1
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9

    Post Update average from different rows in one column to another row in the same table

    Hi everyone,



    I am still quite new to Access and have spend the last two days looking for a solution to an issue but don't seem to find anything that fits. The problem is that I want the three year average of [NIbyA] inserted in [AvgNIbyA]. The years are stored in [FocalYear] (as Date/Time formate) and [TextYear] (as text formate) and [ID] indicates which entity the record belongs to (see below).

    UniqueID ID TextYear FocalYear NIbyA AvgNIbyA
    1020180-19981231 1020180 19981231 31/12/1998 2.09506606526805E-02
    1020180-19991231 1020180 19991231 31/12/1999 1.78318233097104E-02
    1020180-20001231 1020180 20001231 31/12/2000 1.86807887046594E-02
    1020180-20011231 1020180 20011231 31/12/2001 1.70427776329469E-02
    Example.accdb
    I have been playing around with update queries but can not seem to get the syntax right. The query needs to add NIbyA for year 1998, 1999 and 2000, divide it by 3 (or use Avg() function or something of that sort) and insert the result into the row where [FocalYear] = 13/12/2000. To add to the confusion, it should obviously only add those records that belong to the same ID. Years are from 1998 to 2010 and ID's are random (this obviously means that the rows where [FocalYear] = 13/12/1998 and [FocalYear] = 13/12/1999 will not have an entry).

    I hope this makes sense any suggestions, questions etc welcome!

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I assume that you want the average to be stored in the table.

    I'm also sure that it could be done purely with SQL but I am also sure the method would be very, very 'messy' involving intermediate tables and nested multi-stage queries.

    To my mind - and I invite anyone else to comment - the 'cleanest' solution would be to use a VBA routine.

    Is it always the years 1998 to 2000? Or will it be 1999 to 2001 next time around? Or is it simply the latest three years on file?

    I can write this VBA if you want.

  3. #3
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Actually thinking it over again, the SQL may not be as bad as I feared. Need to construct an intermediate result set grouped on ID, with the maximum value of TextYear (or FocalYear) and the average of NIbyA. Update the original table from the intermediate result set. The original table MUST already have a column for the result.

  4. #4
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi Rod,

    thanks a lot for the heads up. As to your questions, it is not always 1998 to 2000 but the next time around its 1999 to 2001 and then 2000 to 2002 etc. I would be happy with either solution, only that I am even less proficient with VBA than with Access queries. For the intermediate result set, I am assuming that you are suggesting a select query like:

    SELECT Example.ID, Max(Example.TextYear) AS MaxOfTextYear, Avg(Example.NIbyA) AS AvgOfNIbyA
    FROM Example
    GROUP BY Example.ID;

    This only gives me the Avg in the highest year? Does it? Or did I get something wrong?

    Thanks!!

    Regards,
    AccessKook

  5. #5
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Hm! All the great plans of mice and men ...

    I've been playing with prototype SQL and cannot get it to work simply with an intermediate result set. I encounter an 'Operation must use an updatable query' error (#3073). There is apparently the hope that this may be overcome by playing with the user permissions, but I don't think you want to bother with all that and anyway moving the db from one place to another probably means you have to repeat the task of setting permissions.

    OK, all this means is the SQL solution is a little more clumsy (and perhaps a trifle slower) than hoped for because you need to create an intermediate table. Thus there are two discrete steps to achieving your requirement: run a Make Table query and then run an Update query. These steps could presumably be encapsulated in a macro (I'm not a macro person) with a Set Warnings Off if you prefer it. (Then there is always the VBA solution but we'll leave that for now.)

    I understand that the span of years may be different from execution to execution. You therefore need a parameter; I have assumed the spread is always three years but if the spread differs then you will need two parameters. I have chosen the parameter to be the end date since this is where you want to store the average.

    The Make Table query is similar to your SQL:

    Code:
    SELECT tblExample.ID, Avg(tblExample.NIbyA) AS AvgOfNIbyA, CLng([EnterEndDateAsYYYYMMDD]) AS TY INTO tblAverages
    FROM tblExample
    WHERE (((tblExample.TextYear) Between [EnterEndDateAsYYYYMMDD]-20000 And [EnterEndDateAsYYYYMMDD]))
    GROUP BY tblExample.ID, CLng([EnterEndDateAsYYYYMMDD]);
    I've called the table, 'tblAverages.' I cannot use Max(TextYear) since this returns the maximum for the whole table and not for the group; instead I convert the parameter to a number and call it 'TY' - it needs to be a number for linking in the Update query. The result set is restricted by the WHERE clause; subtracting 20000 from the end date calculates the start date. The second term in the GROUP BY list is probably redundant.

    Here's the Update query.

    Code:
    UPDATE tblExample INNER JOIN tblAverages ON (tblExample.TextYear = tblAverages.TY) AND (tblExample.ID = tblAverages.ID)
     SET tblExample.AvgNIbyA = [tblAverages].[AvgOfNIbyA];
    Let me know how you get on.

  6. #6
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi Rod,

    thanks a lot, that is actually a very clean solution! I have applied it and it worked fine. I just noticed one oddity. When I create the intermediary table (Averages) for 20001231 then the query returns 1957 records (the original dataset is much larger than the example here and has around 20000 entries), but in the example table there are only 1769 records for the date 20001231 (when I run the update query it only updates those 1769). I compared the ID's of the Averages table with the ID's of the Originals table with a find unmatched query and did indeed find 188 unmatched ID's (that would be the difference). I am just not sure where they come from. I mean would it not be logical that if there are only 1769 records for the date 20001231 that the query only selects 1769 ID's. I am a bit confused but at the end of the day it might not be so important because when I update AvgNIbyA in the original copy then all the entries seem to have the right three year averages.

    Thanks a lot again!

    Kind regards,
    AccessKook

  7. #7
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    You should get no unmatched records! Something's wrong and I suspect it's the data. First try forcing the text year to be numeric as follows (note the additional CLng ):

    Code:
    SELECT tblExample.ID, Avg(tblExample.NIbyA) AS AvgOfNIbyA, CLng([EnterEndDateAsYYYYMMDD]) AS TY INTO tblAverages
    FROM tblExample
    WHERE (CLng(tblExample.TextYear) Between [EnterEndDateAsYYYYMMDD]-20000 And [EnterEndDateAsYYYYMMDD])
    GROUP BY tblExample.ID, CLng([EnterEndDateAsYYYYMMDD]);
    Also try explicitly making the parameter numeric:

    Click image for larger version. 

Name:	1.jpg 
Views:	20 
Size:	28.2 KB 
ID:	8696

    Whether or not that improves things, it is the WHERE clause that excludes records. So there's some condition that we have not thought of whereby records are dropped. I'm afraid you are in for some investigative debugging.

    Do you have duplicates on your table?

    As an aside: changing permissions would not have solved my original problem. However I was only trying to read the non-updatable query, not update it, so the validation is a little too broad. We live and learn.

  8. #8
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi Rod,

    There are not duplicates, I checked that before posting the other previous post but did not mention it.
    I had a play around with the where statement and seem to have solved the problem. Basically I narrowed down the range of records to be selected. The previous query was selecting records of ID's that were present only in one or two years say date 19991231. This caused the issue because the select query was selecting them even though they were of no interest. So the new select statement reads like

    Code:
    PARAMETERS [EnterEndDateAsYYYYMMDD]Long;
    SELECT tblExample.ID, Avg(tblExample.NIbyA) AS AvgOfNIbyA, CLng([EnterEndDateAsYYYYMMDD]) AS TY INTO tblAverages
    FROM tblExample
    WHERE (Clng(tblExample.TextYear) Between [EnterEndDateAsYYYYMMDD]-20000 And [EnterEndDateAsYYYYMMDD]) and (Clng(tblExample.TextYear) Between [EnterEndDateAsYYYYMMDD]-10000 And [EnterEndDateAsYYYYMMDD]) And (Clng(tblExample.TextYear) = [EnterEndDateAsYYYYMMDD])
    GROUP BY tblExample.ID, CLng([EnterEndDateAsYYYYMMDD]);
    That seems to do the trick. Again thanks a lot for your help.

    Kind regards,
    AccessKook

  9. #9
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Great, pleased that it works. I meant to ask (but didn't!) whether all three years had to be present.

    Do you want to put this in one macro or encapsulate it in VBA?

  10. #10
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi Rod,

    As I mentioned earlier I am not very proficient in VBA or macro but if you would write the code it might come in handy for automating the process.

    Kind regards,
    AccessKook

  11. #11
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Here's a Macro encapsulation.

    Click image for larger version. 

Name:	1.jpg 
Views:	15 
Size:	27.3 KB 
ID:	8762

    Substitute the names of your own queries for Query2 and Query3 - in fact select them from the drop-down list at the foot of the design window.

    Make sure that 'Show All Actions' is selected otherwise 'SetWarnings' is not available.

  12. #12
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi Rod,

    works great, thanks a lot again!!!

    Kind regards,
    AccessKook

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2012, 10:38 AM
  2. Replies: 1
    Last Post: 12-16-2011, 08:16 AM
  3. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  4. Replies: 13
    Last Post: 05-28-2010, 11:57 AM
  5. Replies: 3
    Last Post: 05-19-2010, 10:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums