Hi Guys,
I have been working on a database for some time that requires a query of query.
The criteria for Query 1 is a number in a multi-valued field where it displays records for a series of parameters. This works fine as shown in the screenshot below in query 1
The criteria for Query 2 is the result of query 1. The parameters from the initial query are fed in to the second query criteria. The desired output for the query is that records that contain ONLY the criteria from query 1 are shown.
The problem that is occurring is that although any instance of the criteria are shown, additional parameters are shown. Hopefully query 2 will better demonstrate this; the parameters in green correspond to those in query 1. For example; using the ID to the right; records; 16, 19, 64, 67, 75 and 78 records meet the requirements that I want.
This issue is causing havoc with all of my queries in this database. I have spoken with a few database designers and I am aware how difficult multi-valued fields are, but with the extent of the number of parameters; having a field per parameter would be very difficult and it isn't possible to link a primary key to multiple foreign keys. I have tried sub queries and some wild card criteria however I still haven't managed to solve it.
Any help on what SQL might be needed to get rid of the unwanted parameters in that multivalued field?? or some recommendations as to database design that might negate the need for multivalued fields?
Cheers
Tom
I have been working on a database for some time that requires a query of query.
The criteria for Query 1 is a number in a multi-valued field where it displays records for a series of parameters. This works fine as shown in the screenshot below in query 1
The criteria for Query 2 is the result of query 1. The parameters from the initial query are fed in to the second query criteria. The desired output for the query is that records that contain ONLY the criteria from query 1 are shown.
The problem that is occurring is that although any instance of the criteria are shown, additional parameters are shown. Hopefully query 2 will better demonstrate this; the parameters in green correspond to those in query 1. For example; using the ID to the right; records; 16, 19, 64, 67, 75 and 78 records meet the requirements that I want.
This issue is causing havoc with all of my queries in this database. I have spoken with a few database designers and I am aware how difficult multi-valued fields are, but with the extent of the number of parameters; having a field per parameter would be very difficult and it isn't possible to link a primary key to multiple foreign keys. I have tried sub queries and some wild card criteria however I still haven't managed to solve it.
Any help on what SQL might be needed to get rid of the unwanted parameters in that multivalued field?? or some recommendations as to database design that might negate the need for multivalued fields?
Cheers
Tom
