PC Review


Reply
Thread Tools Rate Thread

Counting records 'prior to' other records

 
 
Leslie Isaacs
Guest
Posts: n/a
 
      12th Aug 2008
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



 
Reply With Quote
 
 
 
 
bcap
Guest
Posts: n/a
 
      12th Aug 2008
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" <(E-Mail Removed)> wrote in message
news:u3rCBCF$(E-Mail Removed)...
> 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
>
>
>



 
Reply With Quote
 
 
 
 
Leslie Isaacs
Guest
Posts: n/a
 
      12th Aug 2008
Hello 'Bcap'

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

Thanks again
Les

"bcap" <(E-Mail Removed)> wrote in message
news:48a17101$0$2925$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:u3rCBCF$(E-Mail Removed)...
>> 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
>>
>>
>>

>
>



 
Reply With Quote
 
Leslie Isaacs
Guest
Posts: n/a
 
      12th Aug 2008
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" <(E-Mail Removed)> wrote in message
news:edhZW2J$(E-Mail Removed)...
> Hello 'Bcap'
>
> Brilliant!
> Many thanks for your suggestions - both of them!
> I will use the 2nd.
>
> Thanks again
> Les
>
> "bcap" <(E-Mail Removed)> wrote in message
> news:48a17101$0$2925$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:u3rCBCF$(E-Mail Removed)...
>>> 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
>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
bcap
Guest
Posts: n/a
 
      12th Aug 2008
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" <(E-Mail Removed)> wrote in message
news:eb12unM$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:edhZW2J$(E-Mail Removed)...
>> Hello 'Bcap'
>>
>> Brilliant!
>> Many thanks for your suggestions - both of them!
>> I will use the 2nd.
>>
>> Thanks again
>> Les
>>
>> "bcap" <(E-Mail Removed)> wrote in message
>> news:48a17101$0$2925$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:u3rCBCF$(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
Leslie Isaacs
Guest
Posts: n/a
 
      13th Aug 2008
'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" <(E-Mail Removed)> wrote in message
news:48a212de$0$2509$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eb12unM$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:edhZW2J$(E-Mail Removed)...
>>> Hello 'Bcap'
>>>
>>> Brilliant!
>>> Many thanks for your suggestions - both of them!
>>> I will use the 2nd.
>>>
>>> Thanks again
>>> Les
>>>
>>> "bcap" <(E-Mail Removed)> wrote in message
>>> news:48a17101$0$2925$(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:u3rCBCF$(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
bcap
Guest
Posts: n/a
 
      13th Aug 2008
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" <(E-Mail Removed)> wrote in message
news:%23DDNSNR$(E-Mail Removed)...
> '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
>



 
Reply With Quote
 
Leslie Isaacs
Guest
Posts: n/a
 
      13th Aug 2008
'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" <(E-Mail Removed)> wrote in message
news:48a28e5a$0$2915$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23DDNSNR$(E-Mail Removed)...
>> '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
>>

>
>



 
Reply With Quote
 
bcap
Guest
Posts: n/a
 
      13th Aug 2008
Great, I look forward to hearing how it goes.

"Leslie Isaacs" <(E-Mail Removed)> wrote in message
news:O6y0%23qR$(E-Mail Removed)...
> '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" <(E-Mail Removed)> wrote in message
> news:48a28e5a$0$2915$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%23DDNSNR$(E-Mail Removed)...
>>> '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
>>>

>>
>>

>
>



 
Reply With Quote
 
Leslie Isaacs
Guest
Posts: n/a
 
      14th Aug 2008
'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" <(E-Mail Removed)> wrote in message
news:O6y0%23qR$(E-Mail Removed)...
> '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" <(E-Mail Removed)> wrote in message
> news:48a28e5a$0$2915$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%23DDNSNR$(E-Mail Removed)...
>>> '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
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Rows Then Counting Values in Columns Michael via OfficeKB.com Microsoft Excel Programming 1 1st Jun 2005 04:10 PM
Counting names in a column but counting duplicate names once =?Utf-8?B?VEJvZQ==?= Microsoft Excel Misc 9 11th May 2005 11:24 PM
12,000 and counting - dead, 1 Million and counting - homeless David Candy Windows XP General 34 1st Jan 2005 11:27 PM
counting rows counting empty rows cparsons Microsoft Excel Misc 1 10th Nov 2004 08:12 PM
Counting records if all associated records have a date =?Utf-8?B?RGFuaWVsIFA=?= Microsoft Access Queries 1 21st Apr 2004 04:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:47 PM.