Help with incrementing a counter

[Ben M]

Hi



I have been asked to create a database for a user to store two kinds of quotes repair and production quotes, these had to be kept seperate so I have split it between a main repairs table with other repairs tables connected to it and a production side with the same. In each main table is a quote number (not a primary key) its used just for the uses refence so they know what quote number to put on documentation etc, but this number has to increment for both the production and repair sides e.g. if you created a production quote with the number 8798 then went the the repair side it would show 8799.
I have tried creating a Dmax counter with a +1 that looks for the largest number of both sides but have been unable to get it to work.
what kind of expression should i put on a form to get the counter working?
Is this even the right way to do this, is there a better way?

Thanks

[jzwp11]

Welcome to the forum!

Since the information is for quotes just of two different types, having separate tables violates normalization rules. You can keep them "separate" by designating the type via a field in the table. With all of the records in 1 table, the DMax() +1 should would fine.

[Ben M]

Thanks for the info

unfortunatly just having type designated via a field in the table is something the user wasn't happy with they wanted it to be totally seperate, I think i will have to explain that if they want that they can't have unique quote numbers across both sides and then let them make the choice.

[orange]

I agree with jzwp11 that your database structure is an issue. Your PK, it would seem, would be a compound PK made up of QuoteNumber + QuoteType. This combination would make the "records" unique. It seems from your post that there may be (but may not always be) some connection (something else going on) between Repair and Production quotes.

Getting your structures designed to support the business is key to database.

[jzwp11]

As a matter of good practice, your users should never see your tables. You would create a user interface that gives your users the impression that they are handling the types of quotes differently but the integrity of your table structure would follow normalization rules. I think that approach would be best in the long run for you, your users and most importantly the proper functioning of the application.