Advanced query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure this will not be too advanced for someone but I'm lost with it, I'm
using Access 2000.

I have a table with almost 1 million records and 8 fields.
Field 1 is a primary key with an auto number.
Fields 2 - 5 need to be seen in the query but are not a factor.
Fields 6 is a text field, field 7 is a number field, field 8 is a number
field.
Field 6 is a location id, field 7 is a secondary location id.
Field 8 is a mileage figure contained within the field 6 location and field
7 secondary location.
There could be several mileage figures in field 8 that are the same so that
if you were to look at only fields 6, 7 and 8 there would what seem to be
lots of duplicate records.

What I want to query is that at each change in field 6 or field 7 show me
the highest and lowest figure in field 8.

Hopefully I've explained this ok.
Please let me know if I haven't.

Thanks
 
SELECT LAST(field2), LAST(field3), LAST(field4), LAST(field5), field6,
field7, MIN(field8), MAX(field8)
FROM myTable
GROUP BY field6, field7


I have used LAST for field2 to field5, to display values from one of the
record given a field6 and a field7.


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel

Thanks for the help and fast response but I'm afraid I must have phrased it
wrong.

Field 1 is important from a reference point of view as it is a route being
followed.
What I need from the query is to work through the table in order from record
1 then select each record in turn based on the parameters set out.

Let's say records 1 - 100 have the same data in fields 6 and 7 but variable
data in field 8, the query should look at the 100 records and return the
highest and lowest records based on the data in field 8.

Then let's say that in record 101 the data changes in field 6 or 7 but
remains the same until record 450, again with the data being variable in
field 8. I need the query to look at records 101 - 450 and return the 2
records within those 349 that are the highest and lowest values in field 8.

Sorry for the confusion and thanks for the help.

Regards

Les
 
Ha, then it may be something like:

SELECT a.*
FROM myTable As a
WHERE a.field8 =(SELECT MAX(b.field8) FROM myTable AS b WHERE
b.field7=a.field7 AND a.field6=b.field6)
OR a.field8 =(SELECT MIN(b.field8) FROM myTable AS b WHERE
b.field7=a.field7 AND a.field6=b.field6)


but that could be horribly slow, if you have millions of records. Maybe a
better approach would be to make a temporary table. Turn the query

SELECT field6, field7, MAX(field8) as mmax, MIN(field8) as mmin
FROM myTable
GROUP BY field6, field7

to make a table out of if. Add index on field6 and field7. Say that the new
table is called mm. Then


SELECT a.*
FROM myTable as a INNER JOIN mm ON a.field6=mm.field6 AND a.field7=mm.field7
WHERE a.field8=mm.mmax OR a.field8=mm.mmin



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel

I tried the first query but like you said, it was horribly slow. I was only
running it locally on a fairly new machine but I gave up after 30 mins.

I'm no Access or SQL expert but as far as I can see I followed your
instructions to the letter. When running the last query I get the message
'Type mismatch in expression'. Any ideas.
 
A type mismatch occurs if the two compared fields are of a different data
type (like one a string and the other, a number). Can you check that your
temporary table has its fields mmin and mmax of the same type as the field
field8 of your original table? Same thing for their fields field7 and
field6 that they have in common.


Vanderghast, Access MVP
 
Les said:
I'm sure this will not be too advanced for someone but I'm lost with it,
I'm
using Access 2000.

I have a table with almost 1 million records and 8 fields.
Field 1 is a primary key with an auto number.
Fields 2 - 5 need to be seen in the query but are not a factor.
Fields 6 is a text field, field 7 is a number field, field 8 is a number
field.
Field 6 is a location id, field 7 is a secondary location id.
Field 8 is a mileage figure contained within the field 6 location and
field
7 secondary location.
There could be several mileage figures in field 8 that are the same so
that
if you were to look at only fields 6, 7 and 8 there would what seem to be
lots of duplicate records.

