Solved Saving Query creates ghost "duplicate" query with appended numbers (1 Viewer)

[mistyinca1970]

OK this is the weirdest thing I've ever seen.
I'm creating queries that have a criteria column that is a multi-select lookup. When I use this in my query, there are two queries saved: the one by my title and one that doesn't exist. I'm going to post screenshots because I know that does not make sense. For this example, look at the query called Email_APOD.

Here is the query. Committes.Value is a multi-select lookup. Only queries using this field create this issue. The image to the right is what you see in the Navigation Pane under queries. The image below is what shows up in my list box. Notice the entries with the long string of digits appended. When I opt to show hidden and sys objects, those crazy query names do not show up. They only show up here in the list box. I've never had this happen before. Any ideas how to get rid of them and stop them from happening? Thank you.

 

[MajP]

Committes.Value is a multi-select lookup. Only queries using this field create this issue.

In truth that query probably does actually exist. Welcome to the wonderful mystery world of multi select fields where data is saved in hidden tables. This is probably a helper query needed to query on the multi select field.
@isladogs may have more info. He is the expert on these system tables.
What is the sql for your listbox? I assume you are querying from the system table where object name like "EMAIL". You may have to add something like "AND len([objectname]) <12) to exclude this long names.

 

[mistyinca1970]

In truth that query probably does actually exist. Welcome to the wonderful mystery world of multi select fields where data is saved in hidden tables. This is probably a helper query needed to query on the multi select field.
@isladogs may have more info. He is the expert on these system tables.
What is the sql for your listbox? I assume you are querying from the system table where object name like "EMAIL". You may have to add something like "AND len([objectname]) <12) to exclude this long names.

I did have a Left Name, 6 "Email_" criteria in the List box, but even upon removing it, the offending queries display. Here is the way it stands now:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.Name;

I prefer it to have the additional language to show only those queries beginning with "Email_" as shown below:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~") AND ((Left([Name],6))="Email_"))
ORDER BY MSysObjects.Name;

 

[MajP]

Those query definitely are created when you query on a MVF. I did a quick demo and made a simple query called mvQuery. You can see the additional query created. I brought in some fields to try to decipher.

How about

WHERE (MSysObjects.Type =5)  AND (Left([Name],1) <>"~") AND (Len([Name]) < 12)

double check my number of ()

 

[mistyinca1970]

Those query definitely are created when you query on a MVF. I did a quick demo and made a simple query called mvQuery. You can see the additional query created. I brought in some fields to try to decipher.

View attachment 90424

How about

WHERE (MSysObjects.Type =5)  AND (Left([Name],1) <>"~") AND (Len([Name]) < 12)

double check my number of ()

Thank you. I am able to weed out the "ghost" queries. I'm using 24 instead of 12 as I have some longer query names and the "ghost" queries seem to be over 30 characters.

Do you suppose these fake queries will encumber the database at all? I only recently switched to the multi select. I had previously had a series of yes/no boxes in the table, but found it difficult to use parameters and that the multi select gave me more options.

 

[MajP]

These queries will not impact performance. Most serious access developers frown on the MVF. They are meant to simplify things, but in truce they tend to cause more confusion than simplify things. Most times you are safer to roll your own child table. It is a little more work, but it is much cleaner. If you use these judiciously than you will be ok.

If in the MVF you are really storing the visible value like a color Red, Blue, Green ... That is understandable. If instead you are storing a Key but showing the color
1 Red
2 Blue

Now you have a riddle wrapped in a puzzle wrapped in an enigma. You have a hidden table storing ID as fields, but showing multiple colors concatenated as string. My head is hurting.

 

[arnelgp]

you can also use Value List for your
List's Row Source Type

you can then fill your List using the Load event of the form:

Private Sub Form_Load()
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim nm As String
    Set db = CurrentDb
    For Each qd In db.QueryDefs
        nm = qd.Name
        If nm Like "email_*" Then
            Me.yourListbox.AddItem nm
        End If
    Next
End Sub

 

[isladogs]

As @MajP stated, multivalued fields are a complex datatype which relies on another deep hidden table (not visible in the navigation pane)
I would strongly recommend you don't use these (nor attachment fields as those work in a similar way)
See my article for a detailed explanation Multivalued Fields - Mendip Data Systems

However, for the purposes of this post, I have created a table with a text field and a multivalued field then run several queries on that table.
I've also created a form with two listboxes

Like you, I've run a query on MSysObjects to get a list of queries (Type = 5) but I've also included the very useful Flags field

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=5));

The 2 items starting ~sq_ are the row sources for the two listboxes on my form. These always have Flags = 3

The select queries have Flags=0 or 262144 depending on whether these are looking at the MVF field or its value

The unwanted 'phantom' queries with the long string at the end are created automatically be Access to handle the MVF field. All have a Flags value <0 (-2147352320). In fact these are real queries and you can actually run a query on them...but don't as you will confuse yourself further

