Creating detail values & fields in a query

  • Thread starter Thread starter charlie
  • Start date Start date
C

charlie

I have a LARGE HISTORY table that records an attendance
code (a, b, n, t,m) Each code has a meaning. At the end
of the week we have to know the total ABSENT (CODEs a, b,
n, m), TARTY (code T) AND PRESENT (blank or null).

I am trying to develop a query that will have some table
info and 3 new fields ACOUNT, TCOUNT, and PCOUNT. The
values will be based upon the ATTENDCODE. If the code is
A or N OR M then ACount is 1 and the others are 0, etc.
This will be used to create many reports. Since this is a
large table, I do not want to have cal. fields in the
table.

This is my second posting of this request for help. I did
not describe it very well in the first. We would
appreciate any ideas or suggestions. Thanks

..
 
you need to build a statement in the query using the IIF
formula, like this:
ACOUNT: iif(CODE="a" or CODE="b" or CODE="n" or
CODE="m",1,0)
That says, if the code is a or b or n or m, the value of
ACOUNT will be 1, otherwise 0.
 
The ACOUNT field in the query could be:
ACOUNT: IIF([ATTENDCODE]="A" or ([ATTENDCODE]="N" or
([ATTENDCODE]="M",1,0)
This will work, you can add a modified version for the
other fieds.
Hope this helps.
Fons
 
I have a LARGE HISTORY table that records an attendance
code (a, b, n, t,m) Each code has a meaning. At the end
of the week we have to know the total ABSENT (CODEs a, b,
n, m), TARTY (code T) AND PRESENT (blank or null).

I am trying to develop a query that will have some table
info and 3 new fields ACOUNT, TCOUNT, and PCOUNT. The
values will be based upon the ATTENDCODE. If the code is
A or N OR M then ACount is 1 and the others are 0, etc.
This will be used to create many reports. Since this is a
large table, I do not want to have cal. fields in the
table.
Click to expand...

Since tables shouldn't have calculated fields in any case, this is a
good idea! I can think of two ways to do this, and you may want to try
both to see which gives better performance.

One (which may be too slow) uses IIF. Put calculated fields in the
query:

ACount: IIF([AttendCode] IN ('a', 'b', 'n', 'm'), 1, 0)
TCount: IIF([AttendCode] = 't', 1, 0)
PCount: IIF([AttendCode] IS NULL, 1, 0)

It may instead be better to create a lookup table with one row for
each code, and three integer fields with values 1 or 0; just join this
table to your query:

CodeCount
AttendCode Text Primary Key
Absent Integer
Tardy Integer

a 1 0
b 1 0
n 1 0
m 1 0
t 0 1

Use a Left Join ("show all records in Attendance and matching records
in CodeCount") and include a calculated field:

Present: NZ([Codecount].[AttendCode], 1)
 
Back
Top