What I want to query is that at each change in field 6 or field 7 show me
the highest and lowest figure in field 8.
Click to expand...
Pardon me for jumping in...

I just don't think you are going to get any suitable
SQL magic with this many records.

You might be better served if you bite the bullet and use a VBA routine

-- add a "Grouping" field (type Long) to your table
-- open a recordset to your table ordered by Field 1
-- cycle through records keeping track of previous Field 6 & 7
-- if get a change, increment grouping var and assign to
your Grouping field
-- if no change, assign grouping var to your Grouping field
-- after done, set index on Grouping field

then..your SQL is a no brainer

SELECT
Grouping,
Min([Field 1]) As GrpStart,
Max([Field 1]) As GrpEnd,
First([Field 6]) As Fld6,
First([Field 7]) As Fld7,
Min([Field 8]) As GrpMin,
Max([Field 8]) As GrpMax
FROM
yurtable
GROUP BY
Grouping;

It could be that you *might* get some performance boost
if you use a nested loop where you keep track of current
[Field 1] and only open up (say) 1000 records at a time
in your recordset. For example (*untested*)...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngField1 As Long
Dim strField6 As String 'or what type Field 6 is
Dim strField7 As String 'or what type Field 7 is
Dim lngGrp As Long

lngField1=0
strField6=""
strField7=""
lngGrp=0

Set db = CurrentDb()

Do
'<-- replace "yurtable" with actual name of your table -->
'depending on memory in your computer,
'maybe use TOP 5000 or TOP 10000...
strSQL="SELECT TOP 1000 [Field 1],[Field 6],[Field 7], " _
& "Grouping FROM yurtable " _
& "WHERE [Field 1] >" & lngField1 _
& " ORDER BY [Field 1];"
If DCount("*", strSQL) > 0 Then
'continue
Set rs = db.OpenRecordset( strSQL, dbOpenDynaset)
Else
'done
Exit Do
End If
rs.MoveFirst
Do While Not rs.EOF
If strField6=rs![Field 6] AND strField7=rs![Field 7] Then
'have same group
rs!Grouping = lngGrp
Else
'have new group
lngGrp = lngGrp +1
rs!Grouping = lngGrp
strField6 = rs![Field 6]
strField7 = rs![Field 7]
End If
rs.MoveNext
Loop
lngField1 = rs![Field 1]
rs.Close
Loop

db.Close

'{add error checking here and set rs,db to nothing}

Of course..I could have mistyped (or misthought)...
and this part will be slow...but final query should be
suitable...

Above assumes no Nulls in Field 6 and 7...
if not the case, change

If strField6=rs![Field 6] AND strField7=rs![Field 7] Then
 
on further thought...

need to get rs![Field 1] before .MoveNext
(would have been at EOF otherwise)

rs.MoveFirst
Do While Not rs.EOF
If strField6=rs![Field 6] AND strField7=rs![Field 7] Then
'have same group
rs!Grouping = lngGrp
Else
'have new group
lngGrp = lngGrp +1
rs!Grouping = lngGrp
strField6 = rs![Field 6]
strField7 = rs![Field 7]
End If
lngField1 = rs![Field 1]
rs.MoveNext
Loop

rs.Close

Loop

db.Close
 
Thanks Michel
One of the fields was set to text.
The query has run but the result isn't as expected. I was hopeful when I saw
52k records returned but the results are nowhere near.
Im just left with blocks of records.

Regards
 
I just don't think you are going to get any suitable
SQL magic with this many records.
Click to expand...

Really? It sounds simple to me, essentially:

SELECT Field6, Field7, MIN(Field8), MAX(Field8)
FROM Test
GROUP BY Field6, Field7;

If the OP requires more data from the original table they can put it
in a derived table (DT1) e.g.

