Ordinarily, if I were doing this in MySQL, I would write a single query to get through all this. However, it's proving to be prohibitively painful in MSAccess. I ended up taking the more complicated route, and am stuck:
Database tracks local community association. Base tables are:
house (number, address, area (10 total), block (3-5 per area)
family (to group "persons")
person (firstname, lastname, email, phone)
board_position (president, area captain, block captain, ...)
volunteer (activity sign-ups)
"join" tables:
house_family (family_id, house_id, moved_in_date, moved_out_date - which family lives in which house - allows for history if families move within neighborhood)
member_year (family_id, house_id, year - tracks year-to-year membership. membership optional, therefore needs to be tracked by each distinct year)
person_board (person_id, board_id, start_year, end_year)
person_volunteer (person_id, volunteer_id, year)
Due to the Access' "query parsing challenges", many of the reports I'm having to write have subreports and VBA code behind them...but I'm trying not to over-complicate them.
(note - I'm doing a HUGE rewrite of someone else's work. The existing is "one table, 60 columns". Suber-easy queries/reports, but zero normalization, and hardly extensible)
Current challenge: report of all the area and block captains. Note, some positions are vacant, some have one person, some have two-spouse co-captaincy.
Approach:
Query area and block from "house", group by both (results in single list of areas/blocks)
Report "detail" is "one particular block". Grouping on "area", so report ends up as: area 1, block 1.1, block 1.2, block 1.3...area 2, block 2.1, block 2.2....
To get block captains:
created query:
SELECT First(person.first_name) AS fName1, First(person.last_name) AS lName1, Last(person.first_name) AS fName2, Last(person.last_name) AS lName2, person_board.block, board_position.position
FROM (board_position INNER JOIN person_board ON board_position.id = person_board.board_id) INNER JOIN person ON person_board.person_id = person.id
GROUP BY person_board.block, board_position.position
HAVING (((board_position.position)="Block Captain"));
Within the "detail" section, added "subform/subreport", pointing to this query. "field linker" links "block" between the main query and this subquery.
The desired output in the detail section is the block, and a combined "name". This needs to take into account:
Open position ("Vacant")
One person ("Mickey Mouse")
Couple with same last name ("Mickey and Minnie Mouse")
Couple with different last name ("George Washington and Martha Custis")
To do the "name combination", I set up the following VBA code in a Module (not tied to the report itself):
Public Function MergeNames(fName1 As Variant, lName1 As Variant, fName2 As Variant, lName2 As Variant) As String
MergeNames = "Vacant"
If lName1 = lName2 Then
If fName1 = fName2 Then
MergeNames = fName1 & " " & lName1
Else
MergeNames = fName1 & " and " & fName2 & " " & lName1
End If
Else
MergeNames = fName1 & " " & lName1 & " and " & fName2 & " " & lName2
End If
End Function
To send the subquery results to this function, the textbox which should hold the combined name has, as record source:
=MergeNames([Q BC subreport].[Report]![fName1],[Q BC subreport].[Report]![lName1],[Q BC subreport].[Report]![fName2],[Q BC subreport].[Report]![lName2])
...and the "subform/subreport" itself is set to invisible.
This is all working fine, except for the "Vacant" case. Inserted debug messages suggest this code isn't even being called if the subquery yields no results. I ended up resorting to changing the textbox record source to:
=IIf(IsError([Q BC subreport].[Report]![fName1]),"Vacant",MergeNames([Q BC subreport].[Report]![fName1],[Q BC subreport].[Report]![lName1],[Q BC subreport].[Report]![fName2],[Q BC subreport].[Report]![lName2]))
I'm not crazy about the conditional, but haven't found any other way to pull this off. Any suggestions on how to fix this, or am I stuck with my ultimate solution?
Side-note, hate to gripe on Access, but left-join conditions are driving me crazy, when trying to enforce conditionals. Such as, easy enough to left-join house to house_family when there are no relationships. But, if a family moves out and the house is vacant, trying to introduce the restriction of "moved_out_date is null" has to be done manually in SQL. From there, any further changes may break the query. One such query, I got to work, and saved it. I added a clause, ran it - crash. Manually remove the clause so it's EXACTLY as it was before - same crash. It's reasons like this I had to resort to all this extra VBA code and hidden subqueries. Am somewhat tempted to take an ETL approach, running queries/code to populate a "reporting" table, then writing the report based on that table. Is that the preferable approach in Access, or should I continue on current course?
Thanks... table layout pic attached - table names and fields are consistent with the actual DB.
Windows 10 
Access 2016
Reply With Quote
