Web Development

Question

Gravatar
Locked

Help with Invoicing in Microsoft Access

By tbarnacle ·
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.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

Might take a bit longer than that

by Tony Hopkinson In reply to Help with Invoicing in Mi ...

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.

gravatar
Collapse -

More info

by tbarnacle In reply to Might take a bit longer t ...

Thanks for replying.

At the moment I have a very basic DBase

- 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 !

gravatar
Collapse -

Confused - even more info needed

by Tony Hopkinson In reply to More info

List all the invoices for a company
select some jobs somehow
Create an Invoice ??????

Doesn't make sense.

How do you 'know' which jobs go with which invoice?

gravatar
Collapse -

I'd add a "job number" then.

by seanferd In reply to Confused - even more info ...

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.

gravatar
Collapse -

Link table for jobno and invoiceno

by Tony Hopkinson In reply to I'd add a "job number" th ...

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.

Back to Web Development Forum

Start or search

Related Discussions

Related Forums