Creating a Variable Length Multiple Column Report with Decision Logic

[jarena]

I have been asked by my employer’s financial management (FM) department to create an Access DB application that will calculate monthly income derived from golf course memberships. Input will include a member ID, date joined, and the annual membership dues. Each membership lasts 12 months and members can join any time, so if the member joins in August, the membership will expire July 31 the following year. There are approximately 10 different membership types and add-ons to the membership, so the annual fees will vary from member to member.

FM wants a detail report that will list monthly membership income (annual membership/12). FM wants the ability to choose the number of monthly income columns to display on the report by entering a start date and end date; this way they can view membership income for one month or several months. The report would resemble a spreadsheet with columns totals. (each income column would represent a subsequent month and be titled by month/year).

Sample Output:
In this example, the user wants to list 6 months of income from July-December, 2021



-------------------- Monthly Membership Report from July – December 2021 ----------------------

Mem ID	Ann Dues	Mem Exp Date	7/2021	8/2021	9/2021	10/2021	11/2021	12/2021
1	$1000	8-31-21	83.33	83.33
2	$800	9-30-21	66.67	66.67	66.67
3	$650	11-30-21	54.17	54.17	54.17	54.17	54.17
4	$1200	3-31-22	100.00	100.00	100.00	100.00	100.00	100.00
5	$800	6-30-21	66.67	66.67	66.67	66.67	66.67	66.67
		TOTALS:	370.84	370.84	287.51	220.84	220.84	166.67

Questions: 1) How would you include the monthly income in some columns but not all columns; would some type of decision logic be needed? 2) Is there a way to create this type of report in Access without using VB code?

All suggestions are appreciated.

[Micron]

To display what should be row based data in a column format in a report, I'd say you will need to base the report on a crosstab query. However, AFAIK the query fields have to be made static,otherwise errors will be raised when certain months don't have values (the report controls will be "looking" for them. Nor can you design such a report with 12 fields for months and only show say, the 1st, 3rd and 5th. Also, whatever decision logic that determines which columns are totaled would be for you to decide? In any event, I doubt you will ever come close to arriving at such a report without any code. Maybe if you stack 12 month controls in one spot in report design you can use code to
a) show only the controls for the months found in the query sql
b) make visible and position the ones you need and hide the rest
c) but only if you have a form where the user constructs the sql on the fly and you use that to "design" the report.

This sounds like Access might be the wrong tool for the job because it's designed for relational row based data, not column based like Excel. If you're new to the application, I think you will find this to be a bit of a challenge. Maybe look here for schemas for membership dues databases.

[jarena]

Thanks for the reply Micron. I agree that Access may not be the best solution for this project. I am an old programmer and DB guy that hasn't written code in 10+ years and I have never written VB code.

I thought about writing a query that would create 12 records for each member (one for each membership month) in a new file and then write a second a query (for the report) to sort the data by date and also only select records that satisfy the users date range criteria. I would need to do some SQL research to see if this can be done. I realize that this is not the ideal solution, especially from a DB purist's perspective!

[isladogs]

You can create a crosstab with fixed column headings so that each column will appear whether or not there is data.
In any case if you are grouping data there may be zero values to 'fill' each row/column. The zero values can be hidden using conditional formatting if required.

It is also possible to use dynamic column headings where the headings are determined 'on the fly'....but that definitely requires code.
However your idea about queries sounds more complicated to me, assuming I understood it correctly

[Micron]

I would try to avoid 12 queries if at all possible. Besides, getting the data isn't the issue here - it's trying to present it in a highly dynamic view. I'd be going back the the requestor and saying this isn't practical in a db and if they want a spreadsheet design, use Excel. If you point out all the reasons for not doing this, then you might get agreement to do a normal report. However, that might require you to have a good understanding of the problem. I might create a sample report as incentive to get agreement in that conversation.

If you had a look at a lot of report design images (google) you might see something that will turn out to be acceptable.

[jarena]

I have been out of the office and working on other projects for the last 2 weeks. I resumed working on this project yesterday and met with the FM manager; she stated that she only needs monthly income information for one month at a time, so a spreadsheet output format is not needed. She stated that the other user that was in the original meeting (her boss) was used to seeing data on spreadsheets and that is what prompted her to request multiple monthly columns, so the monthly column dilemma has been solved.

I do have a question about manipulating date fields, but since it is not related to this thread, I will post the question in a new thread. I would like to thank everyone for their input.