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.

 

[Leslie Isaacs]

Hello Allen

Many thanks for your reply.

My original problem was that I needed to 'rank' the position of each 's'
value in field 'absencetype' from a query [qrysspSorN]

e.g. if [qrysspSorN] returned the fields 'absencedate' and 'absencetype'
shown below, the new query need to return the 'position' values also shown
below.

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

qryJoin was suggested to me by 'Bcap' in this forum, and is quite technical
(by my standards!):

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

This was really just a device to encapsulate the join in one query that I
could then use in another query which I could then manipulate in design view
(as opposed to sql view, because I am not too familiar with sql: qryJoin
will not open in design view).

Further to your suggestion I now have:
SELECT qryJoin.A1.AbsenceDate AS thisabsencedate, qryJoin.A1.SorN AS Expr1,
IIf([qryJoin].A1.SorN='s',Count([qryJoin].A1.absencedate)+1,0) AS Pos
FROM qryJoin
GROUP BY qryJoin.A1.AbsenceDate, qryJoin.A1.SorN;
which runs OK - but it returns a 2 in the 'Pos' field for every record where
'SorN' = "S" (rather then 'ranking' the "S" records which is what is
needed).

Have I done something stupid?!
Hope you can help.
Many thanks
Les

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Lord Kelvan]

as a note generally when you get the error typed correctally ot is too
complex it is a date problem other problems can cause it but dates
seem to be the worse

in this case you appear to be doing calculations on a character

-1*(A1.SorN='s')

so this entire line is wrong

-1*(A1.SorN='s')*(COUNT(A2.absencedate)+1) AS [Position]


what woudl be easyest for us to do it for you to give some raw data
and then format it how you want it to appear after the query runs to
make it eaiser for us to understand


though as a guess try

SELECT absencedate AS thisabsencedate,
SorN,iif(sorn = "s",COUNT(absencedate)),0) AS [Position] FROM
qryJoin GROUP BY absencedate,SorN;

but thats just a guess

 

[Allen Browne]

Here's all the suggestions I have for ranking or numbering your records:
http://allenbrowne.com/ranking.html

 

[Leslie Isaacs]

Hello Allen

Thanks for this. I'm not sure that I can use any of these methods though, as
it's important that I only rank the records where 'absencetype' = "s" - but
this needs to be done in the full query that includes all the other records!

Hope you can help.
Many thanks
Les

Here's all the suggestions I have for ranking or numbering your records:
http://allenbrowne.com/ranking.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen

Many thanks for your reply.

My original problem was that I needed to 'rank' the position of each 's'
value in field 'absencetype' from a query [qrysspSorN]