Complicated Invoice tracking.

[Homegrownandy]

Scenario:


We have four orders:

1-75 jobs
2-100 jobs
3-73 jobs
4-200 jobs

But we can be working on all of the orders at the same time. The period of time for this work to be complete is years so we cant wait for payment.

So for example in a week we do 5 jobs from each order. That's 20 jobs we have done.

Problem:
our client wants to use the budget in sequential order. They want to pay for all of order 1 before they pay for any of 2. (no matter what the work is or what order the work is in)

Current System:
I press a button that will generate a report for the costs and once those costs/jobs have been put in the report, the same button does an update query to add todays (whatever day that day is) date to "invoiced date" for that job.


What I need:
I need to track how many jobs this 'current system' has invoiced. Then I need to make sure it doesn't exceed order 1.
When it reaches 75 I need to use the order number from invoice 2.

This has to be fool proof, I cant rely on temperamental code. (referring to my own code haha)

Ideas:

Please criticise or add to any ideas I have.

* add another event to the macro that generates the report and updates.
This macro would run BEFORE the update macro and do a count...

but with that information I'm really confused as to what to do. Or how to make the system deal with it.

All questions welcome.

[June7]

The accounting systems I am familiar with don't associate payments with specific orders. Invoice and payment transactions are associated with account, the net difference is balance on account.

I say let the customer keep track of what order they want to associate payment against in their database - instead of wrecking your time and materials cost accounting.

Otherwise, you have a significant challenge to overcome.

[ItsMe]

I do not understand the idea of the different orders. I imagine being able to sort by date and all 'orders' would be clumped together. If you need to sort by orders, then I suppose a separate query would retrieve orders with a status of 'Active'.

[bigot]

I'd have to see a database diagram to help out here. But some suggestions to get you going:


Are jobs tracked in a separate table?
If a job is completed, is there a "jobCompleted" boolean column that tracks that?
Are you only doing 1 job at a time? (you won't do 4 jobs when 73/75 are complete, and overflow into 77/75)
If yes to all then it's super simple (untested query):

SELECT TOP 1 jobID (select other columns if you want)
FROM tblJobs
JOIN tblOrders on tblJobs.FK = tblOrders.PK (join other columns if you want)
WHERE jobCompleted=FALSE
ORDER BY tblOrders.orderNum ASC, tblJobs.jobNum ASC

I'm basing that query on absolutely nothing, lol. Please provide some table structures so I can help better.


EDIT: Do you provide a job number on the invoices (for example "order #1 job #1", "order #1, job #2", etc.)? Or do you just bill for unidentified jobs (example "order #1 job", "order #1 job", etc.), and don't need to identify them?

[Homegrownandy]

Itsme:

the orders are for work in x amount of locations (75 in order one). Those locations are defined at the very start.... BUT I cant assign the locations to the job even though they should be.

Our client has a cost structure they need to keep to and that means we need to get the funds sequentially from orders. So I know now the orders we do have are useless and Im basically working backwards pushing jobs in orders just so we are paid.

June:
we do have to track this ourselves but you made some good points there.

Bigot:
yes, yes and no. haha.

Thanks to some thinking and reading your posts, I'm tempted to leave this to the accounting department.

My concern was to see what's been invoiced, what we need to invoice, and what payments need chasing. But If I just track invoicing, I can pass the rest on for someone to manually do it.

However, Its still interesting to me if this problem can be over come. I may come back to this when I have a bit less 'Work'.

I could probably use auto number on a new table to pull everything <76 in one report and they only get added to that table once they are complete.. something like that.

[June7]

Your requirement reminds me of a FIFO inventory management system. I have seen db designed to deal with that.

[bigot]

Bigot:
yes, yes and no. haha.

Ok, in that case, the "no" is your only problem. Solve that by processing one job at a time using my query. Problem solved.

Also if it's just this one client, I wouldn't bother changing up my entire system for them. If the costs are fixed, can you pre-prepare all the invoices, then send it to them as jobs get done? (Lol what a bad idea)

[Homegrownandy]

Ok, in that case, the "no" is your only problem. Solve that by processing one job at a time using my query. Problem solved.

Also if it's just this one client, I wouldn't bother changing up my entire system for them. If the costs are fixed, can you pre-prepare all the invoices, then send it to them as jobs get done? (Lol what a bad idea)

totally agree, but they are about half of our business. My current solution is to pass all the data to the accounts department and they manually solve it. Im fairly happy with that. haha.

thanks for the sugestions/time

[June7]

Seems most appropriate solution to me. Accounting should be issuing and tracking invoices.