Counting records 'prior to' other records

[Leslie Isaacs]

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]

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]

Hello 'Bcap'

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

Thanks again
Les

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;

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]

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

Hello 'Bcap'

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

Thanks again
Les

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;

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]

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.

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

Hello 'Bcap'

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

Thanks again
Les

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;

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]

'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

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.

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

Hello 'Bcap'

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

Thanks again
Les

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;

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]

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]

'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

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.

'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]

Great, I look forward to hearing how it goes.

'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

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.

'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]

'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

'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

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.

'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]

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.

'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

'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

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.


'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]

'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

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.

'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

'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


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.


'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]

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.

'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

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.

'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



'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


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.


'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]

'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

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.

'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

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.



'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



'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


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.


'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