creating an expression on multiple date fields in a query

[drnld]

Hi - I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.



These are the fields I'm using in the query:

Student Name
Start Date
Midpoint Date
Hours

This is what I tried using and am not sure what I'm doing wrong. It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]

Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))


Can someone help me with the correct way to write this expression?

Much appreciated.

[ranman256]

you have Hrs between date range
you want a DATE between date range

[drnld]

I'm not sure I understand.

so it should be:

Expr1: Sum([Start Date],[Hours]) between [Start Date] & [Midpoint]))

????

[rpeare]

What is your table structure? Do you have an attendance type table where you record the date the student was present and the amount of time the student was there?

Do you get the start date and midpoint date from another table

if so you would link the two tables by the student ID (don't use student name, each table should have it's own primary key and names are HORRIBLE link)

then your formula would be something like:

Student Hours: iif([ActivityDate] between [StartDate] and [MidpointDate], [TimePresent], 0)

[drnld]

I have a Student Attendance Table that every day is entered a specific # of hours they attended.

Student Name (is a look up field in Student Table)
Attendance Date
Hours
Required Hours (defaulted to the total hours required for each day)


I have a Cycle table that has these fields:
Cycle (text field) Examples: 14-Cycle 1, 14-Cycle 2 etc.
Start Date (specific to each cycle)
Midpoint Date
Grad Date

I've pulled these tables into one query linking Student Table ID to Attendance and Cycle

[rpeare]

I don't understand your cycle table

Are you saying you have something like

[code]
Cycle1 Cycle1Start Cycle1End Cycle2 Cycle2Start Cycle2End ... etc (for each cycle)
[/code[

or is it a more normalized structure like

CycleID  CycleName  CycleStart  CycleEnd
1        Cycle 1    1/1/2014    1/14/2014
2        Cycle 2    1/15/2014   1/29/2014

then your 'junction' table would be your attendance table which had the student ID, the attendance date and the time present, are you also storing the cycle ID in your junction table?

[drnld]

yes exactly!

[drnld]

I tried using your suggestion with my fields and typed it like this:

Student Hours: IIf([Student Attendance].[Hours] Between [Start Date] And [Midpoint],Sum([Student Attendance].[Hours]))

It almost works - but it returns a value of 4 for hours for everyone. They all have 4 hours required per day (it's a night class).

What I want to be able to see is:

Student Name Cycle Start Date Midpoint Date Hours Attended Hours Required
John Smith 14-cycle 1 5/1/14 7/1/14 230 345
Joe Brown 15-cycle 1 6/1/14 8/1/14 125 125

[drnld]

I also tried this way and I do get a total, but it's not accurate. There are 4 students in the oldest cycle that should have more hours than any other and those results are displaying 4 hours where the others are in the 100s.

Student Hours: Sum(IIf([Student Attendance].[Attendance Date] Between [Start Date] And [Midpoint],[Hours],0))

[drnld]

I'm not sure that you got this reply. When I said "exactly" this is what the cycle table is set up as.

CycleID CycleName CycleStart CycleEnd
1 Cycle 1 1/1/2014 1/14/2014
2 Cycle 2 1/15/2014 1/29/2014

[rpeare]

can you post a sample database, just create a copy put in some junk data that mimics your current stuff, then compact/repair, zip it up and upload it here.

[drnld]

Thank you for taking the time to look at this. I'm stumped and I know it's possible to do. I have uploaded a Test Database for you to use with data in it. Let me know if you get it and can open it.

Thanks,
Dottie

[rpeare]

I have one queston... are your students ever in more than one cycle? if they are you don't really have the table structure to support that. You are storing the cycle of each student on your students table which means they will only ever have *1* cycle at a time (in other words you won't be able to report history of data for any student)

As long as that's your intent this query should give you what you want:

SELECT Students.[Last Name], Students.[First Name], [Cycle Table].Cycle, [Cycle Table].[Start Date], [Cycle Table].Midpoint, Sum(IIf([attendance date] Between [start date] And [midpoint],[hours],0)) AS HoursatMidpoint, Sum([Student Attendance].[hours]) AS TotalHours
FROM [Cycle Table] RIGHT JOIN (Students LEFT JOIN [Student Attendance] ON Students.ID = [Student Attendance].Student) ON [Cycle Table].ID = Students.Cycle
GROUP BY Students.[Last Name], Students.[First Name], [Cycle Table].Cycle, [Cycle Table].[Start Date], [Cycle Table].Midpoint;

[drnld]

No, students are never in more than 1 cycle.

[drnld]

At the risk of sounding really stupid... where do I paste this code? I've tried pasting it in a query using the build icon and it gives me a syntax error. I tried breaking it down in a query and it doesn't work. I know it's an error on my end, but unfortunately I guess I really don't understand. Sorry. Do you mind explaining further?