SELECT T1.Field1, T1.Field2,
T1.Field3, T1.Field4, T1.Field5,
T1.Field6, T1.field7,
DT1.min_of_Field8, DT1.max_of_Field8
FROM Test AS T1
INNER JOIN (
SELECT Field6, field7,
MIN(Field8) AS min_of_Field8,
MAX(Field8) AS max_of_Field8
FROM Test
GROUP BY Field6, field7
) AS DT1
ON T1.Field6 = DT1.Field6
AND T1.Field7 = DT1.Field7;

Jamie.

--
 
Jamie Collins said:
Really? It sounds simple to me, essentially:

SELECT Field6, Field7, MIN(Field8), MAX(Field8)
FROM Test
GROUP BY Field6, Field7;

If the OP requires more data from the original table they can put it
in a derived table (DT1) e.g.

SELECT T1.Field1, T1.Field2,
T1.Field3, T1.Field4, T1.Field5,
T1.Field6, T1.field7,
DT1.min_of_Field8, DT1.max_of_Field8
FROM Test AS T1
INNER JOIN (
SELECT Field6, field7,
MIN(Field8) AS min_of_Field8,
MAX(Field8) AS max_of_Field8
FROM Test
GROUP BY Field6, field7
) AS DT1
ON T1.Field6 = DT1.Field6
AND T1.Field7 = DT1.Field7;

Jamie.
Click to expand...

I don't know....

I thought the table looked like:

Field1 Field6 Field7 Field8
1 a b 3
2 a b 4
3 a b 1
4 a c 5
5 a c 2
6 a b 8
7 a b 0

and wanted to return:

Min Max
a b 1 4 {from *first* a-b group}
a c 2 5 {from first a-c group}
a b 0 8 {from *second* a-b group}

which could easily be done with smaller table
all in SQL, but not with 1 million records on
computers like we have at work...
 
Gary

Thanks for your help on this.

I'm a bit of a novice when it comes to VB in Access, can you make it a
little more basic for me i.e. does the code get inserted into a VB module.

Any help would be greatly appreciated.

Regards
 
I don't know....

I thought the table looked like:

Field1 Field6 Field7 Field8
1 a b 3
2 a b 4
3 a b 1
4 a c 5
5 a c 2
6 a b 8
7 a b 0

and wanted to return:

Min Max
a b 1 4 {from *first* a-b group}
a c 2 5 {from first a-c group}
a b 0 8 {from *second* a-b group}

which could easily be done with smaller table
all in SQL, but not with 1 million records on
computers like we have at work...
Click to expand...

Well, the OP did say, "field 7 is a number field" ;-)

I don't know either! We have a different interpretation of "at each
change in field 6 or field 7", the two fields I figured were the
composite 'natural' key'. Your interpretation: as you say, it can be
done but not easily/efficiently, which suggests to me there is a
design flaw e.g. field 1 can't be a true key i.e. a sequence of 1
million?! I figure the "field 1" names are to obfuscate the business
being modelled so who can say?

Jamie.

--
 
Sorry for the cunfusion guys

Gary, that's exactly what the table and result should look like.
If the same combination of fields 6 and 7 appear later in the table then
they she be treated as a seperate entity and nothing to do with records
earlier in the table.
 
Les said:
I'm a bit of a novice when it comes to VB in Access, can you make it a
little more basic for me i.e. does the code get inserted into a VB module.
Click to expand...
I don't know...there are so many things that I could have
got wrong w/o testing. If you want to try this...

Start a new Module.
Save it as say "modGrp"
Paste the following in the module:

'***start code***
Option Explicit
Public Sub GetGrouping()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngField1 As Long
Dim strField6 As String 'you said 6 is Text
Dim lngField7 As Long 'you said 7 is number, so assumed Long
Dim lngGrp As Long

lngField1=0
strField6=""
lngField7=0
lngGrp=0

Set db = CurrentDb()

