Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    26

    Unanswered: Creating invoice#

    Hi
    I have a Sql that creates Invoice numbers.
    The following query works it adds 1000+id no problem.
    But what I really would like is when an address is duplicate the invoice number would be the same as the first entry plus I would like to add -2 .
    for example the first address "12 huck st" would get invoice number 1000 when
    additional services are done to the same address the new invoice would become
    1000-2 and so on.
    Here is what I have so far

    Code:
    SELECT JobName_tbl.JobAddress, JobName_tbl.JobCity, JobName_tbl.JobCivic, JobName_tbl.JobBuilder, [JobEntries_tbl.JobID]+1000 AS Order_Num, JobEntries_tbl.[JobID]
    FROM JobName_tbl LEFT JOIN JobEntries_tbl ON JobName_tbl.JobID = JobEntries_tbl.JobID;
    VIPER

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so write a function called say GetInvoiceNumber
    pass it whatever it needs to find an invoice
    if it finds an invoice add a "-" and +1 to the end of the current invoice
    if not create a new invoice
    if you create a new invoice number and you are in a multi user environment (ie more than one person could create a new invoice at the same time then you may need to consider table / record locks
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    26
    thanks healdem

    That was my next choice.
    Since I'm new to creating SQL's I was wondering if it would be possible creating a QUERY in acceess to get the same results.
    VIPER

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    you still have to 'ask' the db to find the next number. whether thats through you writing the SQL yourself or using an Access macro/function such as DMAX (which I'm absolutely certain behind the scenes is 'writing' the SQL for you).

    the reason behind the function is that it encapsulates a piece of behaviour. if its a function and you require the same behaviour in this or another project then its a simple matter of exporting the function, rather than trying to track down the SQL. if you build a module with these sort of common(ish) functions then you have a portable library which you can use elsewhere.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    And use the dcount+1 Count the number of invoice that the system has created

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I guess the main problem you are going to have is consistency of the address. 12 North Street isnt the same as 12 North St or 12 North St. or any other variations let alone capitalisation and or mispelling.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •