Filtering Data

C

Chad Cameron

Hi All,

I have created a query that pulls from multiple tables, to give me
information about drill holes (I work in the mining industry). Sorry, if
this is confusing, I am not sure how to word it easily.

When we drill a hole, the drill records it as TYPE=2. If the hole collapses
and they have to redrill the same hole, it is TYPE=3.

So, when I generate a report I will get something like this
101=2
102=2
102=3
103=2
103=3
103=3
104=2

So, hole 101 is fine. Hole 102 was drilled, it collapsed and was redrill
successfully. Hole 103 Collapsed twice and then was drilled successfully,
hole 104 is fine.

I don't care about the unsucessful holes, I only want my report to show the
sucessful holes. How would I go about doing this? This is what I want in
my report
101=2
102=3
103=3
104=2

Thanks
Chad
 
J

Jon Lewis

Try

SELECT [qryReport].[Hole], Max([qryReport].[Type]) AS MaxOfType FROM
qryReport GROUP BY [qryReport].[Hole]

I've assumed your using a saved query for your report called qryReport that
has fields Hole and Type. So create another query, switch to SQL view and
Paste in the above. You may be able to combine both queries as one.

HTH
 
C

Chad Cameron

Thanks Jon,

I tried your method and it didn't work. I have included the SQL code with
your suggestion, maybe I typed something wrong.

Thanks again
Chad

SELECT dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Max(dbo_HOLE_INFORMATION.TYPE) AS
MaxofType, Hole_Status.STATUS

FROM ((dbo_HOLE_INFORMATION INNER JOIN dbo_HOLE_POSITION ON
dbo_HOLE_INFORMATION.TIME_START = dbo_HOLE_POSITION.TIME_START) INNER JOIN
dbo_HOLE_PROFILE ON dbo_HOLE_POSITION.TIME_START =
dbo_HOLE_PROFILE.TIME_START) INNER JOIN Hole_Status ON
dbo_HOLE_INFORMATION.TYPE = Hole_Status.TYPE

GROUP BY dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Hole_Status.STATUS,
dbo_HOLE_INFORMATION.TYPE

HAVING (((dbo_HOLE_INFORMATION.ACTUAL_PATTERN)=[Enter Bench]) AND
((dbo_HOLE_INFORMATION.ACTUAL_BLAST)=[Enter Pattern]))
ORDER BY dbo_HOLE_INFORMATION.ACTUAL_NAME;



Jon Lewis @btinternet.com> said:
Try

SELECT [qryReport].[Hole], Max([qryReport].[Type]) AS MaxOfType FROM
qryReport GROUP BY [qryReport].[Hole]

I've assumed your using a saved query for your report called qryReport
that has fields Hole and Type. So create another query, switch to SQL
view and Paste in the above. You may be able to combine both queries as
one.

HTH

Chad Cameron said:
Hi All,

I have created a query that pulls from multiple tables, to give me
information about drill holes (I work in the mining industry). Sorry, if
this is confusing, I am not sure how to word it easily.

When we drill a hole, the drill records it as TYPE=2. If the hole
collapses and they have to redrill the same hole, it is TYPE=3.

So, when I generate a report I will get something like this
101=2
102=2
102=3
103=2
103=3
103=3
104=2

So, hole 101 is fine. Hole 102 was drilled, it collapsed and was redrill
successfully. Hole 103 Collapsed twice and then was drilled
successfully, hole 104 is fine.

I don't care about the unsucessful holes, I only want my report to show
the sucessful holes. How would I go about doing this? This is what I
want in my report
101=2
102=3
103=3
104=2

Thanks
Chad
Click to expand...
Click to expand...
 
C

Chad Cameron

I narrowed it down. I have a table that defines 1,2,3,4,5 types as a string
1-unknown, 2-drilled, 3-redrilled, etc.

When I include this text entity, is shows me all the types per hole, if I
remove it, it only shows me the max. I need this text to define what each
hole is. In my report no one will understand what 3 means on the report,
but they will understand 'redrill'.

Any ideas?
Chad

Chad Cameron said:
Thanks Jon,

I tried your method and it didn't work. I have included the SQL code with
your suggestion, maybe I typed something wrong.

Thanks again
Chad

SELECT dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Max(dbo_HOLE_INFORMATION.TYPE) AS
MaxofType, Hole_Status.STATUS

FROM ((dbo_HOLE_INFORMATION INNER JOIN dbo_HOLE_POSITION ON
dbo_HOLE_INFORMATION.TIME_START = dbo_HOLE_POSITION.TIME_START) INNER JOIN
dbo_HOLE_PROFILE ON dbo_HOLE_POSITION.TIME_START =
dbo_HOLE_PROFILE.TIME_START) INNER JOIN Hole_Status ON
dbo_HOLE_INFORMATION.TYPE = Hole_Status.TYPE

GROUP BY dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Hole_Status.STATUS,
dbo_HOLE_INFORMATION.TYPE

HAVING (((dbo_HOLE_INFORMATION.ACTUAL_PATTERN)=[Enter Bench]) AND
((dbo_HOLE_INFORMATION.ACTUAL_BLAST)=[Enter Pattern]))
ORDER BY dbo_HOLE_INFORMATION.ACTUAL_NAME;



