MS Access How can I automatically list (back) categories in my Access report?

Do you have tables that support these relationships? That is, do you have some sort of Chart of Accounts that lists the Accounts, with all upstream accounts?

Do you have tables that support these relationships?

Yes

Tables                                          
WorkCodes      (Over 1000 records)                       Projects  (over 30K records)
Code ranges from 000 to 999                                             (Each project is connected          
300      ELECTRIC PLANT, GENERAL                                          to one work code)      
301      GENERAL ARRANGEMENT-ELECTRICAL DRAWINGS
302      MOTORS AND ASSOCIATED EQUIPMENT
303      PROTECTIVE DEVICES
304      ELECTRIC CABLES
305      ELECTRICAL DESIGNATING AND MARKING
310      ELECTRIC POWER GENERATION
311      SHIP SERVICE POWER GENERATION
312      EMERGENCY GENERATORS
3121   GENERATOR SETS, EMERGENCY DIESEL
313       BATTERIES AND SERVICE FACILITIES
314       POWER CONVERSION EQUIPMENT
3141  MOTOR GENERATORS, 60 HZ
3142  MOTOR GENERATORS, 400 HZ
3143  POWER CONVERSION, SPECIAL PURPOSE COMPONENTS
3144  POWER SUPPLIES, DC
320       POWER DISTRIBUTION SYSTEMS

 
Workcodes has a relationship with Projects - each project is connected to one work code.

Is there a formula we can provide in the report to:
If a record is in 551 display 550 & 500

But you have no way of knowing that 314 belongs "upstream" to 300 (or others)? Other than by (apparently) a "format" association?

So any number is related to it's "10" parent - i.e. 314 is related to 310, and 539 is related to 530.

However, the "10" parents are not related to other "10" parents, but instead related only to their "100" parent: so 310 is related to 300, and 560 is related to 500?

But you have no way of knowing that 314 belongs "upstream" to 300 (or others)?

No

Other than by (apparently) a "format" association?

Yes

So any number is related to it's "10" parent - i.e. 314 is related to 310, and 539 is related to 530.

Yes

However, the "10" parents are not related to other "10" parents, but instead related only to their "100" parent: so 310 is related to 300, and 560 is related to 500?

Yes

322 is related to 320 & 300
986 is related to 980 & 900
910 is related to 900
220 is related to 200

You have at least three possible solutions
1. In your report's print or format event, include VBA code that will populate text boxes or labels with the hierarchical data.
2. In your query, construct columns (fields) with the calculated hierarchical data
3. Create and populate a table to represent the hierarchical data.  You may also add and populate a parent column in each record.

===========
What should be shown as the hierarchical data for 3143?

Is your Workcodes column numeric or text?

What rules determine parentage?

I copied your table snippet into a worksheet to play with the hierarchical parentage determination.  This algorithm iterates the powers of 10, starting at 1, until we get to one less than the length of the parmItem.  In each iteration, we first set the right most digit to zero and check if it is a Workcode.  If it is, then we add it to our parentage string output.  If not, we decrease the length of the parent by removing the units digit.

In this example, I used a dictionary object to store the Workcode values.  In your environment, you would instantiate the rs Recordset variable, based on the table.  You would use the rs object's Find method to do the lookup.  This could be done with long integer values.  This example is provided for you to understand the algorithm as I currently understand it from your comments.  It has not been optimized for efficiency nor implemented in an Access run-time environment.

Option Explicit

Public Function GetParentage(parmItem)
    Dim rs As Variant   'Recordset
    Dim rng As Range
    Dim dicWorkCodes As Object
    Dim lngParm As Long
    Dim lngPwr As Long
    Dim lngLoop As Long
    Dim vItem As Variant
    Set dicWorkCodes = CreateObject("scripting.dictionary")
    Set rng = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A1").End(xlDown))
    rs = rng.Value
    For Each vItem In rng.Value
        lngLoop = lngLoop + 1
        dicWorkCodes.Add CStr(vItem), rng.Cells(lngLoop, 2)
    Next
    lngPwr = 1
    lngParm = parmItem
    Do
        lngParm = (10 ^ lngPwr) * (lngParm \ (10 ^ lngPwr))
        'rs.Find CStr(lngParm)
        'If rs.NoMatch Then
        'Else
        If dicWorkCodes.exists(CStr(lngParm)) Then
            GetParentage = GetParentage & vbCrLf & CStr(lngParm) & " " & dicWorkCodes(CStr(lngParm))
            lngPwr = lngPwr + 1
        Else
            lngParm = lngParm \ 10
        End If
        
    Loop While lngPwr < (Len(CStr(parmItem)) - 1)
    GetParentage = Mid(GetParentage, 3)
