I am trying to populate a “Pairs” listbox based on the values from several queries/tables. I currently have the listbox working to display data where two plug/jack identifiers match.
Once the user verifies that the plug/jack identifiers really do match, they save the configuration. This data is saved in a separate table [Config].
After the “configuration” is saved, I need to remove the instance of the “configuration” from the “Pairs” listbox. (easy enough with a .RemoveItem command).
HERE’S THE PROBLEM:
After the database is closed and the user reopens it on the following day, I need the “configurations” from the previous sessions NOT to show up in the “Pairs” listbox. So eventually the list dwindles down to nothing.
I do not have the luxury of freely adding new columns to the existing tables so I can’t set up an “internal record auditing” scheme.
HERES THE BACKGROUND:
I have a source table [Table1] with a field called [Name] that has both plug and jack identifiers.
Since the only difference between a plug and a jack is the letter P and J, I create two queries that call [Table1]. The first query [qryPlug] has a field [PlugName] that calls the Replace() function to remove the letter P, and the [qryJack] does the same to remove the letter J in the [JackName] field.
The third query [qryPairs] perform an INNER JOIN on the first two. The query fields [PlugName] and [JackName] are used in the INNER JOIN statement:
FROM [qryJacks] INNER JOIN [qryPlugs] ON [JackName] = [PlugName]
The results leave me with the matching records I need to show on the form.
Here is the RowSource call for the “Pairs” listbox:
Me.lstPairs.RowSource = "SELECT [PlugName], [PlugID], [JackName], [JackID], [EffID] FROM [_qryPairs];"
The table [Config] stores the “configurations” using the format:
[PlugID] [EffID] [JackID]
Next I created a new query [qryConfig] that is just a mirror of the table itself. I created a comma-delimited concatenation of the three fields above (i.e. “17,23,144”) in both the [qryPairs] and another in [qryConfig] queries then tried using the “Find Unmatched Query Wizard” and it did not return the results I need.
SOLUTION?:
What I need is a query that displays all the values of [qryPairs], but if these values are also in the table [Config], I need them to be excluded (possibly using the comma-delimited concatenation fields I described above).
I know it can be done by rigging up a temp table that store the results of [qryPairs], and then deleting all the records that match the values found in [Config], but life would be sooooooo much easier if the same can be accomplished using queries since it might not always be possible to create the temp tables.
ADDITIONALLY:
This query will only need to be run once each time the form is opened.
Running [qryPairs] typically results in 0 to 500ish records.
[Table1] typically has 20 to 5000 records
I will greatly appreciate ANY help I can get on this, I just seem to not be able to wrap my brain around it.
Once the user verifies that the plug/jack identifiers really do match, they save the configuration. This data is saved in a separate table [Config].
After the “configuration” is saved, I need to remove the instance of the “configuration” from the “Pairs” listbox. (easy enough with a .RemoveItem command).
HERE’S THE PROBLEM:
After the database is closed and the user reopens it on the following day, I need the “configurations” from the previous sessions NOT to show up in the “Pairs” listbox. So eventually the list dwindles down to nothing.
I do not have the luxury of freely adding new columns to the existing tables so I can’t set up an “internal record auditing” scheme.
HERES THE BACKGROUND:
I have a source table [Table1] with a field called [Name] that has both plug and jack identifiers.
Since the only difference between a plug and a jack is the letter P and J, I create two queries that call [Table1]. The first query [qryPlug] has a field [PlugName] that calls the Replace() function to remove the letter P, and the [qryJack] does the same to remove the letter J in the [JackName] field.
The third query [qryPairs] perform an INNER JOIN on the first two. The query fields [PlugName] and [JackName] are used in the INNER JOIN statement:
FROM [qryJacks] INNER JOIN [qryPlugs] ON [JackName] = [PlugName]
The results leave me with the matching records I need to show on the form.
Here is the RowSource call for the “Pairs” listbox:
Me.lstPairs.RowSource = "SELECT [PlugName], [PlugID], [JackName], [JackID], [EffID] FROM [_qryPairs];"
The table [Config] stores the “configurations” using the format:
[PlugID] [EffID] [JackID]
Next I created a new query [qryConfig] that is just a mirror of the table itself. I created a comma-delimited concatenation of the three fields above (i.e. “17,23,144”) in both the [qryPairs] and another in [qryConfig] queries then tried using the “Find Unmatched Query Wizard” and it did not return the results I need.
SOLUTION?:
What I need is a query that displays all the values of [qryPairs], but if these values are also in the table [Config], I need them to be excluded (possibly using the comma-delimited concatenation fields I described above).
I know it can be done by rigging up a temp table that store the results of [qryPairs], and then deleting all the records that match the values found in [Config], but life would be sooooooo much easier if the same can be accomplished using queries since it might not always be possible to create the temp tables.
ADDITIONALLY:
This query will only need to be run once each time the form is opened.
Running [qryPairs] typically results in 0 to 500ish records.
[Table1] typically has 20 to 5000 records
I will greatly appreciate ANY help I can get on this, I just seem to not be able to wrap my brain around it.
