Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update using an Aggregate function (Access XP)

    I'm not sure if this is possible or not but I'm trying to update a field using an aggregate function, the SQL I'm using is:

    Update sickhist set sickhist.fpentitlement = sum(sickhist.sickdays) group by sickhist.staffid, sickhist.fpentitlement

    I can make it work by using the onupdate function, however this database will not be used to enter data, just to run a series of calculations to data that has been imported so I can't use that option.

    Can anyone please tell me how I can do this.

    Many thanks

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update using an Aggregate function (Access XP)

    A query can't be a totals query and an update query at the same time, it's one or the other.

    I don't understand why you would want to update a field in the sickhist table to an aggregate value from the same table. Could you explain what you want to accomplish? Thanks.

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update using an Aggregate function (Access XP)

    I'm writing a system to monitor absence which is quite a complicated calculation. The part I'm struggling with at the moment is to calculate the number of days sick in the 12 months prior to the start date. For ease of calculation I have created the following fields:

    The table name is sickhist

    Startdate - Start date of the sickness
    backyear - the date 12 months prior to the sickness
    copyofstartyear - the same as the start date
    FPentitlement - the total number of days sick in the last 12 months
    sickdays - the days sickness in that occurence
    Staffid - the staff number for each person

    The SQL I would like to write to produce what I want would be:

    update sickhist
    set fpentitlement = sum(sickdays)
    having startdate > backyear and startdate < copyofstartyear

    this obviously misses out the group bys etc.

    Have you any ideas?

    Let me know if you need more information.

    Many thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update using an Aggregate function (Access XP)

    I'm sorry, I don't understand the design of the table.

    StaffID, StartDate and SickDays are straightforward, but why would you want to store BackYear and CopyOfStartYear in the table? It is derived information, it can be calculated on the fly in a query. And I would say that FPEntitlement doesn't belong in this table at all, since it is aggregated information. In general, it is not a good idea to store aggregated information in a table unless re-calculating it on the fly takes too long, and I certainly wouldn't store aggregated data in the same table that contains the individual data. If you really must store the aggregated data, create a separate table for it.

    One other point: you state that CopyOfStartYear is the same as StartDate. If that is correct, StartDate < CopyOfStartYear is always false.

    The information you want can be obtained using selection queries, I see no need for an update query.

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update using an Aggregate function (Access XP)

    OK I understand that and I originally wrote it using derived information, however, it would only ever calculate one row when each employee has any number of rows and you need to able to see what the calculations were at the time of the start of the sickness, rather than just being able to see what the calculations are currently. That's why I included the derived fields in the table.

    The reason I added the copystartofyear field was because I need to know the calculation for the previous 12 months and if I just used where startdate > dateadd(yyyy,-1,startdate) it included those sicknesses that were greater than the date of the start of the sickness.

    An example of some data is as follows:

    staffno startdate enddate sickdays
    746 1/10/02 3/10/02 3
    746 10/01/03 15/01/03 5
    746 05/06/03 05/07/03 30
    746 1/10/03 3/10/03 3

    For each record you need to calculate the TOTAL sick days in the previous 12 months.

    If you have a different way of calculcating this I would be really grateful.

    Many thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update using an Aggregate function (Access XP)

    I think it is possible to do this in a query. I'll try to whip up an example.

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update using an Aggregate function (Access XP)

    Thank you, thank you.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update using an Aggregate function (Access XP)

    The idea is to store the sick days either as individual dates or as a startdate and number of days and then use a totals query grouping on the StaffNo and using criteria to filter for the past 12 months. I don't understand how you are deriving the numbers in your example data. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> For an individual record, the number of sick days *involved* is the difference between the beginning and ending date. In a totals query, you would simply add the sickdays for StaffNo records with a start date >= a given date less 1 year and less than or = the given date AND an end date<= the given date.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update using an Aggregate function (Access XP)

    >> I don't understand how you are deriving the numbers in your example data.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> dd//mm/yy date format, with some inconsistency in including/excluding the end date.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update using an Aggregate function (Access XP)

    Suppose you have data as follows (dates in dd/mm/yy format):

    <table border=1><td>staffno</td><td>startdate</td><td>enddate</td><td>sickdays</td><td align=right>746</td><td align=right>29/09/03</td><td align=right>02/10/02</td><td align=right>4</td><td align=right>746</td><td align=right>01/10/03</td><td align=right>03/10/03</td><td align=right>3</td></table>
    The startdate in the first record is more than a year before that in the second record, but the enddate less. How many sickdays do you count in the 12 months before the second record: a) 0 because of the first startdate, or [img]/forums/images/smilies/cool.gif[/img] 2 because the first two days of October 2002 fall within the last twelve months before the first of October 2003? I assumed that you wanted the second option. This complicates the calculations, since you can't just take the startdate and enddate, you must take trim the period to fall within the twelve month period.

    For this trimming, I use a little custom function:

    Function DateBetween(aDate, LoLimit, HiLimit)
    If IsNull(aDate) Then
    DateBetween = Null
    ElseIf aDate < LoLimit Then
    DateBetween = LoLimit
    ElseIf aDate > HiLimit Then
    DateBetween = HiLimit
    Else
    DateBetween = aDate
    End If
    End Function

    It must be put in a standard module.

    I assumed that the SickHist table has three fields: StaffID, StartDate and EndDate. I built three queries:

    1. qrySickHist is based on SickHist. It adds calculated fields SickDays (for the current record) and BackYear. Its SQL is:

    SELECT StaffID, StartDate, EndDate, [EndDate]-[StartDate]+1 AS SickDays, DateAdd("yyyy",-1,[StartDate]) AS BackYear
    FROM SickHist;

    2. qrySickHist_1 is based on two instances of qrySickHist, with a non-standard outer join. For each record (the "reference record"), it retrieves those records whose sickness period falls at least partly within the twelve months before the StartDate of the reference record. The custom DateBetween function is used to "trim" the start and end dates (it wasn't really necessary for the end dates, but I left it in for completeness sake). The "trimmed" duration is calculated - only days that are within one year of StartDate of the reference record are counted. The SQL is:

    SELECT qrySickHist.StaffID, qrySickHist.StartDate, qrySickHist.EndDate, qrySickHist.SickDays, DateBetween([qrySickHist_1].[StartDate],[qrySickHist].[BackYear],[qrySickHist].[StartDate]) AS FirstDate, DateBetween([qrySickHist_1].[EndDate],[qrySickHist].[BackYear],[qrySickHist].[StartDate]) AS LastDate, CDate(Nz([LastDate],0))-CDate(Nz([FirstDate],1))+1 AS SickDays_1
    FROM qrySickHist LEFT JOIN qrySickHist AS qrySickHist_1 ON ((qrySickHist_1.EndDate)>=[qrySickHist].[BackYear]) AND ((qrySickHist_1.StartDate)<[qrySickHist].[StartDate]);

    3. qrySickHistTotals is a totals query based on qrySickHist_1. It groups on the reference record and sums the trimmed durations. Its SQL is:

    SELECT StaffID, StartDate, EndDate, SickDays, Sum(SickDays_1) AS FPEntitlement
    FROM qrySickHist_1
    GROUP BY StaffID, StartDate, EndDate, SickDays;

    I have attached a zipped demo.
    Attached Files Attached Files

  11. #11
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update using an Aggregate function (Access XP)

    That's fantastic.

    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •