Re: Identifying fields out of order

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 07/24/04 

Date: Sat, 24 Jul 2004 07:28:59 -0500

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" wrote:
>
> 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
>
>
> "Gary Walter" wrote
> > 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
> >
> > "Adam" wrote
> > > 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
> > >
> > > --
> > > Adam
> > >

Relevant Pages

  • Re: Identifying fields out of order
    ... I have certainly learnt something about SQL here. ... > Save the query for now and look at the results. ... > tblSeq AS Prev, tblSeq AS Next; ...
    (microsoft.public.access.queries)
  • Parameter from Form
    ... I have a query that uses a parameter on my form to update a combo box. ... My SQL: ... FROM tblPhases ... Prev by Date: ...
    (microsoft.public.access.queries)
  • SQL x Win 2003 SP1
    ... I just installed Win 2003 SP 1 and now I get this error on my sql server... ... The query cannot be executed because some files are either missing or not ... Luiz ... Prev by Date: ...
    (microsoft.public.sqlserver.setup)
  • Question using Like in a query
    ... It's been awhile since I've used SQL and I know there is a way to do this... ... I'm trying to create a Select query that selects items, ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: SPs/ADO.NET/DataSets/DataTables/DataAdapters/TableMapping
    ... you easily retrieve the table names with a simple SQL Query... ... > Good luck. ... Prev by Date: ...
    (microsoft.public.dotnet.framework.adonet)