Hi,
I am trying to make a table that concatenates a client_id with the date to make a primary key. I have a transactions table with a list of clients. A client can appear more than once, but only one time per day. I want to stay away from the autonumber function and use the client_id concatenated with the date. Does anyone know how to go about this?

Thanks,
Tam

My question would be why do you want to stay away from autonumber? You can have a compound index on the two fields to maintain unique values, but you're opening a VERY big can of worms with the approach you are wanting to take.

Noah

Well, first i would heartily disagree with not using an autonumber as your key...I have never run across a situation where not having an autonumber is a better idea. But...I don't think concatenate is what you mean here. You can make a primary key out of two fields by going to desin view and selecting both fields to select the key. The combination, of course must be unique within the table.

To avoid this would make your life easier. You can still index the two field and demand that it be a unique combination.

I do agree that using the autonumber would be easier, but in school, I have always been taught to avoid using the autonumber. I am assuming that there are some long term effects if the database gets too big, but I have never ran into any problems. You would think that if they told me to not use the autonumber, then I would have learned how to avoid it. I must have been sleeping. Does anyone else have any suggestions for a primary key other than the autonumber? Otherwise, autonumber it is.

Thanks,
Tam

Autonumber is the best and easiest pk. I'm not sure the reasoning behind what you were taught in school but the pk doesn't have to mean anything to the user so it doesn't matter what it is. Indexes should be built on the fields that mean things to the user and will be linked on or used as lookup fields.

HTH
Ken