Jon Lewis @btinternet.com> said:
Try

SELECT [qryReport].[Hole], Max([qryReport].[Type]) AS MaxOfType FROM
qryReport GROUP BY [qryReport].[Hole]

I've assumed your using a saved query for your report called qryReport
that has fields Hole and Type. So create another query, switch to SQL
view and Paste in the above. You may be able to combine both queries as
one.

HTH

Chad Cameron said:
Hi All,

I have created a query that pulls from multiple tables, to give me
information about drill holes (I work in the mining industry). Sorry, if
this is confusing, I am not sure how to word it easily.

When we drill a hole, the drill records it as TYPE=2. If the hole
collapses and they have to redrill the same hole, it is TYPE=3.

So, when I generate a report I will get something like this
101=2
102=2
102=3
103=2
103=3
103=3
104=2

So, hole 101 is fine. Hole 102 was drilled, it collapsed and was
redrill successfully. Hole 103 Collapsed twice and then was drilled
successfully, hole 104 is fine.

I don't care about the unsucessful holes, I only want my report to show
the sucessful holes. How would I go about doing this? This is what I
want in my report
101=2
102=3
103=3
104=2

Thanks
Chad
Click to expand...
Click to expand...
Click to expand...
 
C

Chad Cameron

It appears to be deeper than that.

Each time a hole is drilled, I get the Northing, Easting, Elevation, Type,
Status. Since these are different for each hole, I am guessing the Max
function isn't working because it is considered a different entity. If I
remove the northing, easting, elevation, status, the max function works fine
on the type field. So now I need a way to give priority to the Name, then
to Type. Have the max on Type and then if any other data is different to
ignore it.

Is this possible?
Chad


Chad Cameron said:
I narrowed it down. I have a table that defines 1,2,3,4,5 types as a
string
1-unknown, 2-drilled, 3-redrilled, etc.

When I include this text entity, is shows me all the types per hole, if I
remove it, it only shows me the max. I need this text to define what each
hole is. In my report no one will understand what 3 means on the report,
but they will understand 'redrill'.

Any ideas?
Chad

Chad Cameron said:
Thanks Jon,

I tried your method and it didn't work. I have included the SQL code
with your suggestion, maybe I typed something wrong.

Thanks again
Chad

SELECT dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Max(dbo_HOLE_INFORMATION.TYPE) AS
MaxofType, Hole_Status.STATUS

FROM ((dbo_HOLE_INFORMATION INNER JOIN dbo_HOLE_POSITION ON
dbo_HOLE_INFORMATION.TIME_START = dbo_HOLE_POSITION.TIME_START) INNER
JOIN dbo_HOLE_PROFILE ON dbo_HOLE_POSITION.TIME_START =
dbo_HOLE_PROFILE.TIME_START) INNER JOIN Hole_Status ON
dbo_HOLE_INFORMATION.TYPE = Hole_Status.TYPE

GROUP BY dbo_HOLE_INFORMATION.ACTUAL_PATTERN,
dbo_HOLE_INFORMATION.ACTUAL_BLAST, dbo_HOLE_INFORMATION.ACTUAL_NAME,
dbo_HOLE_POSITION.NORTHING, dbo_HOLE_POSITION.EASTING,
dbo_HOLE_POSITION.COLLAR_ELEVATION, Hole_Status.STATUS,
dbo_HOLE_INFORMATION.TYPE

HAVING (((dbo_HOLE_INFORMATION.ACTUAL_PATTERN)=[Enter Bench]) AND
((dbo_HOLE_INFORMATION.ACTUAL_BLAST)=[Enter Pattern]))
ORDER BY dbo_HOLE_INFORMATION.ACTUAL_NAME;



Jon Lewis @btinternet.com> said:
Try

SELECT [qryReport].[Hole], Max([qryReport].[Type]) AS MaxOfType FROM
qryReport GROUP BY [qryReport].[Hole]

I've assumed your using a saved query for your report called qryReport
that has fields Hole and Type. So create another query, switch to SQL
view and Paste in the above. You may be able to combine both queries as
one.

HTH

Hi All,

I have created a query that pulls from multiple tables, to give me
information about drill holes (I work in the mining industry). Sorry,
if this is confusing, I am not sure how to word it easily.

When we drill a hole, the drill records it as TYPE=2. If the hole
collapses and they have to redrill the same hole, it is TYPE=3.

So, when I generate a report I will get something like this
101=2
102=2
102=3
103=2
103=3
103=3
104=2

So, hole 101 is fine. Hole 102 was drilled, it collapsed and was
redrill successfully. Hole 103 Collapsed twice and then was drilled
successfully, hole 104 is fine.

I don't care about the unsucessful holes, I only want my report to show
the sucessful holes. How would I go about doing this? This is what I
want in my report
101=2
102=3
103=3
104=2

Thanks
Chad
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to average data in a query 3
Duplicate entry query on two tables 2
Sort data 2
Combining Worksheets 5
counting from different tables 1
auto index 1
index 1
Concatenate with differents conditions 3

Top