Filter a Filter

[KaiProton]

Im Writing a Dbase to Log our Drawing documents as they come from our Customers..

Now Each month there maybe the same part number but a NEW revision,

so the part number is on a form, and the Revision is on a sub form linked so there can be more than one attached to one part number,

thats the basic idea, on my query,

I only need the latest revision to be shown, they may change from
1st MPGA-44
2nd MPGA-45
Final 25-2-333

Ive given all the revisions in the sub form an autonumber to keep them in order, so I can Have them sorted on the form Descending,

so it reads the latest Revision on the top when the form is open,

My problem is that the Query shows all the Revisions, most of the time when looking up drawing ppl only want to see the latest revision,

can anyone help me limit to the highest sort number on the revisions ?


Thanks
Kai.

 

[gromit]

Hi -

Have you tried a query using the MAX of the autonumber?

- g

 

[KaiProton]

I will try that,

but I dont know how to add it to the WQB,
Im not great with SQL yet so.

The other thing is, in the sub form the autonumber just goes up,

so entry 1 maybe attached to enty 2 in the main table,

and entry 2 maybe attached to entry 3 in the main,

but entry 3 maybe attached to entry2 in the main table,.

so it would need to check for the maximum attached.

 

[gromit]

Yes, that is the problem with using Autonumber. The MAX of the revision date may be more reliable.

To use the MAX, go to the design view of the query. Hit the Totals sign ("Sigma" looks like a strange E) and then choose MAX for the aggregate function. The other fields may need to have GROUP BY selected. However, if you GROUP BY totally distinct fields, you will end up showing the MAX for each record (which does not help).

See the attached query for an example of using MAX.

- g