Hi Adam,
I will try to do my best to explain.
(I'm going to change "perspective"
in hope that it will be easier to understand)
Let's start with offending simple table ("tblSeq")
f1 f2
1 5
2 9
3 15
4 13
5 19
Click on "Create Query in Design View"
In the Show Table dialog box,
click on your table,
click Add,
click on Add again,
and then click Close.
You should now show 2 copies of your table
in the query designer.
Right-mouse click on the left table
and choose Properties.
In the Alias row, type in
Prev
then close the Properties dialog box.
Right-mouse click on the right table
and choose Properties.
In the Alias row, type in
Next
then close the Properties dialog box.
Drag and drop each field from both
tables down into field rows in the grid.
Save the query for now and look at the results.
The SQL is pretty simple:
SELECT
Prev.f1,
Prev.f2,
Next.f1,
Next.f2
FROM
tblSeq AS Prev, tblSeq AS Next;
Our table had 5 records and in this "Cartesian
join" with itself, we get (5 x 5 =) 25 records.
In many query-construction cases, it can be helpful
to think through your problem starting with a
Cartesian join, i.e.,
-- this is "everything possible"
-- my job is to use joins, and/or criteria, and/or groups
to "slice-and-dice" what I want from "everything"
to get my desired result
Mentally slow down the process that Access goes through
to produce your results:
-- get a record from Prev
- get a record from Next and return all 4 fields
- get next record from Next and return all 4 fields
<continue until no more records in Next>
-- get next record from Prev
- get a record from Next and return all 4 fields
- get next record from Next and return all 4 fields
<continue until no more records in Next>
<continue until no more records from Prev>
--------------------------------------------------
Slice1: We want Next to only provide the "next"
f1 for a given Prev.f1
--------------------------------------------------
for example (case when Prev.f1 = 3, Prev.f2 = 15),
Prev.f1 Prev.f2 Next.f1 Next.f2
3 15 1 5
3 15 2 9
3 15 3 15
3 15 4 13 <--return this one
3 15 5 19
the f1's determine the order (what is "next"),
the f2's are (for now) just "along for the ride."
Given a *specific* f1 (say = 3), I imagine you would
not have a problem constructing a separate query
that finds the minimum f1 in tblSeq that is greater
than 3.
Select Min(t.f1)
FROM tblSeq As t
WHERE t.f1 > 3;
This is our "uncorrelated" subquery and would return 4.
In our step-by-step query, we want
to apply this for *each* Prev.f1 and we
can "correlate" this subquery back to the
the step-by-step query by substituting
"Prev.f1" for "3".
So, in the Criteria row under Next.f1, we type
(Select Min(t.f1) FROM tblSeq As t WHERE t.f1 > Prev.f1)
Our SQL now would look like:
SELECT
Prev.f1,
Prev.f2,
Next.f1,
Next.f2
FROM tblSeq AS Prev, tblSeq AS [Next]
WHERE
(((Next.f1)=(Select Min(t.f1) FROM tblSeq As t WHERE t.f1 > Prev.f1)))
ORDER BY Prev.f1, Prev.f2, Next.f1, Next.f2;
and with newly-added ordering would return:
Prev.f1 Prev.f2 Next.f1 Next.f2
1 5 2 9
2 9 3 15
3 15 4 13
4 13 5 19
-----------------------------------
Slice 2: We only want results where
Next.f2 < Prev.f2
(the offending records)
------------------------------------
This is easy...in the Criteria row under Next.f2, type
< Prev.f2
So our SQL would look like:
SELECT
Prev.f1,
Prev.f2,
Next.f1,
Next.f2
FROM tblSeq AS Prev, tblSeq AS [Next]
WHERE
(((Next.f1)=(Select Min(t.f1) FROM tblSeq As t WHERE t.f1 > Prev.f1))
AND
((Next.f2)<[Prev].[f2]))
ORDER BY Prev.f1, Prev.f2, Next.f1, Next.f2;
and would return:
Prev.f1 Prev.f2 Next.f1 Next.f2
3 15 4 13
Maybe this helps you understand the
process better.
In the previous post we used a "perspective"
where Prev would supply only the *previous* record,
as opposed to above, where we concentrated on
Next supplying only the *next* record.
If you have trouble extending this method to your
"tricky bit," please post back with more details and
I will try to help.
Good luck,
Gary Walter
Adam said:
I have to say I'm having trouble getting my head around exactly how the
query works (although it undoubtedly does work). I haven't seen a separate
query within a WHERE clause like that before. Is that what's known as a
subquery? I don't suppose you can point to any good resources where such
things are explained slowly and carefully?
Now for the tricky bit. I also have a grouping variable, and need to be able
to spot out of order values within each group as defined by the other
variable (or possibly variables). Can this SQL be extended to do that?
Many thanks
Adam
Here might be one method
(change "tblSeq" to actual name
of your table):
SELECT
Prev.Field1,
Prev.Field2,
Next.Field1,
Next.Field2
FROM
tblSeq AS Prev,
tblSeq AS [Next]
WHERE
(((Prev.Field1)=(Select Max(t.Field1)
FROM tblSeq As t
WHERE t.Field1<Next.Field1))
AND
((Next.Field2)<[Prev].[Field2]));
producing from your data:
Prev.Field1 Prev.Field2 Next.Field1 Next.Field2
3 15 4 13
Please respond back if I have misunderstood.
Good luck,
Gary Walter
I have a table that has two fields in it, which should both follow
the
same
order. In other words, the following data are OK:
Field1 Field2
1 5
2 9
3 15
4 16
5 19
but
Field1 Field2
1 5
2 9
3 15
4 13
5 19
is unusual, and the offending records (3 and 4) need to be identified.
I am really struggling to think of the best way to design a query
that
will
identify any records that have got 'out of order' in this way. All
suggestions gratefully received!
Many thanks
