Append Query (1 Viewer)

Can anyone help?

I have a Customer Table with up to 999 customer enquires on it.

When an enquiry is 'dead' I have an append query that moves the 'enquiry number' from the Customer Table to a Dead Quotes table.
This is actioned by a macro button which refreshes the form, runs the append query, then deletes the data from the Customer Table.
This works well, but deletes the enquiry number (which I want to use again).

Is there a way to automatically reinstate the enquiry number on the Customer Table?

Thank you

This sounds like a poor way to manage the data. It's not considered good practice to "move" data between tables in this way.

I'm also really puzzled as to the need or desire to reuse the enquiry number - surely it should be unique, and never repeated? What if Mrs Smith had enquiry number 345 , and didn't realise it had been reassigned to John Doe, and phones back in for an update? They will get John Doe's enquiry details?

The normal practice would be to add a field to flag that the enquiry was completed. Something like DateClosed for instance would work. You simply insert the date and your "current enquiry" form only displays enquiries that have no DateClosed.

Thanks Minty,

I know it's not good to reuse enquiry numbers and have tried to get this amended., but unfortunately met resistance.

Before my method below of keeping the data it was simply deleted, so at least we're moving forward.

I would seriously stamp my feet about it. I can't think of any sensible business reason to reuse a case / enquiry / whatever it is number. It defies logic.

I'd love to hear the rationale behind it.

I agree with Minty.
Please tell us why this is a business rule

Is there a way to automatically reinstate the enquiry number on the Customer Table?

Click to expand...

Just thinking of this in non-database terms, isn't the concept involved " a new enquiry"?
Why give it an old number/identifier? Just give it a new number.

If you want to do some sort of historical analysis of enquiries, do you have some categorization scheme (product/service/complaint,.....) and some enquiry date and resolution etc.
That allows you to answer:
How many enquiries since Date X, or between Date X and Date Y.
What is the most frequent enquiry category?
What are the relative frequencies of the various enquiry types/categories?
Which company(ies) submit the most enquiries?
Is there a pattern to enquiries that would make us change some process/technique?

I suggest you get a big picture view and see what is really required from a business and priorities perspective. I don't think reuse of enquiry numbers is even on the horizon.

Someone with some database design skills should be determining how best to implement a business requirement. Database design is rarely a process to be proclaimed by senior management whose focus should be "the business".