Trying to create an Invoicing/POS DB with Access 2010. Needing some advice...

[agrendell]

This will be a bit long. I want to be as thorough as possible...

Some friends and I have recently opened a PC/Phone repair shop in my hometown. We started with little money, and have done quite well to get the business off the ground without too much trouble. But we need to find a way to change the way we take in our sales. We are currently using a POS company that uses the Groov POS software. It's absolutely perfect for our retail, and for our parts and labor. But when it comes to doing commercial work for other businesses, it comes up short because it doesn't allow us to do any invoicing or saved billing. It's basically a good way to keep up with inventory and sales tax. We can pay them to do some customization on the program, but there is where the problem is. We don't have the funds to pay someone to edit the program, and we're locked into a contract with them for 2 years, so changing programs is out of the question. Which is ultimately what leads me here. I need (or at least I think I do, and if I'm wrong, please tell me what I DO need) to create a basic POS db with Access. Something that can allow us to bill a client, showing detail of work performed, and keep up with whether or not the bill has been paid, or paid partially.

I would upload where I've gotten so far, but, to be honest, I'm hung up on one very important part of the beginning structure (this is where I need some good advice). I know there should be a customer table where I keep up with clients, and I want there to be a Sales and Services table, where I can keep a record of all services that we perform, as well as the items that we sell. The part that I'm unsure of is whether or not there's a way to create an invoice table that can have one field with multiple entries (Sales and Services). I'll try to word out an example:

Customer Name: John Doe
Date of Invoice: 5-31-2015
Invoice #: 100001


Sales and Services: CAT5 wiring from Demarc to outlet; Installation of PC; Configuration of Router; Configure Network Printer; etc. etc. etc.

Each semicolon represents a new item. And each one of those items would be followed with the fields Unit (for quantity) and Rate (for price), ending with an Amount field (unit x rate).

My question is: Is there a way that I can create something like that in just one table called Invoices, or do I need to create and name a new table for each individual invoice with some sort of SQL line?

I apologize if this is just too jumbled up to understand but it's the best I could manage without a solid example. If it's absolutely necessary I'll try to get that to whoever is interested in giving me some friendly advice.

Thank you very much in advance,

Adam

[CJ_London]

As a minimum, you need 3 tables one each for customers, invoice headers and invoice details. You may also need tables for Sales Tax/VAT, products, different customer addresses etc

tblCustomers
CustomerPK autonumber
CustomerName text
CustomerAddress text
...
...


tblInvHeader
InvHeaderPK autonumber
CustomerFK Long - links to customer
InvDate Date
InvNumber Long
...
...

tblInvLine
InvLInePK autonumber
InvHeaderFK long - links to header
LineDetail text
LineQuantity Long
LinePrice Currency

and perhaps also
ProductFK
LineValue
TaxAmount

depending on how far you need to go

In a form, have tblInvHeader as the recordsource. Note the control for CustomerFK would be a combobox to assign a customer from tblCustomers
in the same form, also have a subform with a recordsource of tblInvLine

[agrendell]

Thanks so much for the quick reply. That is quite literally the exact information I was needing. I knew all the parts were there, I just didn't know exactly how to lay them out. Short, sweet, and exactly to the point. Thumbs up, sir. Thanks again! I'll give an update on what I've gotten accomplished in the next day or two.

[CJ_London]

You will need to give some thought to InvNumber. It is not recommended that you use the autonumber - which is only 'guaranteed' to be unique per record. You cannot fill in gaps for example, and credit notes (which should be in the same invheader table but you'll need another field (invType). usually have their own number 'range'. Recommend you investigate what is known as DMax()+1 - google this to find out more - one of the benefits is you can give each customer their own unique number - can make them feel more valued.

Otherwise recommend you start your numbering from say 1000 or even 1541 or similar - no need to point out to your customer that they are the first one with invoice no 1!

[agrendell]

Ok, well I got the DB set up the way you mentioned. Now my next challenge is finding a way to link the tables. My biggest hangup is on the tblInvHeader, getting the InvHeader field to automatically fill the InvHeaderFK field. I have a few other problems, but I'm 99% certain that if I can get that figured out the rest will fall into place. I've created relationships between all the tables, but I'm not seeing it make any difference as of yet.

Is there something really simple that I'm just overlooking here?

[CJ_London]

My biggest hangup is on the tblInvHeader, getting the InvHeader field to automatically fill the InvHeaderFK field.

you shouldn't have a InvHeaderFK field in tblInvHeader - it is invHeaderPK and type autonumber. (PK means Primary Key, FK means Family Key)