Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: Aggragating a parameter sub query

    Hi,

    Maybe someone could give me some pointers.

    In a table Salary ( ID, Sal_month, Sal_Amount ) an salary amount is entered when changed. So if Empl starts jan 2010, salary is entered for that month. If he gets a raise in jun, thats enterd for june.

    To retrieve a salary for a specific month I use a sub query
    ( SELECT Top 1 * from Salary WHERE Sal_Month < parameter ORDER Sal_Month DESC )

    Parameter is a date from a form. Works fine.

    Now what if I want to calculate the total for a year? I could of course do a loop in VBA for all months, but I would like to do it fully in SQL. I just do not see how I can do a SUM on the sub query above.

    Any ideas?

    Thanks so much,

    Arnold

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Couldn't you use something like this as your parameter: Between [Enter Start Date:] and [Enter End Date:] or base it off of 2 date fields on the form (ie. Between Forms!MyFormName!StartDate and Forms!MyFormName!EndDate).

    Providing the Sal_Month or another field is a date type field (which ideally it should be)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2009
    Posts
    12
    Thanks PK,

    Thanks for your reply, you're fielding a lot of questions here.

    Its not a problem of getting the right range. There is not a table with salaries for every month, because most of the months there will be no change in salariy. So in the subquery, I calculate the salery for the relevant month, by finding the last salary entry in or before that month with the ( SELECT Top 1.... ) - statement.

    Now the trick is how to sum those results for a range of months. I need to loop through the sub query, for all the months in the range of months I want to sum over.

    Hope this is making sense.

    Arnold

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hi Arnold,

    It's a little bit confusing and giving you an answer without a bit more information but what I often do in situations similar to this is design a make-table query to make a table based on the criteria and then use that table in further calculations or a report.

    You can always write code to open a recordset and loop through the table.

    But my senses are twitching that something is not quite right with the table design or field types given your post. Have you considered possibly adding another field to designate salary changes or some other type of field to make your query easier so you don't need to do any kind of 'looping'?

    I rarely need to use something like SELECT Top 1... but instead set it up so I can just use criteria in the query to 'separate' these records from the rest.

    Calculating totals for a year versus a month should be able to be done using the same query where you can simply apply criteria to a date type field.

    The question I guess I need to ask is what field type is Sal_Month? Hopefully it's a date type field or you have another date type field.
    Last edited by pkstormy; 04-14-10 at 21:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2009
    Posts
    12
    Hi PK,

    Yes it is a data type.

    I think I'll use the make table/recordset work-around for now and have a long hard look at the set-up to see if I can fix it there as well.

    Thanks for your time.

    Arnold

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Arnold,

    Here's an example which might help on your make-table routine and/or using criteria on a form to base it upon date ranges (or other criteria): http://www.dbforums.com/6274791-post21.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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