Need help with this Query

[JRCharlie]

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.
Here is what I have so far.

SELECT JobName_tbl.JobID, JobName_tbl.JobAddress, JobName_tbl.JobCity, JobName_tbl.JobCivic,  [JobID]+1000 AS Invoice_Num
FROM JobName_tbl;

Thank you

[DJDJDJDJ]

I think you will run into long-term issue with this method. It is an example of poor table planning. What happens if first line of address is "123 Main St." but city, state etc. are different? Now you have two different customers linked.

A better approach might be to create customers with customer ID and then link Invoices to the customer IDs in a seperate table.

Simple example is:
Table One have customer information and an auto number for Customer ID
Table two has all of the invoices for all customers. Invoice ID is an auto number. This table also contain Customer ID of the customer that each invoice belongs to.

HTH

[JRCharlie]

Thanks DJ for your reply.
I do have a Jobname_tbl (job Addresses) and Entries_tbl (all the services).
The entries_tbl Contains Jobname_tbl ID
The Jobname_tbl includes the following columns JodId,JoDate,JobStreet,JobCity
The Entries_tbl Contains the following columns EntryID,JobID Services,Cost

I modified the previous sql and still need some help to add -2 -3 and so on

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;

[DJDJDJDJ]

I am still a bit confused regarding why would you want to keep the first invoice number attached to every single invoice for the same address.

One of the approaches I can think of is to simply have the invoice number increment for every invoice. When you need the fine these invoice, you find all invoices that match the customer number you are looking for.

You selection query could be sometihng like this:

select cust.*, invoice.*
from cust, invoice
where invoice.cus_id = 12345
sort by invoice.id


HTH

[JRCharlie]

Thanks DJ I have it working