help with conditional expressions or calculations

[Guest]

i have a table titled "STOP" that has 10 columns:
Column 1: S (S is populated in cells when service is perfomed on Sunday)
Column2: M (M is populated in cells when service is perfomed on Monday)
Column3:T (T is populated in cells when service is perfomed on Tuesday)
Column4:W (W is populated in cells when service is perfomed on Wednesday)
Column5:T (T is populated in cells when service is perfomed on Thursday)
Column6:F (F is populated in cells when service is perfomed on Friday)

THE WAY OUR DATA IS CONFIGURED, THE ONLY WAY TO CAPTURE SATERDAY SERVICE
DAYS IS BY CREATING THE BELOW COLUMNS. For sume reason S which is clearly in
the 7th text position, when translated into Access, if there aren't letters
ahead, S for Saterday could appear in the 4, 5th, 6th, or 7th text positions.
This is only the case wtih Saterday. To get around this I created the below
columns which captures Saterday service but also picks up other days in the
below columns:

Column7 titled SAT1:contains the letter in the 4th text position of SMTWTFS
(could be Ws,Ts, Fs and Ss)

Column8 titled SAT12:contains the letter in the 5th text position could be
Ws,Ts, Fs and Ss
Column9 Titled SAT3:contains the letter in the 6th text position could be
Ws,Ts, Fs and Ss
Column10 Titled SAT4:contains the letter in the 7th text position could be
Ws,Ts, Fs and Ss

I am trying to set up a query that will only count the numbers of times S
appears in column 7, column8, column9, and Column 10 because there are some
Ws,Fs and other leters mixed in those columns.

I created a query and selected count for each columns 1,2,3,4,5 and 6. I now
need to count only Ss in columns SAT1, SAT2, SAT3 and SAT4. However, there
are other letters in these columns besides S.

I created the following where clause in my query:
Field:SAT1
Table:STOP
Total:Where
Sort: blank
Criteria: IIF(Left([STOP]![SAT1],1)="S",Count([SAT1]![SAT1]),""))

AND GOT THE FOLLOIWNG ERROR: CANNOT HAVE AGGREGATE FUNCTION IN WHERE CLAUSE

I tried using the Left function and received the following message:
YOU TRIED TO EXECUTE A QUERY THAT DOES NOT INCLUDE THE SPECIFIED EXPRESSION.

any help would be greatly appreciated.

 

[Douglas J. Steele]

Do yourself a huge favour and redesign your table so that it's properly
normalized. You should not be using columns the way you are: you should have
separate rows (in a second table) for each populated day.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Socal Analyst looking for help"