Please I need Urgent Assistant

[michlean]

Please I just added a screenshot of my Table with the following fields> S/N Beneficiary name, MassRequest, Startday, EndDay, Day, Mass Time.

The mass request has a dropdown where one can select with 4 options, then one can selet a start date from calendar pick a date eg 9th february and EndDate could be 30th March.

one can select either sunday or monday etc from day field with dropdown of all the availabke options.

my proble is this...i want to print a report that can filter a beneficiary with each of the mass intentios, then i can filter any date of my choice ranges from start date and end date, it could be 2nd of march, or 2oth of february. then i can aswel filter mass time time which user also selected from drop down in the form.

my greatest challance is this, if i try normal filter in all the fileds, i can only see the single date of start date and end date, the othe date ranges arent captured.

please what do i do.

 

[Pat Hartman]

Several solutions depending on how you want to handle history. If you want to always be able to see every mass ever requested, then create the individual mass records when you enter the original range. So, if a person requests daily masses for two weeks, create 14 records. One for each day. If they request Sundays for a month, enter 4 or 5 records. I'll attach a link to a db with date functions that will help you to find the dates of weekdays.

If you don't need to see the individual mass dates but only the request, then you probably don't want to create individual records. But you always need to see a schedule of upcoming masses. I would create a function that reads the request records that include the range you want a schedule for and create a temp table with the individual mass dates only in the range you are looking at.

Because temp tables always cause bloat, you can use our "side" end idea and create the temp table in a temp db so it can be recreated each time you need a schedule.

Let us know which of the two I suggested most closely matches your needs and we'll help with the coding if you need it.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

The first solution is slightly easier but the code to generate the individual masses is the same for both versions. Just depends on when you will run it.

 

[michlean]

Thank you Very Much for response and help. like i said, i only started reading through Access because of this assignment, im not yet deep into it to understand th flow. but i dont think i understand your two solutions suggested.

Moreover, what u meant by creating 14 records, this supposed to be automated.

let me explain well what and how i wanted it to work.


from the db i attached, a
person requested for a mass of open thanksgiving from 2/9/2025 thats 9th of Feb 2025 to 12/14/2025 thats 14th of Dec. 2025.

I want if i run a report at each day, on open thanksgiving, the beneficiary name Catholic Youth Organization of Nigeria (CYON) will be inclusive, either Mon, tues, etc.

another eg. a beneficiary (Board of Church Wardens) booked a mass from 2/2/2025 to 12/7/2025 thats from 2nd Feb. till 7th Dec. 2025, from the form, they selected their mass to be called on only on 1 sundays, thats 1st Sunday of every month, so i want if a report is run for printing, at any first sunday, their intention will be printed. thanks. i don't know if u understand.

 

[June7]

Automating either approach will require VBA. In either option presented, code will "batch" create a set of records, whether retained permanently or not. This is actually a fairly common topic but you are a long way from being able to accomplish.

Otherwise, manual data entry of individual records, one record at a time.

 

[Pat Hartman]

Moreover, what u meant by creating 14 records, this supposed to be automated.

When I said "create 14 records", I wasn't saying to do it manually. I was describing the end result. Of course your automation code would create the 14 records permanent if that is what you decide you need. Or, your code will create 14 temporary records if you are creating the records simply to print/display a schedule for the upcoming week. The point is "your code". There is nothing magic in Access that will create the records for you although there are techniques using what we call a "tally" table that can simplify the code so you only need an append query rather than an actual VBA loop. Your requirements are a bit complicated for the "tally" table solution because you would need to select the correct records from the tally table that correspond with your specific request.

Tally table sample

There is a method of generating x records using what is frequently called a "tally table". This table has rows with values from 1 to x with x being the maximum number of items you will need to generate. This particular example is from a receiving/shipping application I built for a client. The...

www.access-programmers.co.uk

