Reports on multiple tables / queries

[drnld]

Hi - I'm trying to develop a way to create a similar looking report as the Excel sheet attached in this zip file using the Access database I created. I was asked to create this AFTER I had already set up the database and now I'm not sure if the structure is correct to do what I need.

I created 3 new tables "Holiday List Table", "Program Year Table" and "School Days Table" and have the look up the program year (PY) in the Program Year Table to link them all. I'm having trouble getting the information to display only the holidays within the particular cycle and only the school days per month for that cycle.

I manually entered the dates int he School Days Table for the day and evening classes since DAY classes meet 6 hours per day 5 days per week and EVE classes meet 4 hours per night for 3 days per week. It gets tricky because the evening classes usually meet Monday, Tuesday & Wednesday except when there is a holiday that week, then they make up the day on Thursday keeping their 3 days per week.

So you can see that this is rather complex to begin with and I'm trying to simplify the school's process using Access. I'm just not sure if I'm going about it the correct way or if there is a better way to do it. I'd appreciate any input. I'm not too familiar with VB code and wondering if that would be something I'd need to get help with to make this work.



Thoughts?
Dottie

[rpeare]

What exactly are you trying to do, just get a list of holidays within a given cycle?

i.e.
You have a cycle with an ID of 54 in your table, that cycle runs from 6/11/2014 through 1/6/2015.
In your Holiday List table you have these holidays between those two dates

ID	Holidays	HDate	ProgYr
36	Independence Day	Friday, July 04, 2014	15
37	Labor Day	Monday, September 01, 2014	15
38	Columbus Day	Monday, October 13, 2014	15
39	Veterans Day	Tuesday, November 11, 2014	15
40	Day before Thanksgiving (pm classes)	Wednesday, November 26, 2014	15
41	Thanksgiving Day	Thursday, November 27, 2014	15
42	Day after Thanksgiving (day classes)	Friday, November 28, 2014	15
43	Christmas Eve (pm classes)	Wednesday, December 24, 2014	15
44	Christmas Day	Thursday, December 25, 2014	15
45	Christmas Break	Friday, December 26, 2014	15
46	Christmas Break	Monday, December 29, 2014	15
47	Christmas Break	Tuesday, December 30, 2014	15
48	Christmas Break	Wednesday, December 31, 2014	15
49	New Year's Day	Thursday, January 01, 2015	15

If all you want to do is bring these dates onto your report (the way you have it designed you'll likely need 2 subreports on a main report to make it work) here's what you do.

On your form PROGRAM & CYCLES FORM, ceate a text box and name it CURRENTCYCLE
(just a word of advice, don't use spaces or special characters in your object names, it will cause you no end of grief in the long run use underscore (_) to indicate a space and don't use special characters at all)
IN the CONTROL SOURCE of CURRENTCYCLE put in =[Cycles Query subform]![id]
This will adopt the cycle ID from your subform.

Next, create this query

SELECT Cycles.ID AS CYCLEID, [Holiday List Table].id AS HOLIDAYID, [Holiday List Table].Holidays, [Holiday List Table].HDate, [Holiday List Table].ProgYr, IIf([hdate] Between [start date] And [end date],1,0) AS Show
FROM Cycles, [Holiday List Table]
WHERE (((Cycles.ID)=[forms]![Program & Cycles Form]![CurrentCycle]) AND ((IIf([hdate] Between [start date] And [end date],1,0))=1));

This is using a cartesian join but I am selecting a single period (the 'current' item in in the subform) and then using a formula (the one labeled SHOW) to determine which holidays fall in that specific cycle's date range.

[drnld]

Hi and thank you so much for your input and time.

A few questions:

You said to enter a text box on my Program & Cycles Form for Currentcycle. I'm unclear as to where I'm putting this. Is it first in the query that creates the form or directly on the form?

[drnld]

OMGosh! I figured it out!

[drnld]

OK - now that I have the query running, I still need to tweak this a bit more. The way it is set up now I have to know what cycle ID to enter in order to find the holidays in that range. It works, but that is not the extent of what I need to report on. I need to be able to enter the exact cycle and department for one particular cycle and have it report EVERYTHING about that cycle including:

Department, Cycle, start date, midpoint date, internship date, end date AND the list of holidays per that cycle.

Then to make matters more difficult, I need it to calculate the # of school days during each month from the beginning of that cycle to the end date separately. For example; July 20 days, August 21 days, etc.

I'm going to try using what you gave me to build what I need, but any help would be appreciated.

[rpeare]

https://www.accessforums.net/forms/f...tml#post234103

This is a thread with a function to count 'working days' minus holidays that should apply to part of your question.

For the others, do you have a more updated version of your database to the last one or is the one attached to this thread still current.