Solved

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

Posted on 2014-04-22
20
314 Views
Last Modified: 2014-04-30
My existing report lists Type of work performed with a category code:
    301 General Arrangements (category code)
    Project:
    Install Cableway
    314 Power Conversion
    Project:
    Motor Recovery

Problem – how can I create a report to display back Categories:
300 Electrical
     301 General Arrangements
     Project:
     Install Cableway
     Load Analysis
310 Electrical Power Generation
    314 Power Conversion
    Project:
    Motor Recovery


The formula I’m looking for:
If record(s) exist display heading(s) with appropriate number in 10’s
(If a record is in 551 display 550 & 500)
(If a record is in 785 display 780 & 700)
(If a record is in 200 display only 200)
(If a record is in 201 display  200)

If a record (project) exists in:
314 Power Conversion
(Project records)

Automatically display:
Add - 300 Electrical
Add - 310 Electrical Power Generation
     314 Power Conversion
            (Project records)

Additional Examples:
If there is a record (project) with a 301 listing – display 300 above the record
If there is a record (project) with a 314 listing display 310 & 300 listing above the record
If I have a project in a 624 category display 620 & 600
(Headings display only one time, not for every record)
0
Comment
Question by:DJPr0
  • 6
  • 5
  • 3
  • +2
20 Comments
 
LVL 84
ID: 40015407
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?
0
 

Author Comment

by:DJPr0
ID: 40015642
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
0
 
LVL 84
ID: 40015911
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?
0
 

Author Comment

by:DJPr0
ID: 40019837
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40025681
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?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40025708
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

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 40027151
If you are using Access. then upload a sample database with a table and an image of the required report output.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40027618
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?
0
 

Author Comment

by:DJPr0
ID: 40027749
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 45

Expert Comment

by:aikimark
ID: 40027756
@DJPr0

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

Expert Comment

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

Author Comment

by:DJPr0
ID: 40027859
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40027913
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

Open 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

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40027922
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));

Open 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;

Open in new window


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

Author Comment

by:DJPr0
ID: 40029881
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40030013
The dupes make sense from how I coded it and grouped it.
Major truncated to just the first digit
Minor truncated to just the two digits -- but if that second digit is a zero, then there should be no minor.
I'm not sure that'll happen anyway, but the minor calculation could be
minor: iif((Left([si_index],2) mod 10)=0,null, Left([si_index],2) & "0")
Your other requirements require some alteration to the sorting and grouping of the report.
If you don't want repetition then MajorHeading and MinorHeading need their own header section.

Have a look
Sample.mdb
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40030257
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_)<>"_"));

Open in new window

0
 

Author Closing Comment

by:DJPr0
ID: 40032039
Thanks to all who contributed!

Special thanks to Nick67!
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now