UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Items In Mvf Do Not Sort Correctly On Report, Access 2016    
 
   
momo2000
post Oct 23 2019, 11:54 AM
Post#1



Posts: 69
Joined: 6-November 13



I have two tables: [Adults] and [Merit Badges]. I have a report [rptByMBCounselor] that shows the details of each record in the [Adult] table as well as the merit badges they are qualified for. In the [Adult] table, the merit badges they are qualified for is a MVF [MBC_for] that looks up its source in the [Merit Badges] table (attached as meritbadge-pic)

I have a control on the form [rptByMBCounselor] (attached as report-design-pic) that is bound to the MVF field [MB_For] from the [Adults] table. The control uses this as the Source:
CODE
SELECT [Merit Badges].ID, [Merit Badges].[MB Name] FROM [Merit Badges] ORDER BY [Merit Badges].[MB Name];


HOWEVER, when the report (attached as report-output-pic) runs it does not sort by the [MB Name] , so I must be doing something wrong. How can I get it to sort by the [MB Name] ?

Attached File(s)
Attached File  meritbadge_pic.png ( 11.27K )Number of downloads: 0
Attached File  report_design_pic.png ( 47.62K )Number of downloads: 16
Attached File  report_output_pic.png ( 212.56K )Number of downloads: 11
 
Go to the top of the page
 
arnelgp
post Oct 23 2019, 01:01 PM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


whether you put an Order on the query, it won't work
and will follow the sequence (ID) on the MBC_For field.

you need to make the arrangement on table [Merit Badges].
putting then Name in proper order.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
momo2000
post Oct 23 2019, 03:34 PM
Post#3



Posts: 69
Joined: 6-November 13



That is really peculiar, since I do have an Order in the query, that is what was making me bang my head.

I'm concerned that if I rearrange my table [Merit Badges] and have both the [ID] and [MB Name] sorted, that will cause problems with the records that the [MBC_For] in the [Adults] table is pointing to, as I think they are linked to the [ID] and not the [MB Name].
Go to the top of the page
 
Doug Steele
post Oct 23 2019, 08:53 PM
Post#4


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You'd need to change the values for the foreign key in the Adults table as well. Or better yet, redo your database without using a MVF! (you may have noticed that the veterans here have an extremely low opinion of MVFs...)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
momo2000
post Oct 23 2019, 09:09 PM
Post#5



Posts: 69
Joined: 6-November 13



"You'd need to change the values for the foreign key in the Adults table as well". That would be a manual process, right? If so, that is a pretty tall climb. Yes, I built this 6 years ago to help, the scouts, first really Access project, and it is clear that I should have never done MVFs. But switching now is another tall climb. So I'm in that great place wink.gif
Go to the top of the page
 
Doug Steele
post Oct 23 2019, 09:19 PM
Post#6


UtterAccess VIP
Posts: 22,228
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Build a temporary table that contains the old ID number and the new ID number, then write an Update query to change the old values to the new values.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
momo2000
post Oct 23 2019, 09:47 PM
Post#7



Posts: 69
Joined: 6-November 13



Thanks. I can do what you just described, but that is to the [Merit Badges] table, right? How do I get the values updated in the [MBC_For] field of the [Adult] table? All I can see it in is the actual name (Backpacking) and not the ID (14), and the ID is what I’d need to change, after I re-order the [Merit Badges] table. Am I thinking about this correctly?
Go to the top of the page
 
June7
post Oct 23 2019, 10:15 PM
Post#8



Posts: 1,001
Joined: 25-January 16



Cannot rely on query ORDER BY. Report Sorting & Grouping overrides.

Changing existing PK/FK values is tricky even without MVF.

1. make copy of db

2. Add a number field called New in [Merit Badges]. Alpha sort table, enter sequential numbers into New.

3. Run an update query
UPDATE [Merit Badges] INNER JOIN [Milton Adults] ON [Merit Badges].ID = [Milton Adults].MBC_For.Value SET [Milton Adults].MBC_For.[Value] = [Merit Badges].[New];

4. Create a new table [Merit Badges New] with ID and [MB Name] fields. Run this command from the VBA immediate window.
CurrentDB.Execute "INSERT INTO [Copy Of Merit Badges](ID, [MB Name]) SELECT New, [MB Name] FROM [Merit Badges] ORDER BY [MB Name]"

5. Delete old [Merit Badges] table, rename [Copy Of Merit Badges].

6. Run Compact & Repair


This post has been edited by June7: Oct 23 2019, 11:11 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Oct 23 2019, 11:27 PM
Post#9



Posts: 1,001
Joined: 25-January 16



Correction:

5. Delete old [Merit Badges] table, rename [Merit Badges New].

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Oct 24 2019, 01:06 AM
Post#10



Posts: 1,001
Joined: 25-January 16



Sorting of combobox dropdown list has nothing to do with sorting of values saved in MBC_For. Values saved in MBC_For are IDs, not badge names. MBC_For sorts by ID.

Rats, I got new table name mixed up in my instructions. I can't edit post so hope you get my point.


This post has been edited by June7: Oct 24 2019, 01:09 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
isladogs
post Oct 24 2019, 01:31 AM
Post#11


UtterAccess VIP
Posts: 1,879
Joined: 4-June 18
From: Somerset, UK


@momo2000
You might find my article MVFs... and why you really shouldn't use them worth reading.
As it says towards the end of the article, I can recover the data as a standard Access table if you would like me to do that for you.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
arnelgp
post Oct 24 2019, 02:23 AM
Post#12



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


