Need some guidance on progressive billing please

[Gina Maylone]

I'm working on a proposal/job/invoicing db (Access 16). Some jobs require progressive billing (beginning, middle, end) and that is set up at the time of the proposal.


My invoice numbers need to be formatted like: 220330-01, I want to append the payment number on there as such: 220330-01-P1 (or P2 or P3), I would like that number to be saved as the invoice number. Right now I have a SUPER CLUNKY event, that runs queries and sometimes opens the correct invoice and sometimes doesn't. And sometimes I end up with an invoice number like: 220330-01-P1-P2. If anyone has any advice for a good way to do this, I would be eternally grateful as always! I hope you're all having a great morning!

[mike60smart]

Hi Gina

Can you upload a copy of the database without any confidential data>

[Gina Maylone]

Hi Gina

Can you upload a copy of the database without any confidential data>

Sure, hopefully I didn't screw it up any worse.fivestarDatabase1.zip

[mike60smart]

Hi Gina

I took a look at the database and a little puzzled by your Relationships as shown in the attached.

The Joins between your tables Jobs, Job Detail, Bills and Vendors show as Indeterminate ?

Also when trying to Join Customers to Bids using CustomerID it returns the error as shown in the attached.

This indicates that you have records in the Bids table without a Parent Record in Customers.

[Gina Maylone]

First of all, thank you for taking the time to look at this. I figured I screwed something up by changing/deleting confidential data. I know my relationships are screwy. I need to tighten everything up, I just need some direction on the progressive billing. I'm doing mental gymnastics with it. (On a personal note, I recently had a stroke and I've been having a hard time with my logical thinking lately.) I've written databases for over 30 years, many for exactly this same purpose. Yet I can't pull all of my thoughts together on this. Anyway, again, thank you for taking the time. I will fix my relationships now. :-)

[Micron]

Hmm, that looks all wrong to me.
- You're going to repeat all the customer info for each job. There should be a customer table - unless it's just missing from the pic.
JobID should be joined to JobID.Bills
- PK fields should be autonumbers whose values link to foreign key fields in related tables - too many unnecessary composite fields
- Jobs table is designed like a spreadsheet with repeating like fields (1st payment, 2nd payment, etc.). Whether or not you'll ever actually need a 4th payment field the litmus test is IF it should happen and you have to add a field to accommodate like data, then the design is wrong.
- Description is a reserved word.
- JobDescription as a pk is questionable. Could never repeat the same job and give it the same description. Indexed fields with a bunch of (basically) non-critical verbiage make for poor indexes IMO.
Will stop there - except to say that subject of db normalization would be a good thing to investigate & learn about.

EDIT
Just saw your last post. Sorry to learn of your issue. Part of your frustration is the design - you have made your goal more difficult. Probably there are templates you could look at, or look here for a schema that might fit your needs.

http://www.databaseanswers.org/data_...all_models.htm

[Gina Maylone]

Hi Micron,

Of course, there is a customer table Thanks for the heads up on "description", I will change that. As always thanks for the advice.

[Micron]

You're always welcome. FWIW, I did download after posting (curiosity got the better of me). The rest of the pointers are more important than use of the word Description.
You really should make the autonumber fields the PK fields, and the ones you have as PK now could be indexed, but the autonumber values should be what you use as related fields. Biggest issue IMHO might just be the repeating fields.

[CJ_London]

My invoice numbers need to be formatted like: 220330-01

why? I'm guessing that is a date format yymmdd + a count of number to date raised on the day.

As far as P1,2 3 is concerned you would need to do a count of something that relates to the customer? a project? something else? So if P1 is raised on 30th March 2022 and the first one of the day and P2 is raised on 25th April 2022 and is the 5th one of the day what would the number be required for each one?

220330-01-P1 and 220330-01-P2

or

220330-01-P1 and 220425-05-P2

or something else?

seems to me you are building a complex code for an invoice number. why not just have an invoice number and additional fields as required to indicate the order they were generated on the day and which stage of billing.

You haven't explained how the app knows which payment it is, user input? count of previous payments? something else? How do you verify it is the right payment number (i.e. customer has two orders so there are two invoice 'chains', how do you identify which chain the invoice relates to? customer order number?). What happens if subsequently an invoice is changed/deleted/credited (in whole or in part).

With regards your bids table, what happens if the customer only wants a single invoice or you agree to 4 payments rather than 3?

I can make some educated guesses based on the relationships but with no guarantee of being correct. You really need to get those sorted first before going any further.

[Gina Maylone]

Gotcha! Thanks!

[Gina Maylone]

why? I'm guessing that is a date format yymmdd + a count of number to date raised on the day.Yes, exactly, and that's how he wants them formatted.

As far as P1,2 3 is concerned you would need to do a count of something that relates to the customer? a project? something else? So if P1 is raised on 30th March 2022 and the first one of the day and P2 is raised on 25th April 2022 and is the 5th one of the day what would the number be required for each one? When the bid is put together, the due dates of the various payments will be listed, so they show up on the bid. Then they show up in the job (if the bid is accepted) and then through the invoice form, they will be billed. They will fall in line with whatever else is billed that day, so if the due date is April 15th, and we've billed 2 other jobs that day, the invoice number would be 220415-03-P2. Just like you said.

220330-01-P1 and 220330-01-P2

or

220330-01-P1 and 220425-05-P2

or something else?

seems to me you are building a complex code for an invoice number. why not just have an invoice number and additional fields as required to indicate the order they were generated on the day and which stage of billing. You are absolutely correct. I guess I am challenging myself. I am not following what you are suggesting. Sorry.

You haven't explained how the app knows which payment it is, user input? count of previous payments? something else? How do you verify it is the right payment number (i.e. customer has two orders so there are two invoice 'chains', how do you identify which chain the invoice relates to? customer order number?). What happens if subsequently an invoice is changed/deleted/credited (in whole or in part). Right now, I have a Payment Schedule table (related to the bid) with the due date and amount. I wrote a query to show me what payments are coming due so I remember to invoice them. It would be easier for me to just modify the invoice number to include the P1, P2, P3, I know.

With regards your bids table, what happens if the customer only wants a single invoice or you agree to 4 payments rather than 3?Then I'll just bill them once. The "scheduled payments" is an option.

I can make some educated guesses based on the relationships but with no guarantee of being correct. You really need to get those sorted first before going any further.

​Agreed, Ajax. Thank you for your time and advice!