Do
'<-- replace "yurtable" with actual name of your table -->
'depending on memory in your computer,
'maybe use TOP 5000 or TOP 10000...
strSQL="SELECT TOP 1000 [Field 1],[Field 6],[Field 7], " _
& "Grouping FROM yurtable " _
& "WHERE [Field 1] >" & lngField1 _
& " ORDER BY [Field 1];"
If DCount("*", strSQL) > 0 Then
'continue
Set rs = db.OpenRecordset( strSQL, dbOpenDynaset)
Else
'done
Exit Do
End If
rs.MoveFirst
Do While Not rs.EOF
If strField6=rs![Field 6] AND lngField7=rs![Field 7] Then
'have same group
rs!Grouping = lngGrp
Else
'have new group
lngGrp = lngGrp +1
rs!Grouping = lngGrp
strField6 = rs![Field 6]
lngField7 = rs![Field 7]
Debug.Print "New Group: " & lngGrp & "; ID=" & rs![Field 1]
End If
lngField1 = rs![Field 1]
rs.MoveNext
Loop
rs.Close
Loop

db.Close

ErrorHandlerExit:
If Not rs Is Nothing Then Set rs = Nothing
If not db Is Nothing Then Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub
'*** end code ***

Find "yurtable" in code and replace with actual name of table.
Save module again.
Click on Debug/Compile.. in top menu to verify.
If compiles okay (and you have added "Grouping" field to table),
in Immediate Window, type

GetGrouping

then hit ENTER key

then go do something else for several hours...
(watch at first though for some stupid mistake I made)
 
If the same combination of fields 6 and 7 appear later in the table then
they she be treated as a seperate entity and nothing to do with records
earlier in the table.
Click to expand...

If the same combination of fields 6 and 7 appear later in the table then
they she be treated as a seperate entity and nothing to do with records
earlier in the table.
Click to expand...

But if they appear in consecutive rows they *are* the same entity?!

May I ask what such entities actually are in reality, likewise the
sequence number Field 1 that distinguishes entities? TIA.

Jamie.

--
 
Ah, I see a different meaning, now, to 'each field6 or field7 change'.


I *assume* your autonumber field generate records WITHOUT HOLE, ie, all
values from 1 to M, where M is the number of records, are present, without
dup. If not, the following solution won't work, as directly as exposed.


I would first create a new table, two fields, one autonumber, g1, as primary
key, and the second one a long integer, g2.

Next, append the record of your actual table to the new table (let us call
it g1g2):

INSERT INTO g1g2(g2) SELECT field1 FROM myTable ORDER BY field6, field7,
field1


That should produce a table with data like:


g1 g2=Field1 Field6 Field7 Field8
1 1 a b 3
2 2 a b 4
3 3 a b 1
4 6 a b 8
5 7 a b 0
6 4 a c 5
7 5 a c 2


(the last 3 columns are just for illustration, for reference, to show what
is going on, but they are not in the new table g1g2). That assumes that the
insertion is done as by the ORDER BY clause, which, from my experience,
seems to work, but I have no insurance that it always do.


And now, if the assumption holds, it is simply a matter to GROUP BY g1-g2:




SELECT LAST(a.field6), LAST(a.field7), MIN(a.field1), MAX(a.field1),
MAX(a.field8), MIN(a.field8)
FROM myTable As a INNER JOIN g1g2 ON g1g2.g1 = a.field1
GROUP BY g1g2.g1-g1g2.g2
ORDER BY MIN(a.field1) ASC



I added the min and max values for field1 that indirectly contributed to the
group.

Hoping it may help,
Vanderghast, Access MVP
 
I hope you found this change (if you tried it):

rs.MoveFirst
Do While Not rs.EOF
If strField6=rs![Field 6] AND lngField7=rs![Field 7] Then
'have same group
Else
'have new group
lngGrp = lngGrp +1
strField6 = rs![Field 6]
lngField7 = rs![Field 7]
Debug.Print "New Group: " & lngGrp & "; ID=" & rs![Field 1]
End If
rs.Edit
rs!Grouping = lngGrp
rs.Update
lngField1 = rs![Field 1]
rs.MoveNext
Loop
rs.Close
Loop

