Need to sum based on conditions

[Gina]

Hi All!
I'm trying to create a database to track employee
violations (such as Absences, Tardies, etc.)
I have the basics down, but here's where I'm running into
a roadblock:
I have an Event field and a Type field. The Event field
is text, and the Type field is a calculation: IIf(Date()-
[EventDate]>=365,"Archive","Current"). (In other words,
if an Event is more than 1 year old, it's Archived.)

I want to be able to sum all the Current Events, and then
further break them down into Current Absences, Current
Tardies, etc. I tried doing it in a query, but I was
using a lot of IIf formulas, and I ended up getting this:
Name CurrAbs CurrTardies
John Doe 1 0
John Doe 0 1

When what I want is this:
Name CurrAbs CurrTardies
John Doe 1 1

Can someone help me achieve this?

Thanks in advance.

 

[Casey]

Gina,

If you want the calculations, you can use a query for
each sum you are seeking, assign each of those queries to
a form, then assign each of the forms as a subform of your
main form that holds that employees' other information.

For example to create a query for the Absences for
John Doe, you would first open the new query, and assign
the Employee field (no. or whatever) field in a column,
then in the next column place the CurrAbs field in the
query. Now, there will be a funny looking "E" like figure
at the top of the query design screen. This is
the "totals" option. Press that option, and you will see
a total row in the query design grid. For the Employee
Number or whatever you are using to identify in the first
column, select "GroupBy" in the total specification for
that column. In the next column select "Sum" for the total
specification. Now, in the third column, place the field
[EventDate], and in the total specification for that
column, specify "Where". Also in that third column,
specify in the "Criteria" specification >=Date()-365.

That will yield a calculation for the CurrAbs. You
can do another query for the CurrTardies using a similar
strategy, but using the CurrTardies field.

After you create both of your queries, you can assign
them to forms. On those forms, just specify the
CurrTardies or CurrAbs field. You can place the text box
on the form for that field on the upper left of the
report, eliminate any headings of the report, and size the
report to be very small, just revealing the text box
holding your calculation. You can do that for both
calculations. After you have created the tiny forms, open
your main form in design view, and select the
subform/subreport option at the top of the screen. Using
that option, designate where on the main form you want to
place the first subform, then go through the use of the
wizard iterating the common field of the main and subform
being placed as the common Employee No. field.

After you are done using the wizard to place the
subform, then the subform should appear with your
calculation on it in the main form. You may have to
adjust the text box that holds the subform to reveal it,
but it can be adjusted.

Then, do the same thing for your other subform, and
you will be all set.

That is one way to do it. There are probably others,
but I have used this method successfully.

I hope I have helped.

Have a nice day.

Casey

-----Original Message-----
Hi All!
I'm trying to create a database to track employee
violations (such as Absences, Tardies, etc.)
I have the basics down, but here's where I'm running into
a roadblock:
I have an Event field and a Type field. The Event field
is text, and the Type field is a calculation: IIf(Date()-
[EventDate]>=365,"Archive","Current"). (In other words,
if an Event is more than 1 year old, it's Archived.)

I want to be able to sum all the Current Events, and then
further break them down into Current Absences, Current
Tardies, etc. I tried doing it in a query, but I was
using a lot of IIf formulas, and I ended up getting this:
Name CurrAbs CurrTardies
John Doe 1 0
John Doe 0 1

When what I want is this:
Name CurrAbs CurrTardies
John Doe 1 1

Can someone help me achieve this?

Thanks in advance.
.

 

[John Vinson]

When what I want is this:
Name CurrAbs CurrTardies
John Doe 1 1

Can someone help me achieve this?

If you don't want to see the archive records in this query, why not
simply put a criterion of

= DateAdd("yyyy", -1, Date())

on the Event Date field? Access will then retrieve only current
records and your totals will come out correct.

 

[Gina]

Thanks so much for the advice...I tried the suggestions
and they work well!
Thanks again!

-----Original Message-----