Too complex

[Leslie Isaacs]

Hello All

I have a query which is apparently "typed incorrectly or is too complex
....". The sql is:

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;

qryJoin, on which the above query is based, runs fine.
Can anyone see the problem?

Hope someone can help.
Many thanks
Leslie Isaacs

 

[Allen Browne]

Suggestions:

1. Position is a reserved word:
http://allenbrowne.com/AppIssueBadWord.html#P
Use a different alias.

2. What are A1 and A2? If you have duplicate field name outputs in qryJoin,
alias them to avoid the duplicates, and use the query name as the prefix.

3. Presumably SorN is a Text field (not a Number field.) The expression:
A1.SorN='s'
will yield True or False (or Null), so presumably you want the count only
where SortN is true, and otherwise it counts as zero?

So, perhaps something like this:
SELECT qryJoin.absencedate AS thisabsencedate,
qryJoin.SorN,
IIf(qryJoin.SorN='s', Count(qryJoin.absencedate) + 1, 0) AS Pos
FROM qryJoin
GROUP BY qryJoin.absencedate, qryJoin.SorN;

Hope I've understood what you intended.