Sorry...been awhile since used this method and I forgot
DAO wants you to signal with .Edit, and assigning a
a value to a recordsource field won't stick without .Update...

Gary Walter said:
Les said:
I'm a bit of a novice when it comes to VB in Access, can you make it a
little more basic for me i.e. does the code get inserted into a VB
module.
Click to expand...
I don't know...there are so many things that I could have
got wrong w/o testing. If you want to try this...

Start a new Module.
Save it as say "modGrp"
Paste the following in the module:

'***start code***
Option Explicit
Public Sub GetGrouping()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngField1 As Long
Dim strField6 As String 'you said 6 is Text
Dim lngField7 As Long 'you said 7 is number, so assumed Long
Dim lngGrp As Long

lngField1=0
strField6=""
lngField7=0
lngGrp=0

Set db = CurrentDb()

Do
'<-- replace "yurtable" with actual name of your table -->
'depending on memory in your computer,
'maybe use TOP 5000 or TOP 10000...
strSQL="SELECT TOP 1000 [Field 1],[Field 6],[Field 7], " _
& "Grouping FROM yurtable " _
& "WHERE [Field 1] >" & lngField1 _
& " ORDER BY [Field 1];"
If DCount("*", strSQL) > 0 Then
'continue
Set rs = db.OpenRecordset( strSQL, dbOpenDynaset)
Else
'done
Exit Do
End If
rs.MoveFirst
Do While Not rs.EOF
If strField6=rs![Field 6] AND lngField7=rs![Field 7] Then
'have same group
rs!Grouping = lngGrp
Else
'have new group
lngGrp = lngGrp +1
rs!Grouping = lngGrp
strField6 = rs![Field 6]
lngField7 = rs![Field 7]
Debug.Print "New Group: " & lngGrp & "; ID=" & rs![Field 1]
End If
lngField1 = rs![Field 1]
rs.MoveNext
Loop
rs.Close
Loop

db.Close

ErrorHandlerExit:
If Not rs Is Nothing Then Set rs = Nothing
If not db Is Nothing Then Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit

End Sub
'*** end code ***

Find "yurtable" in code and replace with actual name of table.
Save module again.
Click on Debug/Compile.. in top menu to verify.
If compiles okay (and you have added "Grouping" field to table),
in Immediate Window, type

GetGrouping

then hit ENTER key

then go do something else for several hours...
(watch at first though for some stupid mistake I made)
Click to expand...
 
Jamie if you look at the example from garys post:

Field1 Field6 Field7 Field8
1 a b 3
2 a b 4
3 a b 1
4 a c 5
5 a c 2
6 a b 8
7 a b 0

and wanted to return:

Min Max
a b 1 4
a c 2 5
a b 0 8

The data in records 1 to 3 are compared because in record 4 either field 6
or field 7 change. The results show the highest and lowest values of field 8.

The query then returns the highest and lowest values for field 8 for records
4 and 5 because again in the next record the data in either field 6 or field
7 changes from the previous record.
It then moves on to the next record and so on...

I'm not sure if this can be done in SQL but the way Gary has laid it out in
plain text there is exactly what I'm trying to achieve.

To answer your question field 1 is simply an autonumber and goes from 1 to
984k.

Field 6 can either be 3 letters i.e. MAD or 3 letters and a number i.e.
MAD1, this field would be best described as a 'section'.

Field 7 is always a 4 digit number i.e. 1001 and can be best described as a
type.
The 'sections' would contain the types.

Field 8 is a mileage figure with 4 decimal places i.e. 5.0028. The 'types'
would contain the mileage figures.

Hope this helps, as always any help is greatly appreciated.
 
Hi Michel

There are no holes or gaps in any of the fields, all records have an entry
in each field.
Filed 1 is a simple autonumber starting at 1 and finishing at 954680.

Thanks
 
Back
Top