SQL and null value problems

[MarkK]

And if you replace your ANDs with ORs, how does that fail to satisfy your requirement? Just trying to find the simplest solution, because any IsNull() check in the WHERE clause is going to make your SQL practically un-readable and difficult to maintain/understand/scale.

 

[HRN_SciTech]

And if you replace your ANDs with ORs, how does that fail to satisfy your requirement? Just trying to find the simplest solution, because any IsNull() check in the WHERE clause is going to make your SQL practically un-readable and difficult to maintain/understand/scale.

Because replacing with 'ORs' doesn't work. I see where you're coming from and I see why that should work but unfortunately it doesn't.. a computer programming friend of mine said SQL is not logical at all.

I tried fiddling around with putting the Iif(IsNull) in and I got it to work for one field (Synonyms) but could not get it to work for both Synonyms & Category so I know it is possible...

 

[jdraw]

Can you tell us or post a jpg of your table structure?
How do you get multiple synonyms for a chemical all within the Chemical table?

Is it possible to post a version of your database?

 

[HRN_SciTech]

I include all synonyms for that chemical under one heading 'Synonyms'. posting a jpg... hang on

 

[MarkK]

What do you mean "it doesn't work?" How does it fail?

Saying "it doesn't work" is like telling your doctor, "I feel sick." Not specific enough to troubleshoot.

And as per jdraw, posting your DB would be an advantage.

 

[HRN_SciTech]

What do you mean "it doesn't work?" How does it fail?

Saying "it doesn't work" is like telling your doctor, "I feel sick." Not specific enough to troubleshoot.

And as per jdraw, posting your DB would be an advantage.

If you are getting annoyed with this,you don't have to help, it's ok...

It doesn't work means it doesn't work. The function of the query is to return results searched for. If it does not do this, it doesn't work.

My clause works for "Where". Yours doesn't. That's what I mean.

 

[HRN_SciTech]

Are we able to upload pictures on here or do I have to upload to another site and link from there?

 

[jdraw]

Any update on that jpg?

You can upload a jpg here.

Goto the GOAdvanced button, then on the next screen-- manage attachments.

 

[MarkK]

I'm not annoyed.

 

[HRN_SciTech]

Ok here's a screenshot of the table

 

[HRN_SciTech]

And this is what happens if I remove one of the fields from a chemical entry and then try to search for it.

 

[jdraw]

A couple of observations:
-in database_ss3.jpg you show multiple synonyms in 1 field - this is unnormalized data and could be part of your issue. At the very least it is making your use of Access more difficult than it needs to be. Normalize your tables is my suggestion.

-I'm not sure what database_ss2.jpg and database_ss4.jpg are trying to tell the reader/viewer


I see
Chemical -->Synonym ----that is a chemical may have 0,1 or more synonyms.
Chemical --Category........a chemical fits into 1 and only 1 category

When you show amounts (of this chemical) do you record the date of that amount.
If someone takes/uses some chemical(s), how do those amounts get reflected in your inventory?

It is not common for a database to consist of 1 table. As I see it you have at least 3 possible tables Chemical, category, synonym and perhaps more with storage location and some of the other attributes.

Can you post a copy of the database with only some of the data? Just enough to show the issue with search.

 

[HRN_SciTech]

Sure. Attached.

 

[HRN_SciTech]

A couple of observations:
-in database_ss3.jpg you show multiple synonyms in 1 field - this is unnormalized data and could be part of your issue. At the very least it is making your use of Access more difficult than it needs to be. Normalize your tables is my suggestion.

-I'm not sure what database_ss2.jpg and database_ss4.jpg are trying to tell the reader/viewer


I see
Chemical -->Synonym ----that is a chemical may have 0,1 or more synonyms.
Chemical --Category........a chemical fits into 1 and only 1 category

When you show amounts (of this chemical) do you record the date of that amount.
If someone takes/uses some chemical(s), how do those amounts get reflected in your inventory?

It is not common for a database to consist of 1 table. As I see it you have at least 3 possible tables Chemical, category, synonym and perhaps more with storage location and some of the other attributes.

Can you post a copy of the database with only some of the data? Just enough to show the issue with search.

Forgot to quote your answer. Have attached the database in my last comment.

The issue I was trying to show in database_ss3 & 4 was that removing one of the fields e.g. synonym meant that chemical no longer showed up in the query.

 

[HRN_SciTech]

A couple of observations:
-in database_ss3.jpg you show multiple synonyms in 1 field - this is unnormalized data and could be part of your issue. At the very least it is making your use of Access more difficult than it needs to be. Normalize your tables is my suggestion.

How do I do this?

When you show amounts (of this chemical) do you record the date of that amount.
If someone takes/uses some chemical(s), how do those amounts get reflected in your inventory?

I am in control of the chemicals so each time they are used, the new amount is recorded. Unfortunately, those in charge of the chemicals before me have not done much record-keeping. The date is recorded when the data is entered into the system by me (which is the same date that they were inspected) so is fairly accurate.

Hopefully in future when I am more Access-savvy, I can instruct the other chemical users to record it themselves when they use any of the chemicals.

 

[jdraw]

Searching for Iodoethane,
My inputs:
searchChem eth
searchsynonym iod
searchcategory nothing, just hit return

Result as per attached jpg

Same result if I use your Form ChemSearch

Also, if I use the form, leave chem blank, leave synonym blank and just enter acid in category,
I get a list of acids --- as expected.

 

[HRN_SciTech]

Searching for Iodoethane,
My inputs:
searchChem eth
searchsynonym iod
searchcategory nothing, just hit return

Result as per attached jpg

Same result if I use your Form ChemSearch

Also, if I use the form, leave chem blank, leave synonym blank and just enter acid in category,
I get a list of acids --- as expected.

Hi yes that's fine, the problem is if those results didn't have either a synonym or a category listed, (so absolutely nothing written in the field), they would not have shown up if you had searched for them as above

 

[jdraw]

Here are 2 links regarding relational database and design.
1: Design Principles
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452
2: Entity Relationship Diagramming
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

I think these articles may help with design concepts and rationale. In my view design is your biggest issue, but you can continue with a 1 table database and create workarounds as needed - especially if you are/will be the only user.

What happens when you run a query along these lines?

Select * from tblChem where Category is Null