Sounds like the simplest solution is to create the records permanently when the request is entered. That makes the retrieval of the information trivial. Your query would request the masses for a data range. Either 1 day range or a multiple day range. Irrelevant if you write the query to be flexible. You can always add a purge function to the database later if you decide you don't need to keep the daily details for more than a few years. I would never purge the requests though. I would leave them in the database permanently.

The tally table sample is very simple since it works for "cartons". I built it for a client whose warehouse received x cartons of goods but the client wanted to store a separate record for each individual carton rather than 5 Cartons of Large White Towels so that he could ensure that the inventory was FIFO and so it was sold as first in, first out so the inventory didn't get stale. Your problem is more complex so you will need code. The simple case of x masses would be created using the method of the sample. The every "Sunday" request is also similar but instead of selecting 10 records from the tally table going forward, the query needs to select the next 10 "Sundays" and that will require a function that identifies "Sunday" dates. The first "Sunday" has another type of twist. So that is three variations. This variation requires a function that retrieves the "first Sunday" of a month only. If a parishioner requests every "Sunday" and every "Wednesday", I would create two request records just to simplify everything. Then each would create a set of records.

Are there other variations we need to know about before someone attempts to write the code? Another more complex version would be masses requested on a type of holy day. My knowledge of what holy days might be involved isn't sufficient to guess. Obviously specific dates are never a problem. So, Ash Wednesday is a specific date for this year but that date changes year to year so taking that kind of request will require the maintenance of a holy day calendar.

 

[Pat Hartman]

@michlean
I do occasional work for charities and churches would qualify. I had some time this weekend so I added a couple of tables and forms to help with this app. If you would like my help on a formal basis, I will need to hear from your minister and we can set up some meetings to discuss in more detail what needs to happen.

I started by creating a tally table that uses dates rather than a sequence number since that will be how masses will be generated once the request is entered. I also assumed that at some point you will create a holiday table. The one in the sample is for US holidays. The app includes a form that uses code to generate next year's holiday records based on two standard rules. If the holiday doesn't conform to the standard two rules - specific date or specific weekday of the month like first Monday, third Thursday, etc. then a custom function must be added. I don't have to add this feature but there are a lot of church holidays during the year and the person managing the calendar will appreciate the help to generate the correct dates for each new year.

So, I know you won't understand the code but some of it might make sense. When the menu opens, open the "Add rows to Tally Dates table". This is a standard function that will be used to fill the tally table with next year's dates. You put a number in "qty to add" and press the button. The From and To dates will populate with the date range the new dates were generated for. The code finds the maximum date that currently exists in the table and then adds x date records after that. So, if the max date is 2/23/25 and you request 5 records, 2/24/25 - 2/28/25 will be added. Once we get into this, I'll add a warning to the add mass form to tell you that there aren't enough dates in the tally table.

The next new form to look at is "calculate holidays"/"Holiday Rules Documentation". That explains how the current rules work. If you want this feature, someone has to tell me how to calculate EVERY holiday date you want to automatically calculate using the two standard rules and define new rules if possible for the other dates. If we can't figure out a new rule, then the holiday dates can always be added manually so we could even do this at the end or never.

Once I've confirmed this is a valid charity, and we've had some discussions, I can modify what you have to add the new mass table and the reports you need.

PM me with contact details and a couple of times we could meet. I am EDT (GMT -5) and evenings after 6 pm generally work. Otherwise, Mon, Tue, and Thu are best. Wed is probably a no and so is Fri because I play a couple of sessions of bridge on those days. I'm usually here at least once a day but sometimes skip so don't get upset if you don't hear from me for a day or two.

Meanwhile, I'll work on your MassIntention table and form and reports.

If you don't actually make masses for any but a small number of actual holidays, we can forget about the need for a table of all holidays.

The MassRequest table will be:
Beneficiary
TypeOfMass
StartDate
EndDate (or # of occurances)
WeekDay (for every Wednesday for 5 weeks for example)
MassTime

Unless your Mass times are always the same for every day, the time table needs to be qualified by WeekDay, Sunday, Saturday, Holiday