Filtering and counting...

[Chrisopia]

I just stubmbled across a problem I have, which I'm sure has a simple solution, but I cant think of anyway to do it? (DMAX+1 comes to mind perhaps?)

I have an orders table, and you select an order type:
either Invoice
or Quotes
(Where ordertype=1 for invoice and 2 for quote)

At some point a quote becomes an invoice, but sometimes they dont.

Now I was going to use the OrderID (primary key) as the Invoice Number, but then realised that even the Quotes would have numbers, Quotes don't need numbers, only the Invoices. (Ordertype=1)

So when a quote isn't turned into an invoice, suddenly there is one less invoice
(e.g. if OrderID: 3 is a quote that gets rejected then we only have invoice 1,2,4,5 etc...)

 

[DCrake]

In theory you should not have holes in your invoice numbering system as auditors tend to think of fraud or the like. Every number should be accountable for, therefore I would be inclined to have a quotation table and an invoice table. Give all quotations a unique number then when it becomes an invoice append the quotation to the invoice table with its own unique invoice number. You should also retain the original quotation as this may be referred to at a later stage. Your invoice table should also record the quotation number for traceability reasons. If a quotation is rejected then simply flag that as being rejected, hopefully this wont be many so it will not clog up your system. Anyway they will allow you to analyse how many quotations are actually rejected.

David

 

[namliam]

So what?? A quote is simple an invoice that has not materialized... why worry about a lost key (or 2)

 

[Chrisopia]

DCrake, that sounds quite complex yet simple?
The OrderID, is connected to Orderdetails ID, finishing ID and Depatment ID... I would therefore have to create it for invoice and one for quote...
Plus I wasn't aware of how to append tables to another table?

but your way of doing this does make sense.

namliam... invoices will be printed off and ordered for admin reasons... heard of WYP?
White is for the customer, Yellow is for the admin and accountant ordered by "paid date" and Pink is VAT ordered by "invoice number". A missing number could imply a missing invoice... or was it a quote... who knows?!

See I was thinking of like count or DMAX or something - Count how many invoices there is "Count(Ordertype=1)" or DMAC(ordertype=1) then add 1 to it perhaps?

Im just not sure how to use DMAX or Count properly in this case?

 

[namliam]

Why not 'simply' make an invoice table with an autonumber?? This would then refer back to the quote as beeing the 'source' using a simple FK construct.

That way you can also keep track of when it was promoted Quote> Invoice etc...

And no, I have never heard of WYP

 

[DCrake]

HowTo use the DMax or DCount in access you do the following

Press Ctrl+G to open up the immediate window

Type in DMax
Highlight the word DMaz
then press F1 for help
Do the same with DCount

in reality is called RTFM

sorry having a bad day

 

[Chrisopia]

Invoice table with Autonumber... interesting....

How would I import the data from other sources?

 

[Rabbie]

Invoice table with Autonumber... interesting....

How would I import the data from other sources?

I originally used autonumber to generate Invoice numbers. This works Ok most of the time but you cannot guarantee the numbers will be in strict sequence. Autonumber only guarantees unique numbers. I use an expression like this to generate the next number

Dim NextInvoiceNo as Long
NextInvoiceNo = DMAX("InvoiceNo","tblInvoices")+1

hope this helps

 

[Chrisopia]

Good point Rabbie,

Through all this random testing with Dummie entries, Im already upto OrderID5... even when I delete previous entries.

I shall try that out ASAP!