Invoice. Dirty fix or redo? (1 Viewer)

[jjatwork]

I'll try to be as brief as possible.

The client wants to be able to add/remove items from an invoice by clicking small 'plus/minus' buttons.

I've accomplished this by making one table which contains orderID and the name of *all* spare parts, then hooking up each pair of buttons on the form to this code:

Private Sub btnPartName1Plus_Click()
Me.PartName1 = Me.PartName1 + 1
End Sub
 
Private Sub btnPartName1Minus_Click()
Me.PartName1 = Me.PartName1 - 1
If Me.PartName1 < 0 Then Me.PartName1 = 0
End Sub

I have a feeling I'm doing something horribly wrong, though.

If my understanding is correct, the tables are not normalized in this way.
Looking at the Northwind template, a separate table with the spare parts info and one with invoice info would be preferable.

With that said, these are my 2 questions:

Is it possible to make several 'IF/IIF/WHERE' statements in SQL, so I'll only grab the entry if value is > 0?
(Basically to avoid printing all the parts, where the quantity is 0, on the invoice.)

How would I redo the tables and 'plus/minus' buttons if I want the data to be normalized? (I guess the plus should check if an entry exists, and create it if it doesn't or increment the quantity if it does, while the minus should find an entry and decrease the quantity by 1 or delete the entry if quantity = 0.)

I hope I've been able to explain the problem clearly.
Any and all advice is much appreciated! Even a nod in the right direction would help.

JJ

 

[Uncle Gizmo]

The earlier version of the Northwind sample from Microsoft had an excellent sample of an invoicing system. I'm not sure that the later version has the same thing? - Not really studied it! However if you do decide to go with the Northwind (the earlier version), then there are a few problems you should be aware of. I go through it in my blog here:- Northwind Problem and Solution

 

[Uncle Gizmo]

With regard to the title of your thread, fix it or redo, well that depends. Only you can make that decision. However I do know that an incorrectly setup database is just a minefield of problems waiting to happen. The more advanced you get with it the more you develop it the more often you will run into seemingly insurmountable problems. Problems which if the database has been constructed correctly in the first place would either not exist, or would be just a very simple fix.

From the point of view of keeping your job, you might be better off attempting a few simple fixes and working separately on creating a new system that follows the recommended MS Access design rules. This way you keep your boss happy, and you will probably learn why not to do it the wrong way! And there's no better way than actually doing it the wrong way to prove that to yourself. That's why you will get the advice from the more experienced members, (and when I say experience I mean we've all done it the wrong way!) That's why we say don't do it the wrong way! We've had our fingers burnt.

 

[jjatwork]

Tyvm Gizmo. I'll check it out right away.

The Northwind template uses a dropdown menu to add items to the invoice, which my client doesn't like.
No doubt I'll pick up a few useful things, though, so I appreciate the link.

JJ

EDIT: And thank you for the extended comment too.

 

[Uncle Gizmo]

By the way the sample northwind database I am referring to can be found on Bob fitz link here:-

NorthWind 2000.zip (2382 views)

 

[gemma-the-husky]

@OP

I don't think your explanation indicates anything unreasonable about your data.

I think you need to consider your invoice as a shopping basket. It's one thing to change the quantities on an "invoice/shopping basket". We have all seen web sites that allow the ordered items counts to be modified at shopping basket stage. But they don't add new items through the shopping basket. They just "continue shopping".

You could use the "plus" button to open the form used to select the items in the first place, and then update the shopping basket/invoice when you are ready. It shouldn't be too hard.

If you don't use a combo box to find items, how do you do it at present?


out of interest, why do you increment a field/control called "partname", and in particular "partname1" - which implies you also have "partname2", etc. This sounds inaccurate to me.

 

[jjatwork]

Hi Dave. Thank you for your reply.

Going by what you wrote, I'm even more certain that I'm doing this the wrong way.
To add some info, this DB is meant for a fleet of bikes and the invoice form is used for billing some of the repairs.

As it is, all parts are listed on the form and small '+/-' buttons are used to add the quantity used.
The 'InvoiceData' table then contains an InvoiceID, AssetID and the names of *all* the parts (PartName1, PartName2, etc.) and the '+/-' buttons just adds/subtracts from the value of the PartName where InvoiceID matches the one in current use on the form.

I'll try to mock up an example-db by tomorrow. Thanks again for your time.

 

[Uncle Gizmo]

all parts are listed on the form and small '+/-' buttons are used to add the quantity used.

Would I be correct in assuming that a 0 number would mean that a part has not been used and therefore will not appear on the invoice?

Or possibly shows up as a part but with zero as number used, therefore not chargeable?

 

[Solo712]

I don't see anything wrong with the table structurally, on what you have shown. But would it not be simpler to create text fields for the parts and enter (or correct) their quantity directly rather than clicking them up and down by one? Just sayin'.

Best,
Jiri

 

[jjatwork]

I think my question might have been very poorly worded and not thought through.
My problem was actually in printing the invoice, but even if I could apply the fix I had in mind, it would not be a good solution, as it doesn't allow me to extract any other information than the label and the value, which will present all sorts of problems down the road.
A separate table for parts would allow me to properly store price, description, stock etc..

I think I've been working backwards from a bad idea. I mocked up a quick visual layout, based on poor db architecture, and my supervisor liked the +/- buttons, so I've tried to keep them.

To clarify my original question and bring some closure to this mess.

Simply put:
I have all invoice info in 1 table, 'tblInvoiceData'.
I wanted to make a printable report/invoice based on a dynamic query.
I needed to select the first 4 columns and any other column with a value > 0.

This is somewhat the structure of the table I have used for invoice data:

CREATE TABLE tblInvoiceData (
    InvoiceID int,
    AssetID int,
    AssetDesription varchar (100),
    CustomerInfo varchar (200),
    PartName1 int,
    PartName2 int,
    PartName3 int,
    PartName4 int,
    etc...
    );

I have a form where the first 4 fields are imported at load and the rest is set up to be incremented
with small +/- buttons.

But. I think I need to redo this whole mess.

Would I be correct in assuming that a 0 number would mean that a part has not been used and therefore will not appear on the invoice?

Or possibly shows up as a part but with zero as number used, therefore not chargeable?

Correct. I might be using the word invoice incorrect. It is billing for repairs (parts/man hours) on assets (bikes).
You describe my problem more concise than I managed!
I've made a printable report, but am not able to hide the labels of all parts not selected.

But I think I'm following the wrong path.

I don't see anything wrong with the table structurally, on what you have shown. But would it not be simpler to create text fields for the parts and enter (or correct) their quantity directly rather than clicking them up and down by one? Just sayin'.

Best,
Jiri

Hi Jiri, thank you for your time.

The goal is to automate as much as possible and streamline the process for the user. The quantity is already displayed in an editable text field, the buttons are just an add on.
This allows mouse-only use, whereas textboxes requires min. 2 inputs (tab/mouseclick + input) and hand on the keyboard.
Most parts are only used in a quantity of 1 or 2, so in the long run, I'm hoping it will be handy.

 

[Mark_]

For myself, I'd have a separate table that holds off of the "Parts".
You could then use a continuous form to show all "Parts" and have in joined to a child of the invoice that holds the quantity for each part. This means that if you add new parts you don't have to redesign your form, you just add the new items.

You would then add a new "InvoiceItem" that holds the parts ID and quantity when you hit your "+" or update it if it already exists. Likewise you'd update the InvoiceItem when you press "-" by reducing the quantity, and delete the InvoiceItem if the quantity hits 0.

Keeping the individual parts in your table is very much spreadsheet thinking. It means any time there is a change you have to change your table definition and update all form/reports/queries to reflect your new structure.

Call me lazy, but I'd rather have an easy way to do this rather than hoping I can catch every instance of something. Also means I only need to be smart ONCE to get the continuous form to work right rather than have multiple instances of redundant code repeated for every instance of "PartName".

 

[The_Doc_Man]

JJ, when you have fields with name1, name2, name3 (i.e. field name contains a number for differentiation of fields), you IMMEDIATELY know you have a structural problem.

May I suggest some reading?

Look up "database normalization" using your favorite web browser (or simply "normalization" using this forum's SEARCH function). From the web you would get nearly a gazillion hits, so limit yourself to hits from an .EDU source.

Look up "parent/child tables" here or on the web. Your invoice/line-item lists will bear such a relationship.

In general, the first and most important part of DB design is to stop and immediately analyze "the problem" because whether you realize it or not, you are using Access to build a programming model of your business. If you are going to make a model, you need to know what will be in it at fairly detailed levels. Therefore, up-front study is pretty much mandatory.

 

[Solo712]

To clarify my original question and bring some closure to this mess.

Simply put:
I have all invoice info in 1 table, 'tblInvoiceData'.
I wanted to make a printable report/invoice based on a dynamic query.
I needed to select the first 4 columns and any other column with a value > 0.

This is somewhat the structure of the table I have used for invoice data:

CREATE TABLE tblInvoiceData (
    InvoiceID int,
    AssetID int,
    AssetDesription varchar (100),
    CustomerInfo varchar (200),
    PartName1 int,
    PartName2 int,
    PartName3 int,
    PartName4 int,
    etc...
    );

I have a form where the first 4 fields are imported at load and the rest is set up to be incremented
with small +/- buttons.

But. I think I need to redo this whole mess.

Now I see why you had qualms about structure. You would be much better off splitting the invoice into two tables: header and detail and and have customers and parts in separate tables also. There are scores of examples on the web how to set up invoicing in Access. You might want to google some out. Here is one: http://web.pdx.edu/~gerbing/325/Resources/Access.pdf

Best,
Jiri

 

[Uncle Gizmo]

I think my question might have been very poorly worded and not thought through.

Any question is good, even if it is not clear. Many times putting your problem into words will help you come to a solution yourself.

The other issue is knowing the MS Access specific terms to use, there's no way to know these if you're not that familiar with the product. So just get something out there, we can usually suss out what's going on!

Sent from my SM-G925F using Tapatalk

 

[jjatwork]

For myself, I'd have a separate table that holds off of the "Parts".
You could then use a continuous form to show all "Parts" and have in joined to a child of the invoice that holds the quantity for each part. This means that if you add new parts you don't have to redesign your form, you just add the new items.

You would then add a new "InvoiceItem" that holds the parts ID and quantity when you hit your "+" or update it if it already exists. Likewise you'd update the InvoiceItem when you press "-" by reducing the quantity, and delete the InvoiceItem if the quantity hits 0.

Keeping the individual parts in your table is very much spreadsheet thinking. It means any time there is a change you have to change your table definition and update all form/reports/queries to reflect your new structure.

Call me lazy, but I'd rather have an easy way to do this rather than hoping I can catch every instance of something. Also means I only need to be smart ONCE to get the continuous form to work right rather than have multiple instances of redundant code repeated for every instance of "PartName".

Hello Mark. Tyvm for your time.

This is by far my preferred choice! But I only have a vague idea about how the buttons should be programmed to function with such a table.
Which is why I focused on the dirty fix.

JJ, when you have fields with name1, name2, name3 (i.e. field name contains a number for differentiation of fields), you IMMEDIATELY know you have a structural problem.

May I suggest some reading?

Look up "database normalization" using your favorite web browser (or simply "normalization" using this forum's SEARCH function). From the web you would get nearly a gazillion hits, so limit yourself to hits from an .EDU source.

Look up "parent/child tables" here or on the web. Your invoice/line-item lists will bear such a relationship.

In general, the first and most important part of DB design is to stop and immediately analyze "the problem" because whether you realize it or not, you are using Access to build a programming model of your business. If you are going to make a model, you need to know what will be in it at fairly detailed levels. Therefore, up-front study is pretty much mandatory.

Hi Doc.

The names are just placeholders. Real labels are 'Brakepads, Handles, Frontlight, etc.'
But it's still the wrong way.

I have a decent understanding of normalization, but I've had to learn many new concepts in a short time, so it's all been a bit overwhelming.
I'll go back and read up on parent/child relations.
I've been through a 190-page base tutorial pdf a few times. (No links allowed for me)

Part of the problem is that my supervisor isn't even sure what the db needs to do.
It started as a simple 'lookup/edit/delete' form, but now it needs to track repair-notes and invoices.
I believe it's called feature-creep, but I'm not gonna say 'no can do'.

Thanks for the advice. It reinforces my belief that the dirty fix was the wrong way to go.

Now I see why you had qualms about structure. You would be much better off splitting the invoice into two tables: header and detail and and have customers and parts in separate tables also. There are scores of examples on the web how to set up invoicing in Access. You might want to google some out. Here is one: (snip)

Best,
Jiri

Tyvm for the link. I will have a look at it once I've read up on normalization.

Any question is good, even if it is not clear. Many times putting your problem into words will help you come to a solution yourself.

The other issue is knowing the MS Access specific terms to use, there's no way to know these if you're not that familiar with the product. So just get something out there, we can usually suss out what's going on!

Yes, expressing the problem often helps. I just often feel like I'd be wasting people's time and therefore refrain from asking.

Thank you so much for your understanding.

 

[The_Doc_Man]

Part of the problem is that my supervisor isn't even sure what the db needs to do.

Yes, that IS part of the problem. The BIGGEST part of the problem. And yes, the term "feature creep" is WELL-known in Access circles and other development circles as well.

"Feature creep" happened to me when I started working as a U.S.Navy contractor in the 1990s. We had a personnel database that was an upgrade from a home-grown data management program. The original program could NEVER in the most generous stretch of one's imagination have been called a database. At best it was a flat-file diddler, but when we formalized it into a relational DB, performance and work capacity jumped by a factor of 8- to 10-fold in a matter of months. EVERYONE loved the new system and its incredible responsiveness.

So OF COURSE now that the Navy brass saw they could get more out of it, they DEMANDED more out of it. In a matter of four years, we had outgrown two different dedicated back-end servers that were just an OS and an RDBMS, nothing else including no direct user logins. The FE was "trusted" to get the login right. In essence, a truly stand-alone back-end processor.

The next time we outgrew what we had, in a matter of only about three years, it was necessary to get a multi-processor, multiple-box situation using clustering and shared network attached storage to keep up with the load. And there was STILL a backlog of admirals, captains, and commanders who wanted us to build even MORE customized reports for them. The "creep" was more than 10-fold in any dimension of growth you wanted to consider, with the possible exception of the mental acumen of the user base. That was more or less stagnant.

Just remember that success bears its own rewards - AND its own penalties. Good luck, because your first and foremost goal here HAS to be to get the supervisor(s) to agree on what is actually needed and maybe make a prioritized list of necessary and desired features. Without SOMETHING to go on (and get it in writing), you are going to be SO lost...

 

[Mark_]

I'll second the Doc.

More to the point, if you start talking to your supervisor(s) about what they need you can get THEM to start telling you what is/isn't important and what they are looking for in the end.

Just get them a gentle nudge by saying "Well, if you don't tell me BEFORE I start what you want, it will take far longer to redo it and I won't always be able to support new features for older records. I also want to make sure you talk to who ever does the books BEFORE we start putting in anything that has a monetary amount attached so I can get them to bless off on what we put in. After all, I don't want any of you to get in trouble if we don't put the right taxes or something on a bill".

Let them know their time is important and you don't want to waste it by having them change things to often. Also make sure they are the ones who will be liable for anything they ask the system to do. And like Doc said, get it in writing. This should be a "Hey, so I can make sure I'm clear on what you want, can you Email it to me? I don't want to keep bugging you for little stuff"...

 

[Uncle Gizmo]

>>>Part of the problem is that my supervisor isn't even sure what the db needs to do<<<

I can't really go into details but I was doing a job for a guy who was sort of my equal and superior. If that makes sense! And he left, and nobody else was interested in the project at all! So, what am I trying to say, is what you are doing for the benefit of your superior, your colleague, or is it of benefit to the business? You need to be quite sure about that!

 

[Mark_]

JJ,

Before I do any more work on this, please look at the attached and let me know if this is a basic approach that will work for your needs.

Concept is you show all products and the quantity relevant for each invoice. If this is something that would work for you I can then toss on the +/- buttons.

 

[jjatwork]

First off:
I think the answer to the OP is to redo the mess.
If any mod wants to mark the thread solved and/or closed, feel free.

(...) Just remember that success bears its own rewards - AND its own penalties. Good luck, because your first and foremost goal here HAS to be to get the supervisor(s) to agree on what is actually needed and maybe make a prioritized list of necessary and desired features. Without SOMETHING to go on (and get it in writing), you are going to be SO lost...

Thanks for the advice.
I always find it inspiring to hear stories from those who have experienced the evolution of programming first hand.

I'm already a bit lost. This journey started with me hearing about an opening and mocking up a simple 'LOOKUP/EDIT/DELETE' mySQL db with a terminal-based UI in python, with a few details pr. bike, to an open source DB program and then "Hey. We got MS Office, can we use Access?".

I'm not without hope thought. Just have to do some more reading and testing.
It's very much a 'figure it out when we get there' situation, so it's hard for me to get much in writing.
I don't have the experience to plan out the small details in advance, so many changes has been oriented towards ease of use, automating and extending already implemented features.

I will try to make more lists though. I've not been good at keeping track of features and functions.
I guess this is what's called project management?

I'll second the Doc.

More to the point, if you start talking to your supervisor(s) about what they need you can get THEM to start telling you what is/isn't important and what they are looking for in the end.

Just get them a gentle nudge by saying "Well, if you don't tell me BEFORE I start what you want, it will take far longer to redo it and I won't always be able to support new features for older records. I also want to make sure you talk to who ever does the books BEFORE we start putting in anything that has a monetary amount attached so I can get them to bless off on what we put in. After all, I don't want any of you to get in trouble if we don't put the right taxes or something on a bill".

Let them know their time is important and you don't want to waste it by having them change things to often. Also make sure they are the ones who will be liable for anything they ask the system to do. And like Doc said, get it in writing. This should be a "Hey, so I can make sure I'm clear on what you want, can you Email it to me? I don't want to keep bugging you for little stuff"...

JJ,

Before I do any more work on this, please look at the attached and let me know if this is a basic approach that will work for your needs.

Concept is you show all products and the quantity relevant for each invoice. If this is something that would work for you I can then toss on the +/- buttons.

I have tried the nudging, but I don't think I'm good at making it clear exactly *how* much work it will take to correct some issues.

The points about liabilty and getting him to send an email is brilliant, though.
That should greatly increase my chance of getting *something* in writing.

Thank you. I'll check out the db first chance I get. I share a workstation, so I can only do research and some basic testing on Tue/Wed.
I don't think it's worth following this path any longer, though.
The list has grown from 15-20 parts to 40-50, which suddenly makes it much less manegable to have them all displayed in the UI. Additionally, this method is not very scalable and I suspect that it might be needed at some point.

I have to comepletely redo my ideas for the UI. I think I need to divide the parts into sub-categories to make selection easy.
Thanks again for your time, but you shouldn't waste any more on a problem that isn't even properly defined.
I'll try again with a more specific question, when I have nailed down how the structure should be and the UI should function.

I can't really go into details but I was doing a job for a guy who was sort of my equal and superior. If that makes sense! And he left, and nobody else was interested in the project at all! So, what am I trying to say, is what you are doing for the benefit of your superior, your colleague, or is it of benefit to the business? You need to be quite sure about that!

Thanks for the insight, I hadn't thought along those lines. To be brutally honest, I'm doing this for myself. I need to find somewhat steady employment, so I need to build a skillset and a CV. (Or is it called portfolio?)

As I see it, I need to present a compelling solution to my supervisor, so he is able to defend the cost of me finishing the project and migrating all the data.