PO generator (1 Viewer)

[TheJoeness]

Good day all,
I made a PO generator on excel, then realized that we need a database to assist in tracking and to reduce the number of places we input the same data. If anybody is willing to help please drop me a message so that I can share all the info, as I have reached a wall in my brain.

I will share the document on this thread for others that also want a business tracker as soon as I'm done. The idea behind it is to aid all aspects of our business except invoicing.

 

[Uncle Gizmo]

If anybody is willing to help please drop me a message

The idea of these forums is that you post your question publicly and then you will get advice from an amazing variety of people with different experiences and ways of doing things. From their responses, you are bound to be able to solve your problem.

However if you have never created a database before, well you might struggle a bit. your best option would be to Jen up on database construction, get yourself a couple of decent books and/or watch some videos.

Alternatively you might be better off employing the services of a professional database developer.

Sent from my Pixel 3a using Tapatalk

 

[Uncle Gizmo]

I made a PO generator on excel,

It just occurred to me that a Purchase Order is essentially an invoice by another name.

One of the early examples provided by Microsoft Access is an invoicing application. I think it was the Microsoft Access 2007 northwind database.

This may well give you a leg up in producing your purchase order system.

There are links to several of the various northwind database examples on my website here:-

https://www.niftyaccess.com/northwind-downloads/

However they are only examples and not fully functioning applications, and they do have some inherent problems particularly the north wind 2007 version. You can find a link to my explanation of one problem I found and the several solutions I offered...

Sent from my Pixel 3a using Tapatalk

 

[TheJoeness]

Thanks Uncle Gizmo, I agree it is very similar, I will have a look at the link. We used databases in programming when I was in high school so I'm just a little rusty. I've checked a few videos on the subject but they seem to all stick to the principal of ordering the same stock over and over, unfortunately our orders differ from job to job. That is why I posted the question as I did as I'd like the help from the community in such a way that we all learn together.


I'm actually busy with like a mission statement to describe the function of the DB that I want to add to the post, I feel a lot of companies/individuals can benefit from either the DB itself or the construction. That's also why I want to share the finished/closer to finished product.

 

[TheJoeness]

In essence I don't want to waste peoples time with my issues but I do need help and love the learning part of it, it teaches my brain to look at problems differently... I don't want to just post my problem and sit and wait for feedback, then I won't know how to fix the problem when I encounter something similar.

 

[CJ_London]

if you want help, you first have to describe what it is you are trying to do.

for context, describe your business (what you buy/sell) and how the 'PO' app fits within that - who's using it (one person/many people, from where (office/home/client location)
Also describe the specific issues you have - 'unfortunately our orders differ from job to job' doesn't tell us anything.

Also explain where you are going with this - how does the app fit with other parts of the business process either existing or to be developed.

 

[jdraw]

Joe,

See this link from M$oft re database design.

Another link on database design

Good luck.

 

[arnelgp]

just give as a sample excel file as to how the PO is generated.

 

[TheJoeness]

JDRAW


I started with that list shown in the first link all ready. I realized that although I know what I want the databases to do I can't convey it properly to others yet. So I'm writing the MO to share with everybody. This will also help other users in deciding if they want to also use it.

 

[TheJoeness]

Uploading my current PO file now. The idea is to easily select the fixed variables like vendor and all that and then to input all the POs details in a single table for reporting. And then if you have a look at the PRINT sheet, all you have to do is select the PO number from the dropdown and the rest is automatic.


The short of it is I want to know if Access will allow/understand this style of data capturing as shown of the PO sheet.

 

[arnelgp]

access can do that and much more.

 

[The_Doc_Man]

You commented that you were working on a "mission statement" for the DB in question. This is THE crucial time in your DB. You will not find another part so important until you reach final testing. That "mission statement" is your roadmap for whatever happens down the road. It pays you BIG TIME to build a good database "mission and goals" document. Niklaus Wirth, the creator of the Pascal programming language, was quoted as saying "80% of all programming problems originate from bad data design." (He said it in Polish so that is a translation, not a direct quote.)

I have reached a wall in my brain.

If you have a good design document and know where you wanted to go, you will find that like the Biblical Joshua at the Battle of Jericho, that document will make those walls come tumbling down.

Don't let the design phase daunt you. Realize that time spent in design is time well spent. It pays dividends when you start code implementation. One of the biggest mistakes people often make with databases is "shooting from the hip" i.e. starting to code before the data design is essentially complete. Oh, sure, you stumble across issues during implementation and have to go back to tweak something. Happens all the time. But you want to get the "big picture" laid out early. That way, the "big ticket" items can be easily implemented.

 

[Mark_]

To add to Doc's post, can you walk us through how you envision someone using the system? Start with the person taking an order. What do they need to know? What do they record?

Remember that as soon as you say "And the second", you will normally be looking at a child table (or tables) for what you record.

By walking through what you expect, you can sit down with those who do and see if it meets what THEY need. This is often done with end users so that you can avoid big issues later.

 

[Pat Hartman]

As a start, the first two tabs will be separate tables and all the little tables on the third tab will be separate tables. Then the repeating part of the PO sheet will become the PODetails table and will contain 1 occurrence of description, price, and qty plus a foreign key that points to the POID of the PO table. So instead of fixing the number of items per PO at 30, each PO can have as many items as you need it to have.

Once you get that done, post back.

 

[TheJoeness]

Thanks to all the help and advice so far, the info I supplied has been very vague, but you guys have been of great help. I considered closing the thread until I can post a proper question, but decided to leave it as is. The "MO" of my database is almost complete, as soon as it is I will post it for all to see and a copy of the DB as well. But once again a big thanks to everybody for their input.

 

[TheJoeness]

Added I might also delete it and just start over, might be the best approach to doing this thing properly.

 

[Pat Hartman]

I gave you pretty specific instructions on how to begin to normalize your schema so you can move forward with Access. Keep in mind that relational databases are very different from spreadsheets when you get behind the curtain. In a spreadsheet, the data layer and the presentation layer are merged and that causes considerable repitition and impedes the ability to produce different kinds of reports. It also makes it a monumental chore to add new horizontal values.

If most of your POs include three items, you are wasting 27 instances and what happens if one PO must have 32 items?

 

[TheJoeness]

I gave you pretty specific instructions on how to begin to normalize your schema so you can move forward with Access. Keep in mind that relational databases are very different from each other when you get behind the curtain. In a spreadsheet, the data layer and the presentation layer are merged and that causes considerable repitition and impedes the ability to produce different kinds of reports. It also makes it a monumental chore to add new horizontal values.

If most of your POs include three items, you are wasting 27 instances and what happens if one PO must have 32 items?

Thank you very much for that input, I have been struggling with that question. I haven't had the time to look into your previous suggestion, but your energy will not be wasted, nobody's advise will fall on deaf ears.