Work DBase - I have created all customer and job tables and everything works well.
However, every couple of weeks I will get a Purchase Order Number and need to invoice the jobs, some jobs are on single PO No.s/invoices, some jobs are all grouped on one PO No.s/invoice. I can't for the life of me think how to plan/design this, an append query ? group query ?.
Any guidance would be REALLY helpful, as I've got to get this done by Monday !!
Thanks
This conversation is currently closed to new comments.
The poper way to do it would be an OrderLines (and maybe InvoiceLines). Then you'd link job to one of them. an order/invoice for one job, would simply have one line. You could do with surrogate keys to carry this off properly.
The quick and dirty way, while you do it proper would be a link table
JobNo, OrderNo/InvoiceNo keyed by both columns, so marking a job as invoiced would just be inserting a record in there to make the link.
- Customer Table - Job Table with Customer code linking them both- - Invoice Table (invoice no. and PONo)
If each job had a seperate invoice than no problems, I could do that, it's the group invoicing thats the problem.
It's when several jobs have one invoice number.
I tried/my idea was - a query grouped by company and PONo. assigned to a split/datasheet form, with an open button, when I press open a new form showing the Cp and PONo. plus the next availble invoice number from the invocie table, then a sub form showing the jobs with that PONo.
Didn't work !
Another idea was to list all invoices for a company, select the jobs/PONos I want, press create invoice, which gives me another form with the next invoice no. and detail of the jobs chosen. But I have no idea where to start with this !
If multiple jobs are under one invoice number, yet the jobs can come up individually, why not ID the jobs themselves? Even if it is a <i>-N</i> appended to the "parent" invoice number.
was my first suggestion, as it's minimum impact and the OP needs to do this by tomorrow.. If this is a reconcile operation, I'd be tempted to go down that route anyway, even if invoicelines or PurchaseOrderLines existed, the link tabel would be JobNo and the surrogate key or either of them. I'm not a big fan of the select * From Jobs where Invoice_no is null approach, index would have to be nonclustered, whereas a link table would be a straight join between two or three clustered indexes.
Mind you whether it's a performance improvement in access, I've no idea to be honest...
Do it properly then cabbage it if you have to is my approach in db design.
Besides you get the option of multiple invoices for one job as well should it become desirable without a huge schema change overhead implementing it.
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Help with Invoicing in Microsoft Access
However, every couple of weeks I will get a Purchase Order Number and need to invoice the jobs, some jobs are on single PO No.s/invoices, some jobs are all grouped on one PO No.s/invoice. I can't for the life of me think how to plan/design this, an append query ? group query ?.
Any guidance would be REALLY helpful, as I've got to get this done by Monday !!
Thanks