Ruled out the last row of this query

[bladu]

Hi,

I have the following query,


SELECT Control.Revision, Control.Fecha_ult_revision
FROM Control
WHERE (((Control.Numero)=[Num]) AND ((Control.Hoja)=[hoj]))
ORDER BY Control.Revision;


The aim of this query is try to obtain filtered the list of revisions, and its revision date (Fecha_ult_revision), of one number of plane (field Numero), and its sheet plane (Hoja), but I have to obtain a list of rows that goes from the first plan (origin), until penultimate plan.


I need to rull out the last row of the result of the query, if I do with ORDER (Asc), the first row if I do with ORDER (Desc).

The query needs to add more conditions, but I don`t know how.

F<or the moment:
The filter is done over the field "Revision", which has a field type of Text, one kind of format this field would be for example: 1 or 1.1, 1.1.1

The other field where the filter is done is the field "Hoja", which has a field type text, one kind of format this field would be for example:Number/
Number-->for example: 1/1

I would be very grateful if anybody could help me.

Thanks with anticipation.
Regards.

 

[jjturner]

Depending on how you determine a unique record, something like this would be one approach to use:

SELECT T1.Revision, T1.Fecha_ult_revision
FROM Control AS T1 LEFT JOIN
   (SELECT TOP 1 *
    FROM Control
    WHERE (((Control.Numero)=[Num]) AND ((Control.Hoja)=[hoj]))
    ORDER BY Control.Revision DESC) AS RecordToExclude
  ON T1.Numero = RecordToExclude.Numero
  AND T1.Fecha_ult_revision = RecordToExclude.Fecha_ult_revision
  AND T1.Hoja = RecordToExclude.Hoja
WHERE (((T1.Numero)=[Num]) AND ((T1.Hoja)=[hoj]))
  AND RecordToExclude.Numero Is Null
  AND RecordToExclude.Fecha_ult_revision Is Null
  AND RecordToExclude.Hoja Is Null
ORDER BY T1.Revision;

HTH,
John

 

[bladu]

Depending on how you determine a unique record, something like this would be one approach to use:

SELECT T1.Revision, T1.Fecha_ult_revision
FROM Control AS T1 LEFT JOIN
   (SELECT TOP 1 *
    FROM Control
    WHERE (((Control.Numero)=[Num]) AND ((Control.Hoja)=[hoj]))
    ORDER BY Control.Revision DESC) AS RecordToExclude
  ON T1.Numero = RecordToExclude.Numero
  AND T1.Fecha_ult_revision = RecordToExclude.Fecha_ult_revision
  AND T1.Hoja = RecordToExclude.Hoja
WHERE (((T1.Numero)=[Num]) AND ((T1.Hoja)=[hoj]))
  AND RecordToExclude.Numero Is Null
  AND RecordToExclude.Fecha_ult_revision Is Null
  AND RecordToExclude.Hoja Is Null
ORDER BY T1.Revision;

HTH,
John

Hi jjturner,

First of all, thanks for the answer.

Trying to do the same in Mysql I have tried the following query:

SELECT DISTINCT C1.Revision, C1.Fecha_ult_revision
FROM Control AS C1 INNER JOIN Control AS C2 ON C1.Revision<C2.Revision
WHERE (((C1.Numero)=[Num]) AND ((C1.Hoja)=[hoj]))
ORDER BY C1.Revision;

In Mysql runs well, and takes out the correct result. But trying the same in Access, the result is not the correct and after executing the query if I try to go to the preview view, it gives me the following message error:

Microsoft Access cannot represent the expression of the combination C1.Revision < C2.Revision in the preview view.

Are symbols like < or > possible in Access queries?

In any case, I will try your solution

Regards

 

[jjturner]

Hi bladu,

Using the other operators in JOIN expressions is certainly possible and allowed in Access, but this simply limits you to using the "SQL View" rather than the "Design View" (for some reason they haven't developed visual representations for these types of joins)

The SQL solution you're using for Mysql is quite clever and as far as I can tell it should work just the same in Access, unless you maybe need another field to JOIN ON to further limit the results. That is why I joined on as many fields as possible in my example - to try to ensure that the product wasn't creating unnecessary records...

HTH,
John

 

[bladu]

Hi John your example works wonderfully!!!!, you are very good

There is only thing that I don`t understand, Why you use "Fecha_ult_revision" field, I think It would be enough using Numero, Hoja, Revision fields. I have tried to delete "Fecha_ult_revision" field, but with this manner it doesn`t work. Would you mind if you could explain it?

Thank you very much with anticipation

Regards

 

[jjturner]

Hi bladu,

You might want to try this modification of your mysql query and see how it compares to what I originally suggested:

SELECT DISTINCT C1.Revision, C1.Fecha_ult_revision
FROM Control AS C1 INNER JOIN Control AS C2 ON
  C1.Revision < C2.Revision AND
  C1.Numero = C2.Numero AND
  C1.Fecha_ult_revision = C2.Fecha_ult_revision
WHERE (((C1.Numero)=[Num]) AND ((C1.Hoja)=[hoj]))
ORDER BY C1.Revision;

The basic approach for what I originally did was to
1) isolate the record which needed to be excluded by using the "SELECT TOP 1" predicate
2) compare that single record with the main query, by using a "LEFT JOIN" in conjunction with the "Is Null" criteria to exclude it from the main query

For this last point, what you are essentially asking the query to do is "show me all the records which are NOT in the set containing the single record".

Hope that makes a bit of sense. But like I originally stated, everything depends on how you define or determine the uniqueness of each record. That's what needs to be considered when you setup your JOIN conditions.

Cheers,
John

 

[bladu]

Thank you very much for your explanation.

Regards