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.