Need help please with making this SELECT statement work!!!!!!

[Mekinnik via AccessMonster.com]

To put it simpily I have a form (test) with 2 comboboxes (cboReviewBy and
cboReview) both are populated from (tblHazinventory). I am trying to make the
following SELECT statement work.

SELECT *
FROM tblHazinventory
WHERE (((Forms!test!cboReviewBy)='Forms!test!cboReview'));


My goal is to have the user select one of four fields from (cboReviewBy)
being the column selected and one of X amount from (cboReview) being the row.

The final goal isto have the query list a single row based on the criteria of
both combo box selection as a read only page (for test purposes as a
datasheet)

 

[Tom Ellison]

Dear Mekinnik:

SELECT *
FROM tblHazinventory
WHERE Forms!test!cboReviewBy = ReviewBy
AND Forms!test!cboReview = Review

In the above, change the = ReviewBy to the name of a column in your table
you want to compare with the contents of that control, and similarly change
= Review to the column in the table for that test.

Your code would appear to be comparing the values in the two controls. This
result would be "static" and would either have no effect, returning all rows
in the table, or would cause nothing to be returned (probably the more
likely result).

Your statement about selecting "one of four fields" is puzzling. Do you
have the names of four columns as static data in the combo box. Oh, I'm now
guessing you want to choose one of 4 columns to be compared with the value
in the other combo box. The level of indirection I infer from your coding
is not available. You could do this dynamically, building the code for the
query in a VBA module, or you could use something like this:

SELECT *
FROM tblHazinventory
WHERE (Forms!test!cboReviewBy = "Column1" and Column1 =
Forms!test!cboReview)
OR (Forms!test!cboReviewBy = "Column2" and Column2 =
Forms!test!cboReview)
OR (Forms!test!cboReviewBy = "Column3" and Column3 =
Forms!test!cboReview)
OR (Forms!test!cboReviewBy = "Column4" and Column4 =
Forms!test!cboReview)

This rather heavy handed approach will work, but would not allow you to add
new columns to the test. You must replace the ColumnX literals and column
names I used with the actual names of your 4 columns.

If you have 4 mostly equivalent columns in your query like this, it probably
reflects a normalization problem. Is there any chance that the day will
come when 4 columns are not enough? Rather than having to add a new column
to the table and redesign multiple forms and reports, it's better to use
another table for such values. This is one of the rules of normalization.
It's a VERY good rule! Learn about it and follow it in the future. You'll
be glad you did in the long run.

Tom Ellison

 

[John Vinson]

To put it simpily I have a form (test) with 2 comboboxes (cboReviewBy and
cboReview) both are populated from (tblHazinventory). I am trying to make the
following SELECT statement work.

SELECT *
FROM tblHazinventory
WHERE (((Forms!test!cboReviewBy)='Forms!test!cboReview'));


My goal is to have the user select one of four fields from (cboReviewBy)
being the column selected and one of X amount from (cboReview) being the row.

What are the valid values of cboReviewBy? IT sounds like your table
structure is improperly normalized, if you need to let the user choose
a FIELDNAME in a query. What is the actual structure of the data, and
what are some typical values of these fields?

As Tom says - if we're understanding you correctly, a) you will need
to write VBA code to construct a SQL string, since you cannot use a
parameter for integral parts of a query such as fieldnames or
operators; and b) your table structure may need to be reviewed.

John W. Vinson[MVP]

 

[Mekinnik via AccessMonster.com]

What are the valid values of cboReviewBy? IT sounds like your table
structure is improperly normalized, if you need to let the user choose
a FIELDNAME in a query. What is the actual structure of the data, and
what are some typical values of these fields?

The values for cboReviewBy are always going to be (Unit name, Dept, Product
Name, and MSDS), those four values never change they are static, its
cboReview that is dynamic and changes based on the number of records in
tblHazinventory.

I am teaching myself as I go and have read a few good books on programming
Access. I have discovered that my original design is not a relational
database, it is merely a flat one, however i'm still going with it just as
training only, my final database will be a relational one..

Thank you both for all your assistance

 

[John Vinson]

my original design is not a relational
database, it is merely a flat one, however i'm still going with it just as
training only, my final database will be a relational one..

I'd suggest that you... Stop, NOW.

Training yourself on the complex getarounds needed to handle a
non-normalized database is WASTED EFFORT.

Instead, rebuild your database with normalized tables (don't worry,
you'll be able to salvage the existing data). Learning to work within
a properly structured environment will be MUCH MUCH more useful than
struggling with a flawed design!

John W. Vinson[MVP]

 

[Tom Ellison]

Dear Mekinnik:

I second John's motion. There is a touch and feel to good database work.
Training yourself to misunderstand this "touch and feel" would be highly
counter-productive. You won't need such work-arounds if you learn to do it
right. The benefit you're getting from doing so is slight, and the
potential to have a wrong-headed approach is significant.

Tom Ellison