I have a question on the Access Work Order,  I have Residental and commerical customer I would like to be able to sort by each so that they are in ABC Order..  Like John Abney and Abney Construction... how do I do that?  and I need a way to list phone number one customer might have 5 different phone numbers Wife might have a cell & work number same for the husband how do you suggest fixing a table for that so that you can determine who/what phone # it is for. Thanks

---

Reply from Richard Rost:

As far as mixing residential and commercial customers, there are many ways to do this. I personally prefer to make a query that selects the company name IF one exists, otherwise it selects LastName, FirstName. Use something like this in your query:

MyName: IIF(IsNull(CompanyName), LastName & ", " & FirstName, CompanyName)

Now you can use that MyName field in your combo boxes and such where you have to choose a customer.

As far as multiple phone numbers go, you COULD just have 3 or more phone number fields in your customer table. However, nowadays you've got families with 6 or 7 different numbers. Everyone's got a cell phone, some have landlines, fax numbers, etc.

So, you IDEALLY should create a second table to store phone number information:

PhoneID (AutoNumber)
Phone (Text)
Type (ID linked to another table: mobile, home, fax, etc.)

Then you can either make this a subform on your customer form OR a listbox to just display them all.

Have fun.