Display only Top / Max Revision

[irsmalik]

Hi dear friends,
I am making a database regarding Drawings Incoming / Outgoing history.
I have a table's fields
DWG#
Rev
Date
Transmit
Incharge

I have a DWG# which has following Rev
0A
0B
0C
2A
2B
2C
3A

Now 3A is the latest. I want to make a query based report which shows the DWG status of only Top / Max Rev of the Drawing.
When I create & run such kind of query, it shows all the records. But I want only One Record of the Top Rev / Max Rev.
Please some one help me... Access Table is attached. Thanks...
Irshad Ahmed

[Narender Sagar]

Hi
I think the problem is with Table structure.
You have all Text fields for REV etc..
Access is unable to understand or compute maximum or minimum values in a Text Field (This is what I understand)

[irsmalik]

So what you suggest what should be the field format ?
How can we define AlfaNumaric. Plz guide.

[Narender Sagar]

Before giving any suggestion, I would like to know your logic behind REV field. I mean, why do you want to have alphanumeric field only, for REV.
If it is really required to have alphanumeric character there, then you can make a separate table where you can maintain a numeric field and a alphanumeric field (Text field). Then you can give reference of this table in your original table.

[irsmalik]

This is the requirement of DWG table... Revision of a drawing will be in the Txt + Numaric.... like
0A
1A
2A
3A
so plz have a look at this. thanks

[Narender Sagar]

I have another idea, (I tried it on your sample database)
If your requirement is to find out Max/Top REV then you can also do it with Date field (which is already there).
I also created a crosstab query (another view of looking at the data)
Pl. check attachment.
HTH

[irsmalik]

Thanx dear Narender.... but I need it in Select Query. coz
I need Rev in single line... (one column).

[Narender Sagar]

Just change the last column in crosstab query to REV (In design mode) you'll get the REV in columns.

[irsmalik]

Yes it works...
Thanks dear.... For a nice help....
In future... Can i consult such kind of help. ?

[Rabbit]

Just a quick note, most SQL engines (Access included), can calculate a max or min on text fields. There are some caveats though. One, it calculates based on a byte by byte comparison from left to right. This can result in unexpected results if numbers are not carefully padded. And two, it calculates it using the ASCII value of the character.

[Narender Sagar]

Thanks Rabbit :)
Irshad, glad to know it works as per your expectation.
Yes, you can seek help from the group. and I am confident there are so many great and knowledgeable persons who are eager to help. :)