if you do want to keep your MVF, follow this steps.
first copy this codes to a Module:
CODE
Public Function fncConcat( _
                            table_name As String, _
                            pk_name As String, _
                            pk_value As Variant, _
                            mvf As String, _
                            lookup_table As String, _
                            lookup_field As String, _
                            lookup_return_field As String) As String

    Dim rp As DAO.Recordset2
    Dim rc As DAO.Recordset2
    Dim db As DAO.Database
    Dim values() As String
    Dim hasData As Boolean
On Error GoTo ExitFunc
    Set db = CurrentDb
    With db.CreateQueryDef("", "select t.[" & mvf & "] from [" & table_name & "] as t " & _
                "where t.[" & pk_name & "] = p0;")
        .Parameters("p0") = pk_value
        Set rp = .OpenRecordset(dbOpenSnapshot)
    End With
    If Not (rp Is Nothing) Then
        rp.MoveFirst
        Set rc = rp.Fields(mvf).value
    End If
    If Not (rc Is Nothing) Then
        With rc
            hasData = True
            .MoveFirst
            ReDim values(0)
            While Not .EOF
                ReDim Preserve values(UBound(values, 1) + 1)
                With db.CreateQueryDef("", "select t.[" & lookup_return_field & "] from [" & lookup_table & "] as t " & _
                                    "where t.[" & lookup_field & "] = p0;")
                    .Parameters("p0") = rc(0)
                    values(UBound(values, 1)) = Trim(.OpenRecordset(dbOpenSnapshot)(0))
                End With
                .MoveNext
            Wend
            .Close
        End With
    End If
    If hasData Then
        QuickSort values
        fncConcat = Join(values, ", ")
        If Left(fncConcat, 1) = "," Then fncConcat = Mid(fncConcat, 3)
    End If
ExitFunc:
    Set rp = Nothing
    Set rc = Nothing
    Set db = Nothing
End Function

Public Sub QuickSort(ByRef aSortArray As Variant, Optional ByVal lngFirst As Long = -1, Optional ByVal lngLast As Long = -1)
'Recursive array quicksort function single dimension

    Dim lngLow  As Long, lngHigh As Long
    Dim vntTemp As Variant, vntList_Separator As Variant

    lngFirst = IIf(lngFirst < 0, LBound(aSortArray), lngFirst)
    lngLast = IIf(lngLast < 0, UBound(aSortArray), lngLast)
    lngLast = IIf(lngLast > UBound(aSortArray), UBound(aSortArray), lngLast)
    lngLow = lngFirst
    lngHigh = lngLast

    vntList_Separator = aSortArray((lngFirst + lngLast) / 2)
    Do
        Do While (aSortArray(lngLow) < vntList_Separator)
            lngLow = lngLow + 1
        Loop
        Do While (aSortArray(lngHigh) > vntList_Separator)
            lngHigh = lngHigh - 1
        Loop
        If (lngLow <= lngHigh) Then
            vntTemp = aSortArray(lngLow)
            aSortArray(lngLow) = aSortArray(lngHigh)
            aSortArray(lngHigh) = vntTemp
            lngLow = lngLow + 1
            lngHigh = lngHigh - 1
        End If
    Loop While (lngLow <= lngHigh)
    If (lngFirst < lngHigh) Then QuickSort aSortArray, lngFirst, lngHigh
    If (lngLow < lngLast) Then QuickSort aSortArray, lngLow, lngLast

End Sub


now, create a query (qryAdultsMBNames) against Adults table:
CODE
SELECT Adults.PersonID, fncConcat("Adults", "PersonID", [PersonID], "MBC_For", "Merit Badges", "ID" ,"MB Name") AS ConCat
FROM Adults;


Create another query (qryForReport) that will Join Adults table to qryAdultsMBNames query:
CODE
SELECT Adults.*, T.Concat FROM Adults INNER JOINS qryAdultsMBNames AS T ON
T.PersonID = Adults.PersonID

use the query (qryForReport) as the Recordsource of your report.
remove field MB_for as controlsource and replace with ConCat field.
remove the rowsource of the combo.
change the combo into textbox with CanGrow=Yes.



--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
momo2000
post Oct 27 2019, 08:13 PM
Post#13



Posts: 69
Joined: 6-November 13



JUNE7: Tonight, tried the 6 steps you outlined (allowing for the corrections in the table names). I like you approach because I understood it wink.gif HOWEVER, when running the Update Query step #3, I received the following error (see attached screen shot): couldn't update 10011 records because of a key violation error. FYI: the [Milton Adults] table only has 2,835 records in it and [Merit Badges] only has 158 records. I pushed through and said YES and completed all of the steps, however, then when comparing before/after, it did not have the correct merit badges for each adult, of course, because the update failed. Any ideas why I received the error?
Attached File(s)
Attached File  Update_Error.png ( 6.64K )Number of downloads: 3
 
Go to the top of the page
 
June7
post Oct 27 2019, 08:47 PM
Post#14



Posts: 1,001
Joined: 25-January 16



I have your MBC_test db from another thread and used it to do that process and it worked. That sample has only 3 Adults records and 143 Merit Badges records. I would have to examine your full dataset.

This post has been edited by June7: Oct 27 2019, 08:50 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 28 2019, 09:29 AM
Post#15



Posts: 69
Joined: 6-November 13



Yes, that is the correct database. It is full of PII, so I can't send, but I get that it is hard to diagnose if you can't see.
Go to the top of the page
 
June7
post Oct 28 2019, 02:03 PM
Post#16



Posts: 1,001
Joined: 25-January 16



Why not follow suggestion to copy database and remove PII? Delete columns with address, phone, email, ssn. Or run an UPDATE action to set those to Null.
This post has been edited by June7: Oct 28 2019, 02:04 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
momo2000
post Oct 28 2019, 09:11 PM
Post#17



Posts: 69
Joined: 6-November 13



Oh, I didn't see that suggestion. I'll go get that done and post. Thanks!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 11:52 PM