Drop downs to select queries

[Steve]

I have a very large database (by my standards) that holds details of
35,000 products that I need to produce price lists for. It relies on
information produced by others that I have no control over and is
frankly a dogs breakfast as far as standardisation goes. However I am
stuck with it.

Products fall into Groups and Categories within Groups then sizes and
prices within Categories.

I have laborishly marked each product as to the Group and Category
with Yes/No fields for the Groups and a combo box drop down for the
Category so I end up with each product having a tick in one of 10 Yes/
No fields for the Group and a name for the Category eg
a 3 inch pipe made of red polyethelene would be marked as belonging to
Group A (Yes/No) and category Pipes (text). It may also belong to
Category C and for this group may be a different category (see dogs
breakfast comment above).

At present I have a whole series of queries where I show (for example)
only those items where Group A is true and where the category =
"pipes" and this query feeds my report/price list. Every time a new
Group or Category is produced, I need to add new queries as
appropriate

Is there any way I could standardise so I have a query where I can
choose from two combo boxes and have the query find only those
products. ie combo one would allow me to choose which Group has been
marked as Yes and combo two would allow me to choose which category.

Hope I have explained this properly and appreciate any help
TIA
Steve

 

[Tom van Stiphout]

On Tue, 1 Jan 2008 13:14:03 -0800 (PST), Steve

That's a horrible design, upon which no decent app can be built. I
would argue that yes you cannot change what dogfood they feed you, but
with some effort you can chew and digest this data and import it in a
decent db design.

That said, to your question: yes, you can do that, by creating a
dynamic sql statement. That would be MUCH better than writing a query
for each permutation <shudder>.
If I understand you correctly the sql statement would be:
select * from SomeTable
where SomeYesNoField=Value1 and SomeTextField=Value2.
Your form would allow the user to select the various parts of this sql
statement, after which you make this the RecordSource for your form or
report.

-Tom.

 

[cw]

Hi Steve,
If you have not gotten too far into your database build - I would highly
suggest changing your structure / design layout. This will allow you to grow
your system easily when new products / categories are added to your database.

I would recommend the following table layout(s).

Category Table - provides a list of all the categories available
Group Table - provides a list of all the groups available
Products Table - provides a look up to Category and Group Table and a filed
for Price and a field for sizes.

To ensure no duplicates hit the table - make all fields in the Product Table
part of your KEY.

Then you can run one query with prompts pull back the results you are
looking for.


If you can not change the layout - in the design view NOT the SQL View of
your query - you can actually add prompt's to the query.

you would do this by going to the criteria of a field and putting the prompt
in brackets [Yes or No?] or [What Category?],etc.

When you run the query - you will be asked to enter the Yes or No - or, any
other value you are looking for your query to pull back results for in a
specific field.

Hope this helps.
cw

 

[cw]

One more thing - you can use the like[What Category?] by using the like -
you can type in *dog* and it will bring up anything with the word dog in it -
like "Premium Dog Food".

Also - to put some control around the data people are feeding to you - you
can create an excel spreadsheet with a "Pick List" for the user. It would
contain only the Products / Categories that are in the db - that way - you
get consistant info and you don't have to spend as much time cleaning your
data before import.

Hope this helps.
--
cw

Hi Steve,
If you have not gotten too far into your database build - I would highly
suggest changing your structure / design layout. This will allow you to grow
your system easily when new products / categories are added to your database.

I would recommend the following table layout(s).

Category Table - provides a list of all the categories available
Group Table - provides a list of all the groups available
Products Table - provides a look up to Category and Group Table and a filed
for Price and a field for sizes.

To ensure no duplicates hit the table - make all fields in the Product Table
part of your KEY.

Then you can run one query with prompts pull back the results you are
looking for.


If you can not change the layout - in the design view NOT the SQL View of
your query - you can actually add prompt's to the query.

you would do this by going to the criteria of a field and putting the prompt
in brackets [Yes or No?] or [What Category?],etc.

When you run the query - you will be asked to enter the Yes or No - or, any
other value you are looking for your query to pull back results for in a
specific field.

Hope this helps.
cw

--
cw

I have a very large database (by my standards) that holds details of
35,000 products that I need to produce price lists for. It relies on
information produced by others that I have no control over and is
frankly a dogs breakfast as far as standardisation goes. However I am
stuck with it.

Products fall into Groups and Categories within Groups then sizes and
prices within Categories.

I have laborishly marked each product as to the Group and Category
with Yes/No fields for the Groups and a combo box drop down for the
Category so I end up with each product having a tick in one of 10 Yes/
No fields for the Group and a name for the Category eg
a 3 inch pipe made of red polyethelene would be marked as belonging to
Group A (Yes/No) and category Pipes (text). It may also belong to
Category C and for this group may be a different category (see dogs
breakfast comment above).

At present I have a whole series of queries where I show (for example)
only those items where Group A is true and where the category =
"pipes" and this query feeds my report/price list. Every time a new
Group or Category is produced, I need to add new queries as
appropriate

Is there any way I could standardise so I have a query where I can
choose from two combo boxes and have the query find only those
products. ie combo one would allow me to choose which Group has been
marked as Yes and combo two would allow me to choose which category.

Hope I have explained this properly and appreciate any help
TIA
Steve