End Function

Select allOpen in new window

If you are using Access. then upload a sample database with a table and an image of the required report output.

I'd have done it with strings.
It's a report, so in the detail section, have a control bound to the code field
Dim CodeString as String
Dim LookupValue1 as long
Dim LookupValue2 as long

CodeString = cstr(me.code.value)
LookupValue1 = Left(CodeString,1) 'left digit
LookupValue2 = Left(CodeString,2) 'left 2 digits

me.SomeUnboundControlOnTheReportCalledMajorCategoryDescription = Dlookup("CodeDescription","tblWorkCodes",CodeID = Clng(LookupValue1 &"00"))

me.SomeUnboundControlOnTheReportCalledMinorCategoryDescription = Dlookup("CodeDescription","tblWorkCodes",CodeID = Clng(LookupValue2 &"0"))

Dlookup isn't the most efficient thing in the world.
In the Report_Open, you could populate an array
But that'd be fairly complex, and similar to what @aikimark proposes to do with a dictionary object    

Thinking about it more, I'd alter the table.  I'd add a field for MajorCategory and a field for MinorCategory.  In an updateQuery, I'd update MajorCategory to Clng(Left(cstr(workcode),1) & "00") and update MinorCategory to Clng(Left(cstr(workcode),2) & "0)".

Then, in the Report's RecodSource, I'd add two more instance of tblWorkCodes, and bind them to MajorCategory & MinorCategory.  Problem solved and efficient.

Why doesn't the data entry form for the project have similar things on the go already?  Surely they don't scroll a combobox of a thousand items?

Please take a look at my sample database and open the report. The added headings in red  is what I am trying to display automatically.

These tables are being pulled from a SQL server which I have limited control over in terms of making changes to the table structure. The inputting is done by another application and this Access product is for reports only.
sample15---Copy.accdb

@DJPr0

I posted some questions in this prior comment that you should answer:
http:#a40025681

See if this can help.
table: id_descr
query: id_descr_q
report: id_descr_q
a function u123 in module1
sample15-2.accdb

Sorry aikimark.

I posted some questions in this prior comment that you should answer:
http:#a40025681

What should be shown as the hierarchical data for 3143?
300
310
314
3143

Is your Workcodes column numeric or text?
Text

What rules determine parentage?
Back by 10/100’s

I tweaked my earlier code to match the parentage you have posted.  It now concatenates the parentage in the correct order
Immediate window test:

?GetParentage(3143)
300 ELECTRIC PLANT, GENERAL
310 ELECTRIC POWER GENERATION
314 POWER CONVERSION EQUIPMENT

Select allOpen in new window

The revised code:

Option Explicit



Public Function GetParentage(parmItem As Long)
    Dim rs As Variant   'Recordset
    Dim rng As Range
    Dim dicWorkCodes As Object
    Dim lngParm As Long
    Dim lngPwr As Long
    Dim lngLoop As Long
    Dim vItem As Variant
    Set dicWorkCodes = CreateObject("scripting.dictionary")
    Set rng = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A1").End(xlDown))
    rs = rng.Value
    For Each vItem In rng.Value
        lngLoop = lngLoop + 1
        dicWorkCodes.Add CStr(vItem), rng.Cells(lngLoop, 2)
    Next
    lngPwr = 1
    lngParm = CLng(parmItem)
    Do
        lngParm = (10 ^ lngPwr) * (lngParm \ (10 ^ lngPwr))
        If dicWorkCodes.exists(CStr(lngParm)) Then
            GetParentage = CStr(lngParm) & " " & dicWorkCodes(CStr(lngParm)) & vbCrLf & GetParentage
            lngPwr = lngPwr + 1
        Else
            lngParm = lngParm \ 10
            If dicWorkCodes.exists(CStr(lngParm)) Then
                GetParentage = CStr(lngParm) & " " & dicWorkCodes(CStr(lngParm)) & vbCrLf & GetParentage
            Else
                lngParm = lngParm \ 10
            End If
        End If
        
    Loop While lngPwr < (Len(CStr(parmItem)) - 1)
    GetParentage = Left(GetParentage, Len(GetParentage) - 2)
