Invoice. Dirty fix or redo?

[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

 

[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.

 

[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.

 

[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

 

[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"...

 

[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.

 

[Mark_]

JJ,

No problem. It is actually a sample I've been wanting to do for a while. That was about a half hour to set up. I'll be doing a more detailed version soon.

It is geared so you can extend your "Parts" quickly by simply adding more records. Putting in "Categories" is a good idea for my demo, and I'll probably be adding that in the next few days.

 

[The_Doc_Man]

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

Actually, though not identical, it was similar for me. I was doing mainframe work and my boss told me about an Access DB being used to track computer assets. At the time, we were in the 600-700 system count and a fellow named Gene (not known to this forum) had cobbled together something to track what we had and build reports. I had done some formal DB study on the mainframe using an ORACLE system that was our BE package and a mainframe-based FE package called SmartStar. The boss said he wanted someone to back up Gene because he was floundering.

Like you, I was aware that the more you did for the boss, the more he would do for you when review-time came around. So I took over and started fixing a GOD AWFUL MESS of a database for which the term "relational" applied only because the description on the back of the original software box used that word once or twice. Got it to work, cleaned it up, made sense of it, Gene left, and then a year later the government took it over. The guy who took it from me claimed to know Access but at the time I saw no evidence to support the assertion. So, I moved on, pooch got screwed, project fell into the dirt due to the negligence of the government person who now owned it, and time passed.

But here is ANOTHER lesson to be learned. Your reputation follows you. So the boss had ANOTHER project suitable for Access and remembered my name. Sort of like a criminal record that follows you around, so to speak. (Which is why I NEVER admitted to understanding COBOL!)

To make the boss happy, I took that one on doing a similar sort of thing to the first one but with more emphasis on security than resources. Also by this time the site had reached 1200-1500 servers across 60-80 projects (the dashes representing growth of the site over six years). Oh, I learned a lot. Including the old adage, "no good deed goes unpunished."

The down side is working with people who didn't know what they wanted and trying to second-guess them. The up side is that my career with the Navy as a contractor lasted a total of 28 1/2 years of steady, reasonably lucrative employment until I was ready to retire. Just walk into the melee with your eyes open and your mouth shut.

 

[jjatwork]

Since the thread hasn't been closed yet, I'll provide a small update.

I've been studying up on normalization and trying to clean up the DB. Lots of trial and error had resulted in some redundant code and tables/queries. Additionally, I've nearly finished removing any sensitive data from the DB and translating it into english, so I have it ready for upload, should it become necessary.

I'll probably be asking quite a few question, so I'm considering how best to go about it.
Would it the best manner to make 1 large thread, to avoid spamming the forum, or make several, more specific threads, so other people, with the same question/problem, can find a more concise answer when they do a search?

Thanks again to you all. I've taken the advice to heart and read the links.
Mark, I haven't been able to look at your example yet. No unauthorized files on the workstations, but my supervisor is working on a solution.
I'll definately have a look at it when things are sorted out.

 

[Mark_]

Normally you start 1 thread for 1 question. This allows you to keep an eye out for solutions to THAT question without having to read dozens of pages of unrelated information. Likewise it allows others to search for the same issue without having to dig through a discussion on squirrel migrations to find what they need.

 

[jjatwork]

Thank you. That was exactly my thinking, I just wanted to make sure.
JJ

 

[The_Doc_Man]

Adding to Mark_'s answer, I suggest that splitting topics up assures that at least some of the questions get faster answers because the contrary method jams a bunch of questions together at once. As an example, I am not up on SQL Server issues in detail, only in general overview (though I understand ORACLE better). And I am a hardware, security, and operating system guy based on background. But others would come from other backgrounds and have different strengths. So splitting questions up lets EACH of us with certain specialties to jump in where we are comfortable and hope that others can take the things we aren't so sure about.