IF then SUM

[Tom Cunningham]

I have four queries, each containing students scores which are grouped by week using the DatePart function. I would like to create a query to total scores from each query by week. Basically, IF Week = 10 SUM QueryA, QueryB, QueryC, QueryD. Below is an example of the four queries which I would like to bring together based on the week. I currently have them joined on ID and on WEEK.
Qry_TIMETable_A
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 25
42 Johnson 11 45
51 Badley 12 30

Qry_TIMETable_B
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 35
42 Johnson 11 40
51 Badley 12 20

Qry_TIMETable_C
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 20
42 Johnson 11 15
51 Badley 12 25

Qry_TIMETable_D
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 20
42 Johnson 11 40
51 Badley 12 35

From these four queries I would like to create one query which contains student?s totals by week. For example: IF WEEK=10 then SUM ATTENDANCE from each of the four queries. This would populate the WEEK TEN column. The next column would be, IF WEEK=11 then SUM ATTENDANCE from each of the four queries. This would continue on?.Below is an example of what I?m looking for.

QryWEEKTotal
ID# LASTNAME WEEK_10 WEEK_11 WEEK_12??.
34 Smith 100
42 Johnson 140
51 Badley 110

In the actual query students have scores for each week so it wouldn?t look so sparse but this is basically it. I have gotten some help with the use of an IF then SUM expression but have not been able to get it to work and hoped that a clearer picture of what I was doing would help.

Thank you in advance for your assistance.

Tom

 

[Bob Barrows]

I have four queries, each containing students scores which are
grouped by week using the DatePart function. I would like to create a
query to total scores from each query by week. Basically, IF Week =
10 SUM QueryA, QueryB, QueryC, QueryD. Below is an example of the
four queries which I would like to bring together based on the week.
I currently have them joined on ID and on WEEK.

Qry_TIMETable_A
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 25
42 Johnson 11 45
51 Badley 12 30

Qry_TIMETable_B
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 35
42 Johnson 11 40
51 Badley 12 20

Qry_TIMETable_C
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 20
42 Johnson 11 15
51 Badley 12 25

Qry_TIMETable_D
ID# LASTNAME WEEK ATTENDANCE
34 Smith 10 20
42 Johnson 11 40
51 Badley 12 35

From these four queries I would like to create one query which
contains student?s totals by week. For example: IF WEEK=10 then SUM
ATTENDANCE from each of the four queries. This would populate the
WEEK TEN column. The next column would be, IF WEEK=11 then SUM
ATTENDANCE from each of the four queries. This would continue
on?.Below is an example of what I?m looking for.

QryWEEKTotal
ID# LASTNAME WEEK_10 WEEK_11 WEEK_12??.
34 Smith 100
42 Johnson 140
51 Badley 110

In the actual query students have scores for each week so it wouldn?t
look so sparse but this is basically it. I have gotten some help with
the use of an IF then SUM expression but have not been able to get it
to work and hoped that a clearer picture of what I was doing would
help.

Thank you in advance for your assistance.

Tom

It looks to me as if you want to do a crosstab.
The first hurdle is that you need to union these queries, not join them. And
actually, it might be possible to do this even more easily if you explain
what each of these queries is doing.

Are they querying separate tables? If so, it appears you are storing data in
multiple tables when you should be storing it in a single table (you will
sometimes hear this mistaken design referred to as "committing spreadsheet"
as in separating the data into multiple tabs might make sense in a
spreadsheet, but not in a sql database). If this is the case, you should
consider normalizing your database so that all the student attendance data
is stored in a single table, using an extra field to identify which subset
of the data each row belongs to.

If that's not the reason for the 4 queries, ignore what I just said.

Anyways, the first step is to normalize the data from the queries. It is
possible to use derived tables (subqueries) to do this all in a single
query, but I'm going to suggest the use of intermediate saved queries to
make debugging easier and to make the final query easier to read.So, create
a saved query called qry_AttendanceUnion using this sql (you will not be
able to do this in Design View - the query needs to be switched to SQL
View):

