Advanced Query Criteria?

[MildJoe]

Can anyone help with this?
I have a table with these two columns amongst others; "tDrawing" and
"tVersion".
I have both as primary keys. Each drawing have a text-name (DR001, DR002
etc.) and a version number (1,2,3 etc.).

I want to make a query that if two or more drawings have the same name, it
gives me the one with the highest version.

So the query should list all the latest versions of drawings in the table.

 

[George]

Dear friend, try the following?


SELECT Table1.tdrawing, Max(Table1.tversion) AS MaxOftversion
FROM Table1
GROUP BY Table1.tdrawing;

Change table1 to your table name.

Regards,

Ο χÏήστης "MildJoe" έγγÏαψε:

 

[MildJoe]

Thank you George!

This works fine when i only have these two columns. But if i have other
columns, like a signature, it doesnt work. when the signature is different
for three versions of a drawing, it returns all three drawings.
Is there any way to work around this? or do i have to use two queries?

 

[MildJoe]

Thanks!

it looks ok, but still doesn't work when i have another column, called
"signature", in the table. i want this also to appear in the query. The
signature may be different in each version.

Try something like

SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing=" & [tdrawing])

If the tdrawing is text, change the sql to
SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing='" & [tdrawing] &
"'")

--
Good Luck
BS"D

Thank you George!

This works fine when i only have these two columns. But if i have other
columns, like a signature, it doesnt work. when the signature is different
for three versions of a drawing, it returns all three drawings.
Is there any way to work around this? or do i have to use two queries?

 

[Ofer Cohen]

Adding another column shouldn't effect the display of the records.

Can you post your SQL?

--
Good Luck
BS"D

Thanks!

it looks ok, but still doesn't work when i have another column, called
"signature", in the table. i want this also to appear in the query. The
signature may be different in each version.

Try something like

SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing=" & [tdrawing])

If the tdrawing is text, change the sql to
SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing='" & [tdrawing] &
"'")

--
Good Luck
BS"D

Thank you George!

This works fine when i only have these two columns. But if i have other
columns, like a signature, it doesnt work. when the signature is different
for three versions of a drawing, it returns all three drawings.
Is there any way to work around this? or do i have to use two queries?

:

Dear friend, try the following?


SELECT Table1.tdrawing, Max(Table1.tversion) AS MaxOftversion
FROM Table1
GROUP BY Table1.tdrawing;

Change table1 to your table name.

Regards,

Ο χÏήστης "MildJoe" έγγÏαψε:

Can anyone help with this?
I have a table with these two columns amongst others; "tDrawing" and
"tVersion".
I have both as primary keys. Each drawing have a text-name (DR001, DR002
etc.) and a version number (1,2,3 etc.).

I want to make a query that if two or more drawings have the same name, it
gives me the one with the highest version.

So the query should list all the latest versions of drawings in the table.

 

[MildJoe]

Sorry!
I sat "group by" on the other columns. removing this, it seems to be fine.
But could you please explain what the criteria in the DMax-function says?

--- tdrawing='" & [tdrawing] & "'")

the tdrawing is text.

Thank you!

Adding another column shouldn't effect the display of the records.

Can you post your SQL?

--
Good Luck
BS"D

Thanks!

it looks ok, but still doesn't work when i have another column, called
"signature", in the table. i want this also to appear in the query. The
signature may be different in each version.

Try something like

SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing=" & [tdrawing])

If the tdrawing is text, change the sql to
SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing='" & [tdrawing] &
"'")

--
Good Luck
BS"D


:

Thank you George!

This works fine when i only have these two columns. But if i have other
columns, like a signature, it doesnt work. when the signature is different
for three versions of a drawing, it returns all three drawings.
Is there any way to work around this? or do i have to use two queries?

:

Dear friend, try the following?


SELECT Table1.tdrawing, Max(Table1.tversion) AS MaxOftversion
FROM Table1
GROUP BY Table1.tdrawing;

Change table1 to your table name.

Regards,

Ο χÏήστης "MildJoe" έγγÏαψε:

Can anyone help with this?
I have a table with these two columns amongst others; "tDrawing" and
"tVersion".
I have both as primary keys. Each drawing have a text-name (DR001, DR002
etc.) and a version number (1,2,3 etc.).

I want to make a query that if two or more drawings have the same name, it
gives me the one with the highest version.

So the query should list all the latest versions of drawings in the table.

 

[Ofer Cohen]

The DMax function bring the Max tversion for each tdrawing, so the records
that will be displayed in the query will be the one that equal to the max
tversion only.

--
Good Luck
BS"D

Sorry!
I sat "group by" on the other columns. removing this, it seems to be fine.
But could you please explain what the criteria in the DMax-function says?

--- tdrawing='" & [tdrawing] & "'")

the tdrawing is text.

Thank you!

Adding another column shouldn't effect the display of the records.

Can you post your SQL?

--
Good Luck
BS"D

Thanks!

it looks ok, but still doesn't work when i have another column, called
"signature", in the table. i want this also to appear in the query. The
signature may be different in each version.





:

Try something like

SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing=" & [tdrawing])

If the tdrawing is text, change the sql to
SELECT Table1.tdrawing, Table1.tversion
FROM Table1
Where Table1.tversion = DMax("tversion","Table1","tdrawing='" & [tdrawing] &
"'")

--
Good Luck
BS"D


:

Thank you George!

This works fine when i only have these two columns. But if i have other
columns, like a signature, it doesnt work. when the signature is different
for three versions of a drawing, it returns all three drawings.
Is there any way to work around this? or do i have to use two queries?

:

Dear friend, try the following?


SELECT Table1.tdrawing, Max(Table1.tversion) AS MaxOftversion
FROM Table1
GROUP BY Table1.tdrawing;

Change table1 to your table name.

Regards,

Ο χÏήστης "MildJoe" έγγÏαψε:

Can anyone help with this?
I have a table with these two columns amongst others; "tDrawing" and
"tVersion".
I have both as primary keys. Each drawing have a text-name (DR001, DR002
etc.) and a version number (1,2,3 etc.).

I want to make a query that if two or more drawings have the same name, it
gives me the one with the highest version.

So the query should list all the latest versions of drawings in the table.