multiple critera search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to search mulltiple fields for the same but multiple data.

each record contains 10product feilds i need to search all for a product, to
be input by the user,the products do not always occur in the same field i can
do this for one product. I need to do it for upto 10products but to ignore
any inputs left blank.

help would be much appreciated.
 
Miree said:
I am trying to search mulltiple fields for the same but multiple data.

each record contains 10product feilds i need to search all for a product, to
be input by the user,the products do not always occur in the same field i can
do this for one product. I need to do it for upto 10products but to ignore
any inputs left blank.
Click to expand...


The use of multiple product fields is a disaster waiting to
happen. Your searching problem is just the tip of the
iceberg that will eventually sink your database. Instead,
you should follow the rules of database normalization and
have a separate table with one product field (which make the
search problem disappear).

As it is, you will have to use the same criteria in all 10
fields, but on different OR rows in the query design grid.
 
The use of multiple product fields is a disaster waiting to
happen. Your searching problem is just the tip of the
iceberg that will eventually sink your database. Instead,
you should follow the rules of database normalization and
have a separate table with one product field (which make the
search problem disappear).
Click to expand...

Hi, the OP here. Thanks for you advice on normalization. The thing is,
I have a business requirement that each entity for which the record
relates must have exactly 10 products, no more, no less (including
zero). With my existing design, this is simple to enforce i.e. 10 NOT
NULL (Required = Yes) columns. With your proposed design, I don't
think this can be enforced in the data layer (while it could in theory
be enforced using a table-level CHECK constraint, in practice I would
need to DROP it -- and remember to recreate it -- every time I need to
add a new set of ten rows to the table, making it impractical).

So should I fully normalize and leave myself wide open to data
integrity issues? Or stick with my existing design and perhaps have a
normalized helper VIEW (non-parameterized stored query that returns a
resultset) to make querying easier e.g.

CREATE VIEW Helper
AS
SELECT entity_ID, Product1 AS Product
FROM MyTable
UNION ALL
SELECT entity_ID, Product2
FROM MyTable
UNION ALL
SELECT entity_ID, Product3
FROM MyTable
UNION ALL
....
UNION ALL
SELECT entity_ID, Product10
FROM MyTable;

PS I'm not really the OP, I'm just trying to engender a meaningful
context for discussion.

Jamie.

--
 
Thank you for your advice, unfortunately normalising the data into one field
defeats the purpose of wht i am trying to do, i have figured out if i run a
query to find all records with one product then take the results and run
another query off that to find my next product it works fine.
 
Back
Top