Query Help please

[Gary]

I know the basics of query's.

I have one table and this table has two fields in it.

Field 1: Salesman
Field 2: primary key
(it has more but these are the only two needed for my question.)

I want to query the data, so that results are returned if they match a
salesman's name (so far so simple..) However, I want only the last
entry for that salesman to be shown. For example.

I search for salesman 'tom'
there are four records which have the salesman as tom.

I want only the last entry entered to be returned by the query. How do
i do that?

I have had the idea that I could use the primary key to make sure only
the last record entered is shown, by showing only the record which has
the highest number in its primary key field - but i dont know how i'd
accomplish this.

Thanks,
Gary.

 

[John Vinson]

I know the basics of query's.

I have one table and this table has two fields in it.

Field 1: Salesman
Field 2: primary key
(it has more but these are the only two needed for my question.)

I want to query the data, so that results are returned if they match a
salesman's name (so far so simple..) However, I want only the last
entry for that salesman to be shown. For example.

I search for salesman 'tom'
there are four records which have the salesman as tom.

I want only the last entry entered to be returned by the query. How do
i do that?

I have had the idea that I could use the primary key to make sure only
the last record entered is shown, by showing only the record which has
the highest number in its primary key field - but i dont know how i'd
accomplish this.

A Table HAS NO ORDER. Unless you have some field which can be reliably
counted on to be in ascending order, there is no way to reliably
identify the "last" record.

A Primary Key can be of any datatype (text, date/time, etc.) - but
assuming that your Primary Key is a sequential Autonumber, you can use
a Subquery: try putting this query into the SQL view of a new query
(and then adapting it to your needs):

SELECT * FROM yourtable
WHERE [Field 2] = (SELECT Max([Field 2]) FROM yourtable AS X
WHERE X.[Salesman] LIKE [Enter salesman's name:] & "*")


John W. Vinson[MVP]

 

[Guest]

Also you might have entered the date that the sale - if so, use a totals
query and set the TOTAL row as seen in the query sesign grid to LAST.

I know the basics of query's.

I have one table and this table has two fields in it.

Field 1: Salesman
Field 2: primary key
(it has more but these are the only two needed for my question.)

I want to query the data, so that results are returned if they match a
salesman's name (so far so simple..) However, I want only the last
entry for that salesman to be shown. For example.

I search for salesman 'tom'
there are four records which have the salesman as tom.

I want only the last entry entered to be returned by the query. How do
i do that?

I have had the idea that I could use the primary key to make sure only
the last record entered is shown, by showing only the record which has
the highest number in its primary key field - but i dont know how i'd
accomplish this.

A Table HAS NO ORDER. Unless you have some field which can be reliably
counted on to be in ascending order, there is no way to reliably
identify the "last" record.

A Primary Key can be of any datatype (text, date/time, etc.) - but
assuming that your Primary Key is a sequential Autonumber, you can use
a Subquery: try putting this query into the SQL view of a new query
(and then adapting it to your needs):

SELECT * FROM yourtable
WHERE [Field 2] = (SELECT Max([Field 2]) FROM yourtable AS X
WHERE X.[Salesman] LIKE [Enter salesman's name:] & "*")


John W. Vinson[MVP]

 

[John Vinson]

Also you might have entered the date that the sale - if so, use a totals
query and set the TOTAL row as seen in the query sesign grid to LAST.

This is not terribly reliable in my experience, Karl - LAST refers to
the last record *in disk storage order*, which is not necessarily
chronological. Try it by all means, but I'd be cautious!

John W. Vinson[MVP]