Criteria for Boolean other than true/false

[InLaNoche]

Yes sounds confusing, but I'm having to filter out 500,000+ entries, and right now I have a Boolean field helping me.

well it's more of a lookup field. The data involved is borehole info, which has up to 3 types of materials in the sample. This data has been provided to us, we just have to sort it out for what we need. Ok, long story short, I added a Boolean field to their lookup table to allow us to "mark" undesirable materials. If the box is checked, I want to filter it out... Simple.... maybe...

the way they have the data is that each row of the table has 1-3 possible materials. so it is possible for the 2nd 2 fields to be empty. when that is the case, the Boolean result based on the look up is neither an empty box or a check. Instead it is a full box. What is the value of this full box?

When I run the query with the criteria Not True, I only get the unchecked boxes. I want to exclude the checked boxes, but I need the blank entries as well.

[InLaNoche]

more complicated than I thought, though I think I have a work around-ish... I changed the lookup field from Boolean to Text, so it will store a Yes or No. I found that when I ran criteria on the 3 layer type lookups, it would not give proper results. So instead, I made a new query field that checks if each layer material is Yes. If it is then set 0, if not set 1 (which seems to grab the blank fields as 1, so works out).

The problem is I cannot cull the 0s at this point, so I guess I have to run another query (since this value is generated in this query) to then drop the 0s...

[HiTechCoach]

If this were my project here is what I would do.

1) Make a new table for for materials that has the priamry bey and the materil ID.

2) create an append query that looks for all record that have soemitng in marterial one and append to the new table
3) repeat step two (2) for th other two material fields.

The resulting table should only have record where there is actually material.

Now that you have the data properly normalized it should be easy.

Yu could also try a Union query that creates the same recordset as the table above. With your large table it may not work well. That is why I suggest making a new table.

[InLaNoche]

Unfortunately, I'm not sure I could do that. I'm no Geo Tech, but it would appear that those who designed the db were. The team here wants the data shown the way it is here. I left out that there are depth markers for the materials, so in a given borehole, there might be more than 1 sample layer with up to 3 different materials. This leaves a lot of blank fields which is what was causing the issues.

Thanks, I will keep this in mind for later projects. Now that I have my foot in, there are more and more people looking to push me in...

[HiTechCoach]

Unfortunately, I'm not sure I could do that.

Why not ? I do it all the time.


... but it would appear that those who designed the db were

If you are talking about the data you received, it is NOT in a database format. You must convert the data into a relational; database format.

I work with well data from outside sources a lot for clients. The data comes in flattened (not normalized). It is NOT in a relational database format by design. It is done this way so that it is does not match any specific relational database or application. This makes it much easier to import the data into your normalized relational database structure.

[InLaNoche]

The do have relations set up, but it is more on how the data is entered and stored. Maybe it's more than I can handle in that manner, at this time. The data is over 700,000 records for the boreholes, and each borehole can have multiple material samplings.... Well looking back at what you were saying, I can see that I could do that. Before I go there though, I am hitting roadblocks with the amount of data that needs to actually be seen vs. what is there.

Most of my morning has been spent trying to get the query to give an output. I have generated a "bad" list and was trying to run an SQL query to remove the bad from the good:

SELECT * FROM tblborehole WHERE borehole_ID NOT IN (SELECT borehole_ID FROM tbl_BadBoreHoles)

As a test, I made 2 tables and ran the same style SQL and it worked. I let the above line run for over 1/2 hour before killing the process.
(as I was writing this)
So they got me the co-ordinates for the specific zone, which knocked down the total record count to 4500. The above SQL statement is now working and I have my select data. HiTechCoach, I may have to go into clean up the material records like you stated above, as they are looking for one complete line for all the data in one borehole (they are killing me here), though it seems this is more geared for a report. I wish I could share a sample with you so you can see what I am working with, but 2 things stop me. The file is 1.77GB (after compact and repair), and it is government info that I am not allowed to display. Anyway, thanks for listening and posting. Though it may not seem like you have helped directly, Any bit of learning I get from you guys will make me that much better in the future...

Thanks again.

[HiTechCoach]

I use Access as the front end with an SQL Server Express back end. SQL Server Express is free and has a 10 gig size limit per database.

I would definitely urge you to split the Access database if you have not. If you must keep the back end in Access you may have to also split the back end.


SELECT * FROM tblborehole WHERE borehole_ID NOT IN (SELECT borehole_ID FROM tbl_BadBoreHoles)

That is not a very efficient sub query. You need a WHERE clause in the sub query,

Why not use a Unmatched query?

[InLaNoche]

As this was possibly a one time thing, and the data was handed to us in this format from the government, We got it as is. I too would have put it into our SQL server, but alas, that is not my call... In that SQL statement, I exported my original data marking the bad boreholes to a new table as it was a static picture of the data (this db is purely reference) marking what boreholes fit the "bad" criteria. This was purely just the borehole IDs.

Never unsed an Unmatched Query before, something more to research...

[HiTechCoach]

I would also recommend learning more about sub queries and how to use a WERE clause.

[InLaNoche]

Will do. I know about sub-reports, but not about sub queries.