Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    23

    Question Unanswered: Complicated Query from two tables

    I have a Rates Table which consist of some rates(half weekly rates, weekly rates & monthly rates)...the other table is an attendance table which consist of names, date, absent or present(yes/no) field.

    Now the query or report I would like to run is when a child attend 3 or less days in a week...the half weekly rate must apply, when they attend more than 3 days the weekly rate will apply...also there are a few monthly payments.

    I'm having a hard time coming up with the right results.

    Any help is greatly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Without knowing your data model its a bit difficult. But I woudl suggest splitting the task into several queries

    1st query to count the number of days attended by each child in each week
    2nd query to find the correct rate

    eg
    Q1
    select [childNum], count([ChildNum]) as NoAttend from tblAttendance
    where [weekno]=??????

    Q2
    select [ChildNum],NoAttend, iif([PaysMonthly]=true, <use mothlyrate>, iif[NoAttend])<=3,<use half week rate>,<use week rate>) as rate from Q1

    HOpe this gives an idea of what might work - you probably should pull the rates from a separate table (in place of the rate set a rate code, and pull the rate in a third query.

    Its not the most elegant, but it should get you there, A word of caution IIF can have problems with null values.

Posting Permissions

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