Counting records 'prior to' other records

Discussion in 'Microsoft Access Queries' started by Leslie Isaacs, Aug 12, 2008.

  1. Hello All

    I have a table [absences] with fields 'absencedate' and 'absencetype'
    (amongst others).
    I need a query that will return, for each record in [absences], the position
    of each of the "w" 'absencetypes' when the records are sorted by
    'absencedate'.

    e.g.
    absencedate absencetype position
    14 July 2008 s 0 (or blank)
    18 July 2008 s 0 (or blank)
    19 July 2008 w 1
    22 July 2008 s 0 (or blank)
    26 July 2008 w 2
    etc

    I realise that there is no real order of records in a table, so I decided to
    try, for each record, to count the number of records of 'absencetype' "w"
    with an earlier absencedate. The sql of my qruery was:

    SELECT absences.absencedate, absences.absencetype,
    DCount("[absencedate]","[absences]","[absencetype]='w' and
    [absencedate]<[absencedate]")+1 AS Position
    FROM absences;

    .... but this obviously returns a Position of 0 for all records (because of
    the criteria [absencedate]<[absencedate]), so I amended it to:

    SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    DCount("[absencedate]","[absences]","[absencetype]='w' and
    [thisabsencedate]<[absencedate]")+1 AS Position
    FROM absences;

    .... but now I get an error that access cannot find the name 'absencedate'.
    I'm now stuck, and hope someone can help!

    Many thanks
    Leslie Isaacs
     
    Leslie Isaacs, Aug 12, 2008
    #1
    1. Advertisements

  2. Leslie Isaacs

    bcap Guest

    Your query can be fixed by constructing the DCount criterion correctly,
    thus:

    DCount("[absencedate]","[absences]","[absencetype]='w' and [absencedate]<#"
    & Format([absencedate],"mm/dd/yyyy") & "#")

    This will be a fearsomely inefficient query. This would be much better:

    SELECT
    A1.absencedate AS thisabsencedate,
    A1.absencetype,
    -1*(A1.absencetype='w') * (COUNT(A2.absencedate)+1) AS Position
    FROM
    absences AS A1 LEFT JOIN
    absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    'w')
    GROUP BY
    A1.absencedate,
    A1.absencetype;

    "Leslie Isaacs" <> wrote in message
    news:u3rCBCF$...
    > Hello All
    >
    > I have a table [absences] with fields 'absencedate' and 'absencetype'
    > (amongst others).
    > I need a query that will return, for each record in [absences], the
    > position of each of the "w" 'absencetypes' when the records are sorted by
    > 'absencedate'.
    >
    > e.g.
    > absencedate absencetype position
    > 14 July 2008 s 0 (or blank)
    > 18 July 2008 s 0 (or blank)
    > 19 July 2008 w 1
    > 22 July 2008 s 0 (or blank)
    > 26 July 2008 w 2
    > etc
    >
    > I realise that there is no real order of records in a table, so I decided
    > to try, for each record, to count the number of records of 'absencetype'
    > "w" with an earlier absencedate. The sql of my qruery was:
    >
    > SELECT absences.absencedate, absences.absencetype,
    > DCount("[absencedate]","[absences]","[absencetype]='w' and
    > [absencedate]<[absencedate]")+1 AS Position
    > FROM absences;
    >
    > ... but this obviously returns a Position of 0 for all records (because of
    > the criteria [absencedate]<[absencedate]), so I amended it to:
    >
    > SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    > DCount("[absencedate]","[absences]","[absencetype]='w' and
    > [thisabsencedate]<[absencedate]")+1 AS Position
    > FROM absences;
    >
    > ... but now I get an error that access cannot find the name 'absencedate'.
    > I'm now stuck, and hope someone can help!
    >
    > Many thanks
    > Leslie Isaacs
    >
    >
    >
     
    bcap, Aug 12, 2008
    #2
    1. Advertisements

  3. Hello 'Bcap'

    Brilliant!
    Many thanks for your suggestions - both of them!
    I will use the 2nd.

    Thanks again
    Les

    "bcap" <> wrote in message
    news:48a17101$0$2925$...
    > Your query can be fixed by constructing the DCount criterion correctly,
    > thus:
    >
    > DCount("[absencedate]","[absences]","[absencetype]='w' and
    > [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#")
    >
    > This will be a fearsomely inefficient query. This would be much better:
    >
    > SELECT
    > A1.absencedate AS thisabsencedate,
    > A1.absencetype,
    > -1*(A1.absencetype='w') * (COUNT(A2.absencedate)+1) AS Position
    > FROM
    > absences AS A1 LEFT JOIN
    > absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    > 'w')
    > GROUP BY
    > A1.absencedate,
    > A1.absencetype;
    >
    > "Leslie Isaacs" <> wrote in message
    > news:u3rCBCF$...
    >> Hello All
    >>
    >> I have a table [absences] with fields 'absencedate' and 'absencetype'
    >> (amongst others).
    >> I need a query that will return, for each record in [absences], the
    >> position of each of the "w" 'absencetypes' when the records are sorted by
    >> 'absencedate'.
    >>
    >> e.g.
    >> absencedate absencetype position
    >> 14 July 2008 s 0 (or blank)
    >> 18 July 2008 s 0 (or blank)
    >> 19 July 2008 w 1
    >> 22 July 2008 s 0 (or blank)
    >> 26 July 2008 w 2
    >> etc
    >>
    >> I realise that there is no real order of records in a table, so I decided
    >> to try, for each record, to count the number of records of 'absencetype'
    >> "w" with an earlier absencedate. The sql of my qruery was:
    >>
    >> SELECT absences.absencedate, absences.absencetype,
    >> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >> [absencedate]<[absencedate]")+1 AS Position
    >> FROM absences;
    >>
    >> ... but this obviously returns a Position of 0 for all records (because
    >> of the criteria [absencedate]<[absencedate]), so I amended it to:
    >>
    >> SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    >> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >> [thisabsencedate]<[absencedate]")+1 AS Position
    >> FROM absences;
    >>
    >> ... but now I get an error that access cannot find the name
    >> 'absencedate'.
    >> I'm now stuck, and hope someone can help!
    >>
    >> Many thanks
    >> Leslie Isaacs
    >>
    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 12, 2008
    #3
  4. Hello again 'Bcap'

    Having said that I will use your 2nd suggestion, I now have a problem: I
    cannot open the query in design view ("Access cannot represent the joins
    ...."), but I need the query to be an append query, and to add some other
    fields and criteria, and I don't know how to do these things in sql view. Is
    there any way to tell access how to show the query in design view?

    Thanks for your further help.
    Les


    "Leslie Isaacs" <> wrote in message
    news:edhZW2J$...
    > Hello 'Bcap'
    >
    > Brilliant!
    > Many thanks for your suggestions - both of them!
    > I will use the 2nd.
    >
    > Thanks again
    > Les
    >
    > "bcap" <> wrote in message
    > news:48a17101$0$2925$...
    >> Your query can be fixed by constructing the DCount criterion correctly,
    >> thus:
    >>
    >> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >> [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#")
    >>
    >> This will be a fearsomely inefficient query. This would be much better:
    >>
    >> SELECT
    >> A1.absencedate AS thisabsencedate,
    >> A1.absencetype,
    >> -1*(A1.absencetype='w') * (COUNT(A2.absencedate)+1) AS Position
    >> FROM
    >> absences AS A1 LEFT JOIN
    >> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >> 'w')
    >> GROUP BY
    >> A1.absencedate,
    >> A1.absencetype;
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:u3rCBCF$...
    >>> Hello All
    >>>
    >>> I have a table [absences] with fields 'absencedate' and 'absencetype'
    >>> (amongst others).
    >>> I need a query that will return, for each record in [absences], the
    >>> position of each of the "w" 'absencetypes' when the records are sorted
    >>> by 'absencedate'.
    >>>
    >>> e.g.
    >>> absencedate absencetype position
    >>> 14 July 2008 s 0 (or blank)
    >>> 18 July 2008 s 0 (or blank)
    >>> 19 July 2008 w 1
    >>> 22 July 2008 s 0 (or blank)
    >>> 26 July 2008 w 2
    >>> etc
    >>>
    >>> I realise that there is no real order of records in a table, so I
    >>> decided to try, for each record, to count the number of records of
    >>> 'absencetype' "w" with an earlier absencedate. The sql of my qruery was:
    >>>
    >>> SELECT absences.absencedate, absences.absencetype,
    >>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>> [absencedate]<[absencedate]")+1 AS Position
    >>> FROM absences;
    >>>
    >>> ... but this obviously returns a Position of 0 for all records (because
    >>> of the criteria [absencedate]<[absencedate]), so I amended it to:
    >>>
    >>> SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    >>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>> [thisabsencedate]<[absencedate]")+1 AS Position
    >>> FROM absences;
    >>>
    >>> ... but now I get an error that access cannot find the name
    >>> 'absencedate'.
    >>> I'm now stuck, and hope someone can help!
    >>>
    >>> Many thanks
    >>> Leslie Isaacs
    >>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 12, 2008
    #4
  5. Leslie Isaacs

    bcap Guest

    I'm sorry, I should have mentioned: the Access query designer is not smart,
    and it will most certainly barf on the query I gave you. You have several
    choices:

    1. Use your original query, with the correction I supplied to the Dcount
    criterion;

    2. Post your requirements and ask for help implementing them directly in the
    SQL;

    3. Learn a bit more about working in SQL (which is probably, really, a
    corollary to option 2);

    4. (a) In the SQL window, copy the join expression, and paste it into
    Notepad, Word or whatever (b) in the query, replace the join expression with
    a simpler one (e.g. A1.absencedate=A2.absencedate) (c) continue to design
    the query in the query designer (d) when you are done, switch to the SQL
    view and copy/paste the original join expression back into the query.

    Unfortunately the one thing that is not possible is to force the query
    designer to accept a join expression of which it has already disapproved.


    "Leslie Isaacs" <> wrote in message
    news:eb12unM$...
    > Hello again 'Bcap'
    >
    > Having said that I will use your 2nd suggestion, I now have a problem: I
    > cannot open the query in design view ("Access cannot represent the joins
    > ..."), but I need the query to be an append query, and to add some other
    > fields and criteria, and I don't know how to do these things in sql view.
    > Is there any way to tell access how to show the query in design view?
    >
    > Thanks for your further help.
    > Les
    >
    >
    > "Leslie Isaacs" <> wrote in message
    > news:edhZW2J$...
    >> Hello 'Bcap'
    >>
    >> Brilliant!
    >> Many thanks for your suggestions - both of them!
    >> I will use the 2nd.
    >>
    >> Thanks again
    >> Les
    >>
    >> "bcap" <> wrote in message
    >> news:48a17101$0$2925$...
    >>> Your query can be fixed by constructing the DCount criterion correctly,
    >>> thus:
    >>>
    >>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>> [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#")
    >>>
    >>> This will be a fearsomely inefficient query. This would be much better:
    >>>
    >>> SELECT
    >>> A1.absencedate AS thisabsencedate,
    >>> A1.absencetype,
    >>> -1*(A1.absencetype='w') * (COUNT(A2.absencedate)+1) AS Position
    >>> FROM
    >>> absences AS A1 LEFT JOIN
    >>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >>> 'w')
    >>> GROUP BY
    >>> A1.absencedate,
    >>> A1.absencetype;
    >>>
    >>> "Leslie Isaacs" <> wrote in message
    >>> news:u3rCBCF$...
    >>>> Hello All
    >>>>
    >>>> I have a table [absences] with fields 'absencedate' and 'absencetype'
    >>>> (amongst others).
    >>>> I need a query that will return, for each record in [absences], the
    >>>> position of each of the "w" 'absencetypes' when the records are sorted
    >>>> by 'absencedate'.
    >>>>
    >>>> e.g.
    >>>> absencedate absencetype position
    >>>> 14 July 2008 s 0 (or blank)
    >>>> 18 July 2008 s 0 (or blank)
    >>>> 19 July 2008 w 1
    >>>> 22 July 2008 s 0 (or blank)
    >>>> 26 July 2008 w 2
    >>>> etc
    >>>>
    >>>> I realise that there is no real order of records in a table, so I
    >>>> decided to try, for each record, to count the number of records of
    >>>> 'absencetype' "w" with an earlier absencedate. The sql of my qruery
    >>>> was:
    >>>>
    >>>> SELECT absences.absencedate, absences.absencetype,
    >>>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>>> [absencedate]<[absencedate]")+1 AS Position
    >>>> FROM absences;
    >>>>
    >>>> ... but this obviously returns a Position of 0 for all records (because
    >>>> of the criteria [absencedate]<[absencedate]), so I amended it to:
    >>>>
    >>>> SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    >>>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>>> [thisabsencedate]<[absencedate]")+1 AS Position
    >>>> FROM absences;
    >>>>
    >>>> ... but now I get an error that access cannot find the name
    >>>> 'absencedate'.
    >>>> I'm now stuck, and hope someone can help!
    >>>>
    >>>> Many thanks
    >>>> Leslie Isaacs
    >>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    bcap, Aug 12, 2008
    #5
  6. 'Bcap'

    Many thanks once again for your suggestions.
    I will go with option 4, and I will post back here with the result/any
    problems.

    Thanks again
    Les


    "bcap" <> wrote in message
    news:48a212de$0$2509$...
    > I'm sorry, I should have mentioned: the Access query designer is not
    > smart, and it will most certainly barf on the query I gave you. You have
    > several choices:
    >
    > 1. Use your original query, with the correction I supplied to the Dcount
    > criterion;
    >
    > 2. Post your requirements and ask for help implementing them directly in
    > the SQL;
    >
    > 3. Learn a bit more about working in SQL (which is probably, really, a
    > corollary to option 2);
    >
    > 4. (a) In the SQL window, copy the join expression, and paste it into
    > Notepad, Word or whatever (b) in the query, replace the join expression
    > with a simpler one (e.g. A1.absencedate=A2.absencedate) (c) continue to
    > design the query in the query designer (d) when you are done, switch to
    > the SQL view and copy/paste the original join expression back into the
    > query.
    >
    > Unfortunately the one thing that is not possible is to force the query
    > designer to accept a join expression of which it has already disapproved.
    >
    >
    > "Leslie Isaacs" <> wrote in message
    > news:eb12unM$...
    >> Hello again 'Bcap'
    >>
    >> Having said that I will use your 2nd suggestion, I now have a problem: I
    >> cannot open the query in design view ("Access cannot represent the joins
    >> ..."), but I need the query to be an append query, and to add some other
    >> fields and criteria, and I don't know how to do these things in sql view.
    >> Is there any way to tell access how to show the query in design view?
    >>
    >> Thanks for your further help.
    >> Les
    >>
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:edhZW2J$...
    >>> Hello 'Bcap'
    >>>
    >>> Brilliant!
    >>> Many thanks for your suggestions - both of them!
    >>> I will use the 2nd.
    >>>
    >>> Thanks again
    >>> Les
    >>>
    >>> "bcap" <> wrote in message
    >>> news:48a17101$0$2925$...
    >>>> Your query can be fixed by constructing the DCount criterion correctly,
    >>>> thus:
    >>>>
    >>>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>>> [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#")
    >>>>
    >>>> This will be a fearsomely inefficient query. This would be much
    >>>> better:
    >>>>
    >>>> SELECT
    >>>> A1.absencedate AS thisabsencedate,
    >>>> A1.absencetype,
    >>>> -1*(A1.absencetype='w') * (COUNT(A2.absencedate)+1) AS Position
    >>>> FROM
    >>>> absences AS A1 LEFT JOIN
    >>>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype
    >>>> = 'w')
    >>>> GROUP BY
    >>>> A1.absencedate,
    >>>> A1.absencetype;
    >>>>
    >>>> "Leslie Isaacs" <> wrote in message
    >>>> news:u3rCBCF$...
    >>>>> Hello All
    >>>>>
    >>>>> I have a table [absences] with fields 'absencedate' and 'absencetype'
    >>>>> (amongst others).
    >>>>> I need a query that will return, for each record in [absences], the
    >>>>> position of each of the "w" 'absencetypes' when the records are sorted
    >>>>> by 'absencedate'.
    >>>>>
    >>>>> e.g.
    >>>>> absencedate absencetype position
    >>>>> 14 July 2008 s 0 (or blank)
    >>>>> 18 July 2008 s 0 (or blank)
    >>>>> 19 July 2008 w 1
    >>>>> 22 July 2008 s 0 (or blank)
    >>>>> 26 July 2008 w 2
    >>>>> etc
    >>>>>
    >>>>> I realise that there is no real order of records in a table, so I
    >>>>> decided to try, for each record, to count the number of records of
    >>>>> 'absencetype' "w" with an earlier absencedate. The sql of my qruery
    >>>>> was:
    >>>>>
    >>>>> SELECT absences.absencedate, absences.absencetype,
    >>>>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>>>> [absencedate]<[absencedate]")+1 AS Position
    >>>>> FROM absences;
    >>>>>
    >>>>> ... but this obviously returns a Position of 0 for all records
    >>>>> (because of the criteria [absencedate]<[absencedate]), so I amended it
    >>>>> to:
    >>>>>
    >>>>> SELECT absences.absencedate AS thisabsencedate, absences.absencetype,
    >>>>> DCount("[absencedate]","[absences]","[absencetype]='w' and
    >>>>> [thisabsencedate]<[absencedate]")+1 AS Position
    >>>>> FROM absences;
    >>>>>
    >>>>> ... but now I get an error that access cannot find the name
    >>>>> 'absencedate'.
    >>>>> I'm now stuck, and hope someone can help!
    >>>>>
    >>>>> Many thanks
    >>>>> Leslie Isaacs
    >>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 13, 2008
    #6
  7. Leslie Isaacs

    bcap Guest

    I'm sorry, Les, I obviously haven't got my thinking head on at the moment.
    There is a much better solution:

    Encapsulate the troublesome join in another query, like this:

    SELECT
    *
    FROM
    absences AS A1 LEFT JOIN
    absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    'w');

    Save this query. Let's call it qryJoin, for the sake of argument.

    The orginal query can now be rewritten like this:

    SELECT
    A1.absencedate AS thisabsencedate,
    A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    [Position]
    FROM
    qryJoin
    GROUP BY
    A1.absencedate,
    A1.absencetype;

    And this latter query *can* be manipulated in the query design window.


    "Leslie Isaacs" <> wrote in message
    news:%23DDNSNR$...
    > 'Bcap'
    >
    > Many thanks once again for your suggestions.
    > I will go with option 4, and I will post back here with the result/any
    > problems.
    >
    > Thanks again
    > Les
    >
     
    bcap, Aug 13, 2008
    #7
  8. 'Bcap'

    Great idea! I shall proceed on that basis. I'm not in the office until this
    evening and so cannot let you know if there are any further problems until
    then.

    I really do appreciate all this help - and believe it or not I am also
    learning!

    Thanks again
    Les


    "bcap" <> wrote in message
    news:48a28e5a$0$2915$...
    > I'm sorry, Les, I obviously haven't got my thinking head on at the moment.
    > There is a much better solution:
    >
    > Encapsulate the troublesome join in another query, like this:
    >
    > SELECT
    > *
    > FROM
    > absences AS A1 LEFT JOIN
    > absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    > 'w');
    >
    > Save this query. Let's call it qryJoin, for the sake of argument.
    >
    > The orginal query can now be rewritten like this:
    >
    > SELECT
    > A1.absencedate AS thisabsencedate,
    > A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    > [Position]
    > FROM
    > qryJoin
    > GROUP BY
    > A1.absencedate,
    > A1.absencetype;
    >
    > And this latter query *can* be manipulated in the query design window.
    >
    >
    > "Leslie Isaacs" <> wrote in message
    > news:%23DDNSNR$...
    >> 'Bcap'
    >>
    >> Many thanks once again for your suggestions.
    >> I will go with option 4, and I will post back here with the result/any
    >> problems.
    >>
    >> Thanks again
    >> Les
    >>

    >
    >
     
    Leslie Isaacs, Aug 13, 2008
    #8
  9. Leslie Isaacs

    bcap Guest

    Great, I look forward to hearing how it goes.

    "Leslie Isaacs" <> wrote in message
    news:O6y0%23qR$...
    > 'Bcap'
    >
    > Great idea! I shall proceed on that basis. I'm not in the office until
    > this evening and so cannot let you know if there are any further problems
    > until then.
    >
    > I really do appreciate all this help - and believe it or not I am also
    > learning!
    >
    > Thanks again
    > Les
    >
    >
    > "bcap" <> wrote in message
    > news:48a28e5a$0$2915$...
    >> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >> moment. There is a much better solution:
    >>
    >> Encapsulate the troublesome join in another query, like this:
    >>
    >> SELECT
    >> *
    >> FROM
    >> absences AS A1 LEFT JOIN
    >> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >> 'w');
    >>
    >> Save this query. Let's call it qryJoin, for the sake of argument.
    >>
    >> The orginal query can now be rewritten like this:
    >>
    >> SELECT
    >> A1.absencedate AS thisabsencedate,
    >> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    >> [Position]
    >> FROM
    >> qryJoin
    >> GROUP BY
    >> A1.absencedate,
    >> A1.absencetype;
    >>
    >> And this latter query *can* be manipulated in the query design window.
    >>
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:%23DDNSNR$...
    >>> 'Bcap'
    >>>
    >>> Many thanks once again for your suggestions.
    >>> I will go with option 4, and I will post back here with the result/any
    >>> problems.
    >>>
    >>> Thanks again
    >>> Les
    >>>

    >>
    >>

    >
    >
     
    bcap, Aug 13, 2008
    #9
  10. 'Bcap'

    OK - finally I have been able to run the two queries that you suggested, and
    although I am sure they are working 'as they should' I'm afraid I don't
    understand the output!

    My sample table [absences] has just 97 records. qryJoin outputs 188 records,
    and within that query there are two sets of data - one set relating to A1
    and the other set to A2. The A1 set seems to show 2 records for some of the
    (single) records in table [absences] - but with values in the A2 set that
    seem not to relate to the original data in table [absences].

    Having said all that, the original query as amended below does return the
    original 97 records, the expected results in the A1 set of fields. So the
    only question is: should I be concerned with the A2 set?

    Apologies for my ignorance, but I have tried - and failed - to make sense of
    qryJoin!

    Once again, many thanks for all your help.

    Les



    "Leslie Isaacs" <> wrote in message
    news:O6y0%23qR$...
    > 'Bcap'
    >
    > Great idea! I shall proceed on that basis. I'm not in the office until
    > this evening and so cannot let you know if there are any further problems
    > until then.
    >
    > I really do appreciate all this help - and believe it or not I am also
    > learning!
    >
    > Thanks again
    > Les
    >
    >
    > "bcap" <> wrote in message
    > news:48a28e5a$0$2915$...
    >> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >> moment. There is a much better solution:
    >>
    >> Encapsulate the troublesome join in another query, like this:
    >>
    >> SELECT
    >> *
    >> FROM
    >> absences AS A1 LEFT JOIN
    >> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >> 'w');
    >>
    >> Save this query. Let's call it qryJoin, for the sake of argument.
    >>
    >> The orginal query can now be rewritten like this:
    >>
    >> SELECT
    >> A1.absencedate AS thisabsencedate,
    >> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    >> [Position]
    >> FROM
    >> qryJoin
    >> GROUP BY
    >> A1.absencedate,
    >> A1.absencetype;
    >>
    >> And this latter query *can* be manipulated in the query design window.
    >>
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:%23DDNSNR$...
    >>> 'Bcap'
    >>>
    >>> Many thanks once again for your suggestions.
    >>> I will go with option 4, and I will post back here with the result/any
    >>> problems.
    >>>
    >>> Thanks again
    >>> Les
    >>>

    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 14, 2008
    #10
  11. Leslie Isaacs

    bcap Guest

    Hi Les,

    It was a bit sloppy of me to return all the A2 fields from qryJoin, the only
    one actually needed is A2.absencedate. In other words, qryJoin could be
    rewritten like this with the same result:

    SELECT
    A1.*,
    A2.absencedate
    FROM
    absences AS A1 LEFT JOIN
    absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    'w');

    What qryJoin is doing is returning the records which then need to be grouped
    and counted by absencedate. Because some of the final counts will be
    greater than one, there are more records returned by qryJoin than were in
    the original table.

    Unfortunately what is going on here uses two powerful but subtle techniques:
    (i) an outer join (in this case a LEFT JOIN) and (ii) a self-join i.e. table
    "absences" is being joined to itself. It's very hard to explain these in a
    few lines.



    "Leslie Isaacs" <> wrote in message
    news:eeqClEm$...
    > 'Bcap'
    >
    > OK - finally I have been able to run the two queries that you suggested,
    > and although I am sure they are working 'as they should' I'm afraid I
    > don't understand the output!
    >
    > My sample table [absences] has just 97 records. qryJoin outputs 188
    > records, and within that query there are two sets of data - one set
    > relating to A1 and the other set to A2. The A1 set seems to show 2 records
    > for some of the (single) records in table [absences] - but with values in
    > the A2 set that seem not to relate to the original data in table
    > [absences].
    >
    > Having said all that, the original query as amended below does return the
    > original 97 records, the expected results in the A1 set of fields. So the
    > only question is: should I be concerned with the A2 set?
    >
    > Apologies for my ignorance, but I have tried - and failed - to make sense
    > of qryJoin!
    >
    > Once again, many thanks for all your help.
    >
    > Les
    >
    >
    >
    > "Leslie Isaacs" <> wrote in message
    > news:O6y0%23qR$...
    >> 'Bcap'
    >>
    >> Great idea! I shall proceed on that basis. I'm not in the office until
    >> this evening and so cannot let you know if there are any further problems
    >> until then.
    >>
    >> I really do appreciate all this help - and believe it or not I am also
    >> learning!
    >>
    >> Thanks again
    >> Les
    >>
    >>
    >> "bcap" <> wrote in message
    >> news:48a28e5a$0$2915$...
    >>> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >>> moment. There is a much better solution:
    >>>
    >>> Encapsulate the troublesome join in another query, like this:
    >>>
    >>> SELECT
    >>> *
    >>> FROM
    >>> absences AS A1 LEFT JOIN
    >>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >>> 'w');
    >>>
    >>> Save this query. Let's call it qryJoin, for the sake of argument.
    >>>
    >>> The orginal query can now be rewritten like this:
    >>>
    >>> SELECT
    >>> A1.absencedate AS thisabsencedate,
    >>> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    >>> [Position]
    >>> FROM
    >>> qryJoin
    >>> GROUP BY
    >>> A1.absencedate,
    >>> A1.absencetype;
    >>>
    >>> And this latter query *can* be manipulated in the query design window.
    >>>
    >>>
    >>> "Leslie Isaacs" <> wrote in message
    >>> news:%23DDNSNR$...
    >>>> 'Bcap'
    >>>>
    >>>> Many thanks once again for your suggestions.
    >>>> I will go with option 4, and I will post back here with the result/any
    >>>> problems.
    >>>>
    >>>> Thanks again
    >>>> Les
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    bcap, Aug 15, 2008
    #11
  12. 'Bcap'

    Thanks for that ... which I kind of understand!

    My problem now is that in fact I needed the query to run on another query
    (called qrysspSorN), rather than directly on table [absences]. qrysspSorN
    has the field (called 'SorN' instead of the field 'absencetype' from table
    [absences]. I have therefore amended qryJoin to the sql below ... but it
    doesn't run - I get a message "Join expression not supported"! I'm sorry
    about this - but I hope you can help.

    qryJoin is now:
    SELECT *
    FROM qrysspSorN AS A1 LEFT JOIN qrysspSorN AS A2 ON
    A1.absencedate>A2.absencedate AND A2.SorN =
    'w';

    qrysspSorN is:
    PARAMETERS [ [Forms]]![Form1]![piwstart] DateTime, [
    [Forms]]![Form1]![piwend] DateTime;
    SELECT
    CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID])
    AS AbsenceDate, Weekday([AbsenceDate],2) AS paydayno,
    Mid([Forms]![Form1]![paydays],[paydayno],1) AS workingday,
    IIf([workingday]="Y","S","N") AS SorN,
    countwaitingdays([Forms]![Form1]![employeename],[Forms]![Form1]![piwstart])
    AS waitdays, [Forms]![Form1]![employeename] AS employee,
    DCount("[absencedate]","[absences]","[absencetype]='s' and [absencedate]<#"
    & Format([absencedate],"mm/dd/yyyy") & "#" And
    [employee]=[Forms]![Form1]![employeename]) AS Expr2
    FROM tblcount
    WHERE
    (((CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]))
    Between [Forms]![Form1]![piwstart] And [Forms]![Form1]![piwend]))
    ORDER BY
    CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]);

    Many thanks for all you help so far: hope you don't give up on me!

    Les




    "bcap" <> wrote in message
    news:48a53cec$0$2920$...
    > Hi Les,
    >
    > It was a bit sloppy of me to return all the A2 fields from qryJoin, the
    > only one actually needed is A2.absencedate. In other words, qryJoin could
    > be rewritten like this with the same result:
    >
    > SELECT
    > A1.*,
    > A2.absencedate
    > FROM
    > absences AS A1 LEFT JOIN
    > absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    > 'w');
    >
    > What qryJoin is doing is returning the records which then need to be
    > grouped and counted by absencedate. Because some of the final counts will
    > be greater than one, there are more records returned by qryJoin than were
    > in the original table.
    >
    > Unfortunately what is going on here uses two powerful but subtle
    > techniques: (i) an outer join (in this case a LEFT JOIN) and (ii) a
    > self-join i.e. table "absences" is being joined to itself. It's very hard
    > to explain these in a few lines.
    >
    >
    >
    > "Leslie Isaacs" <> wrote in message
    > news:eeqClEm$...
    >> 'Bcap'
    >>
    >> OK - finally I have been able to run the two queries that you suggested,
    >> and although I am sure they are working 'as they should' I'm afraid I
    >> don't understand the output!
    >>
    >> My sample table [absences] has just 97 records. qryJoin outputs 188
    >> records, and within that query there are two sets of data - one set
    >> relating to A1 and the other set to A2. The A1 set seems to show 2
    >> records for some of the (single) records in table [absences] - but with
    >> values in the A2 set that seem not to relate to the original data in
    >> table [absences].
    >>
    >> Having said all that, the original query as amended below does return the
    >> original 97 records, the expected results in the A1 set of fields. So the
    >> only question is: should I be concerned with the A2 set?
    >>
    >> Apologies for my ignorance, but I have tried - and failed - to make sense
    >> of qryJoin!
    >>
    >> Once again, many thanks for all your help.
    >>
    >> Les
    >>
    >>
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:O6y0%23qR$...
    >>> 'Bcap'
    >>>
    >>> Great idea! I shall proceed on that basis. I'm not in the office until
    >>> this evening and so cannot let you know if there are any further
    >>> problems until then.
    >>>
    >>> I really do appreciate all this help - and believe it or not I am also
    >>> learning!
    >>>
    >>> Thanks again
    >>> Les
    >>>
    >>>
    >>> "bcap" <> wrote in message
    >>> news:48a28e5a$0$2915$...
    >>>> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >>>> moment. There is a much better solution:
    >>>>
    >>>> Encapsulate the troublesome join in another query, like this:
    >>>>
    >>>> SELECT
    >>>> *
    >>>> FROM
    >>>> absences AS A1 LEFT JOIN
    >>>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype
    >>>> = 'w');
    >>>>
    >>>> Save this query. Let's call it qryJoin, for the sake of argument.
    >>>>
    >>>> The orginal query can now be rewritten like this:
    >>>>
    >>>> SELECT
    >>>> A1.absencedate AS thisabsencedate,
    >>>> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1) AS
    >>>> [Position]
    >>>> FROM
    >>>> qryJoin
    >>>> GROUP BY
    >>>> A1.absencedate,
    >>>> A1.absencetype;
    >>>>
    >>>> And this latter query *can* be manipulated in the query design window.
    >>>>
    >>>>
    >>>> "Leslie Isaacs" <> wrote in message
    >>>> news:%23DDNSNR$...
    >>>>> 'Bcap'
    >>>>>
    >>>>> Many thanks once again for your suggestions.
    >>>>> I will go with option 4, and I will post back here with the result/any
    >>>>> problems.
    >>>>>
    >>>>> Thanks again
    >>>>> Les
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 15, 2008
    #12
  13. Leslie Isaacs

    bcap Guest

    No problem Les.

    All that's happened here is that the Access query designer has pulled a fast
    one on you. It has removed the brackets from the JOIN expression, something
    that it has an annoying tendency to do. But, those brackets are in fact
    required, so pop them back in!

    SELECT *
    FROM qrysspSorN AS A1 LEFT JOIN qrysspSorN AS A2 ON
    (A1.absencedate>A2.absencedate AND A2.SorN = 'w');

    I would guess that the final 'w' should now be either an 'S' or an 'N', but
    I don't know which.

    "Leslie Isaacs" <> wrote in message
    news:u%23lshuv$...
    > 'Bcap'
    >
    > Thanks for that ... which I kind of understand!
    >
    > My problem now is that in fact I needed the query to run on another query
    > (called qrysspSorN), rather than directly on table [absences]. qrysspSorN
    > has the field (called 'SorN' instead of the field 'absencetype' from table
    > [absences]. I have therefore amended qryJoin to the sql below ... but it
    > doesn't run - I get a message "Join expression not supported"! I'm sorry
    > about this - but I hope you can help.
    >
    > qryJoin is now:
    > SELECT *
    > FROM qrysspSorN AS A1 LEFT JOIN qrysspSorN AS A2 ON
    > A1.absencedate>A2.absencedate AND A2.SorN =
    > 'w';
    >
    > qrysspSorN is:
    > PARAMETERS [ [Forms]]![Form1]![piwstart] DateTime, [
    > [Forms]]![Form1]![piwend] DateTime;
    > SELECT
    > CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID])
    > AS AbsenceDate, Weekday([AbsenceDate],2) AS paydayno,
    > Mid([Forms]![Form1]![paydays],[paydayno],1) AS workingday,
    > IIf([workingday]="Y","S","N") AS SorN,
    > countwaitingdays([Forms]![Form1]![employeename],[Forms]![Form1]![piwstart])
    > AS waitdays, [Forms]![Form1]![employeename] AS employee,
    > DCount("[absencedate]","[absences]","[absencetype]='s' and
    > [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#" And
    > [employee]=[Forms]![Form1]![employeename]) AS Expr2
    > FROM tblcount
    > WHERE
    > (((CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]))
    > Between [Forms]![Form1]![piwstart] And [Forms]![Form1]![piwend]))
    > ORDER BY
    > CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]);
    >
    > Many thanks for all you help so far: hope you don't give up on me!
    >
    > Les
    >
    >
    >
    >
    > "bcap" <> wrote in message
    > news:48a53cec$0$2920$...
    >> Hi Les,
    >>
    >> It was a bit sloppy of me to return all the A2 fields from qryJoin, the
    >> only one actually needed is A2.absencedate. In other words, qryJoin
    >> could be rewritten like this with the same result:
    >>
    >> SELECT
    >> A1.*,
    >> A2.absencedate
    >> FROM
    >> absences AS A1 LEFT JOIN
    >> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >> 'w');
    >>
    >> What qryJoin is doing is returning the records which then need to be
    >> grouped and counted by absencedate. Because some of the final counts
    >> will be greater than one, there are more records returned by qryJoin than
    >> were in the original table.
    >>
    >> Unfortunately what is going on here uses two powerful but subtle
    >> techniques: (i) an outer join (in this case a LEFT JOIN) and (ii) a
    >> self-join i.e. table "absences" is being joined to itself. It's very
    >> hard to explain these in a few lines.
    >>
    >>
    >>
    >> "Leslie Isaacs" <> wrote in message
    >> news:eeqClEm$...
    >>> 'Bcap'
    >>>
    >>> OK - finally I have been able to run the two queries that you suggested,
    >>> and although I am sure they are working 'as they should' I'm afraid I
    >>> don't understand the output!
    >>>
    >>> My sample table [absences] has just 97 records. qryJoin outputs 188
    >>> records, and within that query there are two sets of data - one set
    >>> relating to A1 and the other set to A2. The A1 set seems to show 2
    >>> records for some of the (single) records in table [absences] - but with
    >>> values in the A2 set that seem not to relate to the original data in
    >>> table [absences].
    >>>
    >>> Having said all that, the original query as amended below does return
    >>> the original 97 records, the expected results in the A1 set of fields.
    >>> So the only question is: should I be concerned with the A2 set?
    >>>
    >>> Apologies for my ignorance, but I have tried - and failed - to make
    >>> sense of qryJoin!
    >>>
    >>> Once again, many thanks for all your help.
    >>>
    >>> Les
    >>>
    >>>
    >>>
    >>> "Leslie Isaacs" <> wrote in message
    >>> news:O6y0%23qR$...
    >>>> 'Bcap'
    >>>>
    >>>> Great idea! I shall proceed on that basis. I'm not in the office until
    >>>> this evening and so cannot let you know if there are any further
    >>>> problems until then.
    >>>>
    >>>> I really do appreciate all this help - and believe it or not I am also
    >>>> learning!
    >>>>
    >>>> Thanks again
    >>>> Les
    >>>>
    >>>>
    >>>> "bcap" <> wrote in message
    >>>> news:48a28e5a$0$2915$...
    >>>>> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >>>>> moment. There is a much better solution:
    >>>>>
    >>>>> Encapsulate the troublesome join in another query, like this:
    >>>>>
    >>>>> SELECT
    >>>>> *
    >>>>> FROM
    >>>>> absences AS A1 LEFT JOIN
    >>>>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype
    >>>>> = 'w');
    >>>>>
    >>>>> Save this query. Let's call it qryJoin, for the sake of argument.
    >>>>>
    >>>>> The orginal query can now be rewritten like this:
    >>>>>
    >>>>> SELECT
    >>>>> A1.absencedate AS thisabsencedate,
    >>>>> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1)
    >>>>> AS [Position]
    >>>>> FROM
    >>>>> qryJoin
    >>>>> GROUP BY
    >>>>> A1.absencedate,
    >>>>> A1.absencetype;
    >>>>>
    >>>>> And this latter query *can* be manipulated in the query design window.
    >>>>>
    >>>>>
    >>>>> "Leslie Isaacs" <> wrote in message
    >>>>> news:%23DDNSNR$...
    >>>>>> 'Bcap'
    >>>>>>
    >>>>>> Many thanks once again for your suggestions.
    >>>>>> I will go with option 4, and I will post back here with the
    >>>>>> result/any problems.
    >>>>>>
    >>>>>> Thanks again
    >>>>>> Les
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    bcap, Aug 16, 2008
    #13
  14. 'Bcap'

    This just goes on and on!

    Having put those brackets back in, qryJoin runs, although in the output I
    get #error in some of the A2 fields for all records. The A1 fields look OK.
    Also - and more of a show-stopper - the original query, which I have amended
    to

    SELECT A1.absencedate AS thisabsencedate,
    A1.SorN, -1*(A1.SorN='s')*(COUNT(A2.absencedate)+1) AS [Position] FROM
    qryJoin GROUP BY A1.absencedate, A1.SorN;

    is now "typed incorrectly or is too complex ..."!!!!

    Is this problem just not meant to be?!

    As usual, many thanks for everything.
    Les



    "bcap" <> wrote in message
    news:48a68065$0$2517$...
    > No problem Les.
    >
    > All that's happened here is that the Access query designer has pulled a
    > fast one on you. It has removed the brackets from the JOIN expression,
    > something that it has an annoying tendency to do. But, those brackets are
    > in fact required, so pop them back in!
    >
    > SELECT *
    > FROM qrysspSorN AS A1 LEFT JOIN qrysspSorN AS A2 ON
    > (A1.absencedate>A2.absencedate AND A2.SorN = 'w');
    >
    > I would guess that the final 'w' should now be either an 'S' or an 'N',
    > but I don't know which.
    >
    > "Leslie Isaacs" <> wrote in message
    > news:u%23lshuv$...
    >> 'Bcap'
    >>
    >> Thanks for that ... which I kind of understand!
    >>
    >> My problem now is that in fact I needed the query to run on another query
    >> (called qrysspSorN), rather than directly on table [absences]. qrysspSorN
    >> has the field (called 'SorN' instead of the field 'absencetype' from
    >> table [absences]. I have therefore amended qryJoin to the sql below ...
    >> but it doesn't run - I get a message "Join expression not supported"! I'm
    >> sorry about this - but I hope you can help.
    >>
    >> qryJoin is now:
    >> SELECT *
    >> FROM qrysspSorN AS A1 LEFT JOIN qrysspSorN AS A2 ON
    >> A1.absencedate>A2.absencedate AND A2.SorN =
    >> 'w';
    >>
    >> qrysspSorN is:
    >> PARAMETERS [ [Forms]]![Form1]![piwstart] DateTime, [
    >> [Forms]]![Form1]![piwend] DateTime;
    >> SELECT
    >> CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID])
    >> AS AbsenceDate, Weekday([AbsenceDate],2) AS paydayno,
    >> Mid([Forms]![Form1]![paydays],[paydayno],1) AS workingday,
    >> IIf([workingday]="Y","S","N") AS SorN,
    >> countwaitingdays([Forms]![Form1]![employeename],[Forms]![Form1]![piwstart])
    >> AS waitdays, [Forms]![Form1]![employeename] AS employee,
    >> DCount("[absencedate]","[absences]","[absencetype]='s' and
    >> [absencedate]<#" & Format([absencedate],"mm/dd/yyyy") & "#" And
    >> [employee]=[Forms]![Form1]![employeename]) AS Expr2
    >> FROM tblcount
    >> WHERE
    >> (((CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]))
    >> Between [Forms]![Form1]![piwstart] And [Forms]![Form1]![piwend]))
    >> ORDER BY
    >> CDate([Forms]![Form1]![piwstart]-Weekday([Forms]![Form1]![piwstart])+1*[tblCount].[CountID]);
    >>
    >> Many thanks for all you help so far: hope you don't give up on me!
    >>
    >> Les
    >>
    >>
    >>
    >>
    >> "bcap" <> wrote in message
    >> news:48a53cec$0$2920$...
    >>> Hi Les,
    >>>
    >>> It was a bit sloppy of me to return all the A2 fields from qryJoin, the
    >>> only one actually needed is A2.absencedate. In other words, qryJoin
    >>> could be rewritten like this with the same result:
    >>>
    >>> SELECT
    >>> A1.*,
    >>> A2.absencedate
    >>> FROM
    >>> absences AS A1 LEFT JOIN
    >>> absences AS A2 ON (A1.absencedate>A2.absencedate AND A2.absencetype =
    >>> 'w');
    >>>
    >>> What qryJoin is doing is returning the records which then need to be
    >>> grouped and counted by absencedate. Because some of the final counts
    >>> will be greater than one, there are more records returned by qryJoin
    >>> than were in the original table.
    >>>
    >>> Unfortunately what is going on here uses two powerful but subtle
    >>> techniques: (i) an outer join (in this case a LEFT JOIN) and (ii) a
    >>> self-join i.e. table "absences" is being joined to itself. It's very
    >>> hard to explain these in a few lines.
    >>>
    >>>
    >>>
    >>> "Leslie Isaacs" <> wrote in message
    >>> news:eeqClEm$...
    >>>> 'Bcap'
    >>>>
    >>>> OK - finally I have been able to run the two queries that you
    >>>> suggested, and although I am sure they are working 'as they should' I'm
    >>>> afraid I don't understand the output!
    >>>>
    >>>> My sample table [absences] has just 97 records. qryJoin outputs 188
    >>>> records, and within that query there are two sets of data - one set
    >>>> relating to A1 and the other set to A2. The A1 set seems to show 2
    >>>> records for some of the (single) records in table [absences] - but with
    >>>> values in the A2 set that seem not to relate to the original data in
    >>>> table [absences].
    >>>>
    >>>> Having said all that, the original query as amended below does return
    >>>> the original 97 records, the expected results in the A1 set of fields.
    >>>> So the only question is: should I be concerned with the A2 set?
    >>>>
    >>>> Apologies for my ignorance, but I have tried - and failed - to make
    >>>> sense of qryJoin!
    >>>>
    >>>> Once again, many thanks for all your help.
    >>>>
    >>>> Les
    >>>>
    >>>>
    >>>>
    >>>> "Leslie Isaacs" <> wrote in message
    >>>> news:O6y0%23qR$...
    >>>>> 'Bcap'
    >>>>>
    >>>>> Great idea! I shall proceed on that basis. I'm not in the office until
    >>>>> this evening and so cannot let you know if there are any further
    >>>>> problems until then.
    >>>>>
    >>>>> I really do appreciate all this help - and believe it or not I am also
    >>>>> learning!
    >>>>>
    >>>>> Thanks again
    >>>>> Les
    >>>>>
    >>>>>
    >>>>> "bcap" <> wrote in message
    >>>>> news:48a28e5a$0$2915$...
    >>>>>> I'm sorry, Les, I obviously haven't got my thinking head on at the
    >>>>>> moment. There is a much better solution:
    >>>>>>
    >>>>>> Encapsulate the troublesome join in another query, like this:
    >>>>>>
    >>>>>> SELECT
    >>>>>> *
    >>>>>> FROM
    >>>>>> absences AS A1 LEFT JOIN
    >>>>>> absences AS A2 ON (A1.absencedate>A2.absencedate AND
    >>>>>> A2.absencetype = 'w');
    >>>>>>
    >>>>>> Save this query. Let's call it qryJoin, for the sake of argument.
    >>>>>>
    >>>>>> The orginal query can now be rewritten like this:
    >>>>>>
    >>>>>> SELECT
    >>>>>> A1.absencedate AS thisabsencedate,
    >>>>>> A1.absencetype, -1*(A1.absencetype='w')*(COUNT(A2.absencedate)+1)
    >>>>>> AS [Position]
    >>>>>> FROM
    >>>>>> qryJoin
    >>>>>> GROUP BY
    >>>>>> A1.absencedate,
    >>>>>> A1.absencetype;
    >>>>>>
    >>>>>> And this latter query *can* be manipulated in the query design
    >>>>>> window.
    >>>>>>
    >>>>>>
    >>>>>> "Leslie Isaacs" <> wrote in message
    >>>>>> news:%23DDNSNR$...
    >>>>>>> 'Bcap'
    >>>>>>>
    >>>>>>> Many thanks once again for your suggestions.
    >>>>>>> I will go with option 4, and I will post back here with the
    >>>>>>> result/any problems.
    >>>>>>>
    >>>>>>> Thanks again
    >>>>>>> Les
    >>>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Leslie Isaacs, Aug 16, 2008
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Denyse

    Querying records prior to a certain period

    Denyse, Jul 13, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    487
    [MVP] S. Clark
    Jul 14, 2003
  2. sara

    Possible to Get prior records?

    sara, Mar 20, 2004, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    171
    Nikos Yannacopoulos
    Mar 29, 2004
  3. ave
    Replies:
    6
    Views:
    156
  4. Guest
    Replies:
    2
    Views:
    295
    Van T. Dinh
    May 27, 2005
  5. Guest
    Replies:
    2
    Views:
    279
    Guest
    Nov 8, 2006
Loading...

Share This Page