End Function

Select allOpen in new window

Yuuuuuup.
Since you can't update the table, build a query with Major & Minor as calculated fields

SELECT dbo_dd_wbs.grpheading, 
dbo_dd_dwgh.dh_dwgnum, 
dbo_dd_dwgh.dh_rev, 
dbo_dd_dwgh.dh_ltitle, 
dbo_dd_dwgh.dh_san, 
dbo_dd_didata.si_remarks, 
dbo_dd_didata.si_index, 
dbo_dd_didata.si_shipid, 
Left([si_index],1) & "00" AS Major, 
Left([si_index],2) & "0" AS minor
FROM dbo_dd_dwgh INNER JOIN (dbo_dd_wbs INNER JOIN dbo_dd_didata ON dbo_dd_wbs.index_ = dbo_dd_didata.si_index) ON dbo_dd_dwgh.dh_dcn = dbo_dd_didata.si_dcn
WHERE (((dbo_dd_didata.si_shipid)=730));

Select allOpen in new window

As the initial query.  Bind the report to a second query with the two joins to dbo_dd_wbs

SELECT qrydbo_dd_wbsInitial.grpheading, 
qrydbo_dd_wbsInitial.dh_dwgnum, 
qrydbo_dd_wbsInitial.dh_rev, 
qrydbo_dd_wbsInitial.dh_ltitle, 
qrydbo_dd_wbsInitial.dh_san, 
qrydbo_dd_wbsInitial.si_remarks, 
qrydbo_dd_wbsInitial.si_index, 
qrydbo_dd_wbsInitial.si_shipid, 
qrydbo_dd_wbsInitial.Major, 
qrydbo_dd_wbsInitial.minor, 
dbo_dd_wbs.grpheading AS MajorHeading, 
dbo_dd_wbs_1.grpheading AS MinorHeading
FROM dbo_dd_wbs AS dbo_dd_wbs_1 RIGHT JOIN 
(dbo_dd_wbs RIGHT JOIN 
qrydbo_dd_wbsInitial ON 
dbo_dd_wbs.index_ = qrydbo_dd_wbsInitial.Major) 
ON dbo_dd_wbs_1.index_ = qrydbo_dd_wbsInitial.minor;

Select allOpen in new window

Change the labels to textboxes and bind 'em and you're in business
sample15---Copy.mdb

Nick67 - works good except for the dupes - see dbo-dd-wbs.pdf for a marked up report


Please modify to display headings without dupes:  (All headings should be displayed only once)
 400 COMMAND AND SURVEILLANCE, GENERAL
     430 INTERIOR COMMUNICATIONS
           437 INDICATING, ORDER, AND METERING SYSTEMS
                   225544 - REPLACE SEWAGE TANK
           4373 WIND INDICATING SYSTEMS   (this belongs to 430)
                   888888 B New Drawing 1
     440 EXTERIOR COMMUNICATIONS       (this belongs to 400)
                   999999 - New Drawing 2
dbo-dd-wbs.pdf
sample15---Copy---Copy.mdb

For the data you posted in the dbo_dd_wbs, this query seems to retrieve the parentage.  You could join this query to the existing report source and then concatenate the parentage, or you retrieve the data from this query as needed.

SELECT I.index_, 
(SELECT P.index_
FROM dbo_dd_wbs As P 
WHERE (P.index_ = Left(I.[index_],Len(I.[index_])-1) 
Or P.index_ = (Left(I.[index_],Len(I.[index_])-1) & "0"))
And
P.index_ <> I.[index_]
) AS P1,

(SELECT PP.index_
FROM dbo_dd_wbs As PP 
WHERE (PP.index_ = Left(I.index_,Len(I.index_)-2) & "0"
Or PP.index_ = (Left(I.index_,Len(I.index_)-2) & "00"))
And
PP.index_ <> Left(I.[index_],Len(I.[index_])-1)
) AS P2,

(SELECT PPP.index_
FROM dbo_dd_wbs As PPP 
WHERE (PPP.index_ = Left(I.index_,Len(I.index_)-3) & "0"
Or PPP.index_ = (Left(I.index_,Len(I.index_)-3) & "00"))
And
PPP.index_ <> Left(I.index_,Len(I.index_)-3)
) AS P3

FROM dbo_dd_wbs AS I
WHERE (((I.index_)<>"_"));

Select allOpen in new window

Thanks to all who contributed!

Special thanks to Nick67!