This seems like it should be easy, but for the life of me I can't figure it out.

I have a table that holds client information with the primary key as the client number.
example fields: Client#, LName, FirstName, ID#

Clients are given a number in a specific range for 4 different "types" of clients. For example, numbers from 1000 to 4999 are used for type A, 5000 to 9999 for type B, and so on.

All records are entered into one table through a data entry form. As a convenience to the person entering the data, i would like to be able to display the last client number for each type of client - this way the clerk can easily determine which client number to use for a new client.

I assumed this could be done with 4 queries and a 4 calculated text boxes on the form. So i made 4 queries - all they do is determine the last client number used for that specific type (A, B, C, D) So each query only returns one field and one value.

And here's where I have trouble. I can't figure out how to make the form display that one value from each of the 4 queries. Any ideas would be a big help!! I hope this makes sense....

I would probably use a select case and DMax function to retrieve the last values from the table and not use queries at all.

well i was reading the help files on DMax and it did look promising. but i couldn't figure out the syntax of the criteria that i would need.

I know how to put it into words though.. hehe I need the highest number in the client# field but only from the range of 1000 to 4999.

Putting 'all your eggs in one basket' is not a good idea when it comes to databases. You may think it will help the user, but in the long run it will cause more trouble than it is worth.

You need to 'normalise your data using two, maybe three tables.

HTH

Kevin M

You have made the common mistake of using a single data item to mean multiple things. Your client number indicates client type as well as providing a unique identifier for a client. You don't say what type of application this is but it really doesn't matter. At some point you will need to add a "new" client type or you will run out of numbers in a particular range causing you to assign an additional range for the same client type. In either case, programming will be required to accomodate the change. In a properly structured table, neither of these situations should occur.

The correct approach is to use two columns. One to hold the unique identifier (which should be an autonumber data type) and a second to hold client type.