Full Version: Finding The Highest Of Two Dmax() Results
UtterAccess Forums > Microsoft® Access > Access Forms
spooker
I have built a system for a small component manufacturing company. Basically, the customers send the orders, which are recorded onto the system and given a unique Works Order No.
When the components have been made, the user selects the order number then selects which items on the order are ready (there may be several items which could be ready at different times) and then raises the invoice.
So invoices can only be raised against orders that have been entered onto the system.
There are two tables used for invoices "InvHeader" and "InvDetails"
The user has now requested a facility to raise invoices for "Ad Hoc" items where there has been no order recorded - basically a manually generated invoice.
Ocan get over this by using two new tables - say "ManInvHeader" and "ManInvDetails", but my problem is the user wants the invoices to be sequential whether the invoice is a normal system generated invoice or a manual version.
Currently the system uses DMax("InvoiceNo","InvHeader")+1 on the invoicing form to determine the next invoice number.
But now I'll need to look up the last invoice numbers from both the InvHeader and the ManInvHeader tables.
I imagine I need to have the Dmax() function for both tables and then see which is the highest and add 1 for the next invoice
Any pointers on how I could do this? I've tried running it in a query but can't seem to make any progress. <
Thanks in advance...
projecttoday
You could use a table to store the latest invoice number. Both of the invoice forms can read from the same table. This has the added advantage of working for multiple users (which Dmax doesn't).
MrSiezen
You shouldn't use two tables for your invoices, period. What if your user wants to know what his result is on a particular component? You would have to use union queries or code to calculate it, with a kinds of potential problems.
olve this by adding a field in the current table in which you can store if it was a manual invoice or
projecttoday
I agree you should consider using one table. But you should also consider getting rid of Dmax because it can result in bad data if more than one person is creating invoices.
Have a look at this custom counter.
spooker
Point taken, but a manufactured component will always be ordered & invoiced through the existing system which pulls the customer details from the customer table, products from the products table and details of the order from the orders table.
If the user wants to sell off say for example some surplus equipment to someone he has never sold to before and is unlikely to again, he doesn't want to set up customers, then set up the equipment as products for that customer then create an order just to raise one ad hoc invoice for a one off sale.
The current invoicing system is lead by the user selecting an order which then pull down all of the required data to process the invoice and it's going to be a major rewrite to change.
Looks like I need to get the grey matter back into gear....
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.