select "A" As Source, [ID#], LASTNAME,[WEEK],ATTENDANCE
FROM Qry_TIMETable_A
UNION ALL
select "B", [ID#], LASTNAME,[WEEK],ATTENDANCE
FROM Qry_TIMETable_B
UNION ALL
select "C", [ID#], LASTNAME,[WEEK],ATTENDANCE
FROM Qry_TIMETable_C
UNION ALL
select "D", [ID#], LASTNAME,[WEEK],ATTENDANCE
FROM Qry_TIMETable_D

Again, you're better off starting with your data in normalized form - this
union is not only complex, but it will also need to be modified if you need
to add a new source of attendance data into the mix.

Now that the data is in a normalized form, it will be easier to deal with
using sql. At this point you have two alternatives:
1. Fire up the Crosstab Query wizard (click the New Query button and select
the Crosstab option) and point it at qry_AttendanceUnion. Go through the
steps, using ID and LASTNAME for the row headingss, WEEK for the column
headings, and ATTENDANCE for the Summary value, selecting Sum as the
aggregation function. Here's an illustrated tutorial on the crosstab wizard:
http://www.teacherclick.com/access2003/t_9_2.htm
If you prefer watching a video as opposed to reading text, here's a video
geared to A2010 (for future questions, please specify the version of Access
you are using - it's almost always relevant):

Notice in the tutorial, that there is a builtin option to pivot date/time
data by weeks, similar to the functionality I told you exists in reports.

2. After seeing how easy the crosstab query wizard makes this, I doubt
you'll even read this but, just in case you do want to write it yourself,
there are a couple options:
a. Use the TRANSFORM and PIVOT keywords:
TRANSFORM Sum(ATTENDANCE)
SELECT [ID#], LASTNAME FROM qry_AttendanceUnion
GROUP BY [ID#], LASTNAME
ORDER BY [ID#], LASTNAME
PIVOT WEEK

Incidently, that's very similar to the sql that will be generated by the
Crosstab query wizard.

b. Use Iif() to do the pivot. The drawback to this method is that the weeks
need to be hard-coded into the sql and therefore the query will need to be
rewritten whenever you want to add new weeks.
SELECT [ID#], LASTNAME
,Sum(Iif(WEEK=10,ATTENDANCE,0)) As Week10
,Sum(Iif(WEEK=11,ATTENDANCE,0)) As Week11
,Sum(Iif(WEEK=12,ATTENDANCE,0)) As Week12
FROM qry_AttendanceUnion
GROUP BY [ID#], LASTNAME
ORDER BY [ID#], LASTNAME

 

[Tom Cunningham]

Thanks for your help! Regarding why these queries seem to be doing the same thing.
We measure student behavior 4 times per day in a 24 hour period (our students stay with us 24/7 for roughly 30ish days)on the same 5 categories for each time slot (Attendance, hygiene, chores, respect & leadership). Time slots are as follows: A=12am-11am, B=11am-3pm, C=3pm-7pm & D=7pm-12am. When I built the database I created four TABLES each one representing one of the four time periods just listed. These four table are connected the the student information table with a one to many relationship. The each one of the four queries is gathering "active" student scores of each of the four time periods.
Currently, I am working on bring together student information by week (which you are helping me with) which will give us a good picture of whether or not our students have made improvements from week one to week two and so forth. My hope is that I have also set this database up in such a way that will give us the greatest flexibility for the future (which time period do students score the best, the worst, etc..).
I hope this clears up and I really appericate your assistance.

Tom

 

[Tom Cunningham]

Thanks for your help! Regarding why these queries seem to be doing the same thing.

We measure student behavior 4 times per day in a 24 hour period (our students stay with us 24/7 for roughly 30ish days)on the same 5 categories for each time slot (Attendance, hygiene, chores, respect & leadership). Time slots are as follows: A=12am-11am, B=11am-3pm, C=3pm-7pm & D=7pm-12am. When I built the database I created four TABLES each one representing one of the four time periods just listed. These four table are connected the the student information table with a one to many relationship. The each one of the four queries is gathering "active" student scores of each of the four time periods.
Currently, I am working on bring together student information by week (which you are helping me with) which will give us a good picture of whether or not our students have made improvements from week one to week two and so forth. My hope is that I have also set this database up in such a way that will give us the greatest flexibility for the future (which time period do students score the best, the worst, etc..).
I hope this clears up and I really appericate your assistance.
Tom

 

[Tom Cunningham]

Hello,
I just wanted to thank you for all your help. Your suggestions to union all the queries and then create a cross tab query worked perfectly, very simply.
Thanks again!
Tom

 

[Bob Barrows]

Thanks for your help! Regarding why these queries seem to be doing
the same thing.

We measure student behavior 4 times per day in a 24 hour period (our
students stay with us 24/7 for roughly 30ish days)on the same 5
categories for each time slot (Attendance, hygiene, chores, respect &
leadership). Time slots are as follows: A=12am-11am, B=11am-3pm,
C=3pm-7pm & D=7pm-12am. When I built the database I created four
TABLES each one representing one of the four time periods just
listed.

Again, you would have saved yourself (and the database query engine) a bunch
of work by creating a single table with a column to identify the time slot,
allowing you to create four records in that table for each student each
week, rather than having to maintain and union the data from four tables.
Think about the work you will have to go through if you want to add another
column in the future, or worse, if you want to start tracking 5 periods each
day instead of 4 ... Again, that design (four tables/tabs) might make sense
in a spreadsheet, but is usually not appropriate in a sql database.

Just think how easier this particular task would have been for you if you
had been able to simply point the crosstab query wizard at a single table
....

The only reason to use four tables is if different metrics are being saved
depending on which time slot is being entered. But even then, a case could
be made to continue using a single table, entering nulls into the columns
that are not relevant in a particular time slot.