I also included an update query (Flags = 48) along with own own associated 'phantom query' (Flags = -2147352272)
All action queries involving MVFs will cause similar additional queries to be created

Anyway, onto the form and its two listboxes. In each case, the second column is the Flags value
.

The listbox on the left shows all the queries using the same SQL as above for its row source
The other listbox filters the list to just the required items using:

SELECT qryQueryList.Name, qryQueryList.Flags FROM qryQueryList WHERE (((qryQueryList.Flags)>=0 And (qryQueryList.Flags)<>3));

This excludes all with Flags<0 (the unwanted items) and those with Flags=3
Or I could also have used

SELECT qryQueryList.Name, qryQueryList.Flags
FROM qryQueryList
WHERE (((qryQueryList.Name) Not Like "~sq_*") AND ((qryQueryList.Flags)>=0));

You can obviously filter the list further based on the Name field ...

To summarise, it is possible to omit the unwanted items by making use of the Flags field in your row source

BUT this is just a band aid to solve your current issue. If you continue to use MVFs, every step will be fraught with difficulties.
I would urge you to scrap the use of these now

Hope this helps rather than cause further confusion

 

[mistyinca1970]

Thank you for your help and suggestions! I am not married to the MVFs. If I can accomplish the same via another method, I am happy to take suggestions.

I originally had each option as a separate yes/no field. In fact, it is still there as I decide whether or not to keep the MVFs. But I wanted to use parameters in queries, and it wasn't working because I would have to create a parameter for each one of them and the user would have to answer multiple questions.

This is a contacts database, housing contact information and creating email lists that the user can select to send out batch emails to. The MVFs (and previously, the series of yes/no fields) indicate the committees and email groups to which each person in the database belongs. Those become the criteria in my queries.

I dislike the MVFs because I cannot run update queries on them to change the selected options. Also, I do prefer to use combo boxes in forms to force data validation rather than a lookup field at all.

What strategy would you suggest that would allow me to run a query with parameters (only 1 or two) and be able to perform update queries, while at the same time designating one or multiple committees that each record is associated with?

Thank you so much!

 

[MajP]

Kind of like @arnelgp said. I do something similar in most databases for queries and reports. Searching the system tables for available queries or reports is nice if you will update a lot. In this case I do not know if the user will be adding more email lists. If this does not happen often I usually do this in a table

ItemName DisplayName Description ItemType SortOrder

so if I have a report rptUserAccts I may have the display name User Account Report. Its Itemtype is Report and I might want a sort order for displaying the reports different than alphabetical in the list.

Obviously this forces you or the user to update the table, buty gives some nice flexibility.
This way you can display a nice user readable name, while maintaining a consistent object naming convention that the user does not see.

 

[MajP]

I originally had each option as a separate yes/no field. In fact, it is still there as I decide whether or not to keep the MVFs. But I wanted to use parameters in queries, and it wasn't working because I would have to create a parameter for each one of them and the user would have to answer multiple questions

You may need better normalization. Each yes no option, may actually need to be a child record in another table. Again this may be a little more work up front, but will in the long run be the most flexible and easiest to use.

 

[mistyinca1970]

You may need better normalization. Each yes no option, may actually need to be a child record in another table. Again this may be a little more work up front, but will in the long run be the most flexible and easiest to use.

Let me make sure I follow...would I then be creating a separate table for each yes/no question, or a separate table containing each of the committees (I already have this table) and then a table containing two records, yes and no, for each option in the committee table? How would that allow for the inclusion of multiple committees to each record in the contacts table? --Thank you.

 

[isladogs]

Actually you can update MVFs (see my article) but its tricky and far too easy to make a mistake.
Anyway, it seems you've decided to scrap the MVFs which is a good thing.

I'm not a fan of using value lists for listboxes nor of using parameter queries. But that's just me rather than either being bad practice.
In my opinion it is better to get users to select available options from one or more combos when combine the choices made to filter your data.
You mentioned the possibility of doing this. Is there any reason why you haven't tried it?
I have some example databases you can look at if you wish Multiple Group & Filter - Mendip Data Systems

 

[mistyinca1970]

Actually you can update MVFs (see my article) but its tricky and far too easy to make a mistake.
Anyway, it seems you've decided to scrap the MVFs which is a good thing.

I'm not a fan of using value lists for listboxes nor of using parameter queries. But that's just me rather than either being bad practice.
In my opinion it is better to get users to select available options from one or more combos when combine the choices made to filter your data.
You mentioned the possibility of doing this. Is there any reason why you haven't tried it?
I have some example databases you can look at if you wish Multiple Group & Filter - Mendip Data Systems

I love this idea! The reason I haven't tried it is that I don't know how. I will look at your links.

The parameters I've set up basically are for queries tied to a report, for example. I have a "sign in sheet" report that runs on a committee list query. User is prompted for parameters for committee, date, time, meeting location. After answering these parameters, the report runs with a perfect sign in sheet ready for printing.

 

[isladogs]

Exactly the same can be done with multiple combos.