PDA

View Full Version : Business System Ideas - Advice Needed!



MrKabanenko
10-18-2013, 05:35 AM
Dear Group Members,

I have recently joined this group since I have started a University course which requires VBA/SQL programming.

Since I have not prior experience in programming, but have some IT knowledge, I would like to ask for your help.file:///C:\Users\Dima\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif

For my project, I have to come up with a business system which will use both Excel (VBA) and Access.

Access will be used as a back-end programme for data storage.
Excel needs to be used for multiple business calculations and user interface.
Users will be displayed error messages to if they enter wrong information in the userform (in Excel).

I am thinking of using an online shop idea for the customers to input their details into the user forms in order to register and log-in. There will also be an interface to select and order products (using a list box and other features).

The Excel calculations will perform profit/loss analysis, stock checks (using if analysis) and other calculations (perhaps project future sales based on prior performance if possible).

Could you please let me know what you think of this idea and perhaps suggest any other interesting business projects which will combine Excel (VBA) with Access (SQL)? I have posted in this forum since the majority of the calculations and queries need to be performed in Excel (using VBA).

Your help will be much appreciated.

P.S. This is a university level course and even though the lecturer does not expect me to produce a comprehensive system, it still have to be a which will incorporate calculations and user interface in a business environment.

Kaban

SamT
10-18-2013, 07:21 AM
Since I have not prior experience in programming, but have some IT knowledge, I would like to ask for your help.
file:///C:\Users\Dima\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gifThats what we see in your post :D

You cannot paste an image from your computer to a web forum. You can upload it to a file share site and paste it, or you can upload it directly here using the "Go Advanced' button.

I would not put the store online, because that would mean also learning to code HTML. I would start your project by first building the store, from inventory to cash register and Receiving to Accounting.

"Process follows Structure"(snb) and the store is the foundation of all the other Objects in your system.

The next higher level object is the DataBase. Its structure depends on the Store's activities and Objects. I have found that an old shareware version of Dezign4Database has the best basic tutorial on DBs. Here (http://www.datanamic.com/dezign/) is the latest version, but I can't comment on it$ tutorial.

Only after you have the DB structure can you use VBA's UserForms to handle the data and Excel to process it.

VBA can also use Word to handle any paper forms you need

Paul_Hossler
10-19-2013, 07:30 PM
MS had used a sample database call Northwind which might give you ideas

http://databases.about.com/gi/o.htm?zi=1/XJ&zTi=1&sdn=databases&cdn=compute&tm=17&gps=467_5_1680_881&f=00&su=p284.13.342.ip_p504.6.342.ip_&tt=7&bt=0&bts=0&zu=http%3A//office.microsoft.com/en-us/templates/TC012289971033.aspx%3FCategoryID%3DCT101428651033%26av%3DZAC000


DescriptionThis sample database template demonstrates how Access can manage small business customers, orders, inventory, purchasing, suppliers, shipping, and employees. The database can generate 15 different reports, and is a great showcase for learning and customizing Access databases.



You might think about using Access to enter data into the database and for data listings, and use Excel to report 'management' type date

We created a dashboard using Excel 2010 pivot table slicers and connected to the access data tables

The Northwind database is pretty complicated (at least to me since I'm not a database kind of guy) as you can see in the screen shot, so you probably won't need the complexity, but it might give you ideas


Paul

SamT
10-20-2013, 06:23 AM
Briefly, that image shows 18 tables, the Table (Excel Sheet) name is in the Title bar of each block.

Each block lists the Field (Excel Column) names. You can't see all the Fields in the blocks with scroll bars.

The lines represent Relationships, by field, between Tables.

Using the left side of the Orders Table:



[*=1]The top line, from Orders.Order ID to Order Details.Order ID, (I would have named Order Details as Order Items,) means that those two Fields have the same value in their respective Record (Excel Row).
[*=1]The Arrow on the Order Items Table end of the line means that there must be a Order Record before there can be an Order Item Record.
[*=1]The infinity symbol on the Order Items end means that there can be many Order Items Records (with the same Order ID) for each Order, but the 1 on the Orders end means that there can only be one Order Record for each Order Item Record.
[*=1]The lack of an arrow on the Orders Table means that there doesn't have to be a value in the Order Items ID field. (Order canceled, no items.)




Tables with only 2 fields are Index or Lookup tables (Excel Lookup tables)

Using Employees, Employee Privileges, and Privileges tables



[*=1]Employee Privileges is an Intermediate Index. It can contain many records with identical values in the Employee ID and Privilege Fields
[*=1].
[*=1]Each of any employee's Records will contain only one unique Privilege ID value.
[*=1]Since many employees can have the same privilege, both Fields must be compared to uniquely identify a Record. (Both Fields have a Key symbol next to Them.)
[*=1]The Key symbol indicates a Table Key Field.
[*=1]A Table must have a Primary Key Field, and can have a Secondary Key Field, and in very rare cases, a Tertiary Key Field. Generally, if you think you need a Tertiary Key, you need to redesign the Database.


Tables have plural form names. Fields have singular form names. IMHO, the use of ID Field names that include the Table name should be avoided, except for Secondary Keys and Intermediate Index Tables. See: Privileges, Orders Status, and Order Details Status Tables.

Also, IMHO, the Products Table is over simplified, in that it fails to consider that a product may have several suppliers, for example; #2 pencils.

A Possible Products Database structure


In-House Products Table



[*=1]In-House Product ID
[*=1]Product In-House Details Fields


A Tertiary Keyed Intermediate Index Table



In-House Product ID
Supplier ID
Supplier Product ID


Supplier Tables


Supplier Details
Product Table


Suppliers' Product Tables



Product ID
Product Detail Fields




I hope this helped.

Paul_Hossler
10-20-2013, 07:13 AM
SamT is correct. The Northwind Access tables (again only an example) could also be implemented as Excel worksheets.


But since the OP's requirements were:



Access will be used as a back-end programme for data storage.
Excel needs to be used for multiple business calculations and user interface.


it seems like a appropriate example

Designing a good, workable DB structure is WAY beyond a forum post, so SamT's details are worth reading.

http://en.wikipedia.org/wiki/Database_normalization

http://support.microsoft.com/kb/283878



While Excel could do everything, IMHO using Access as the RDBMS to handle data storage, but connecting to it as a Excel Data Connection to report and perform business calculations would be worth investigation

As I said, we've had very nice (and easy) results using Excel 2010 with a pivot table using the Access tables as a source. Add slicers, a couple of pivot charts, and you have a nice looking management dashboard

The real challenge will be defining the requirements and the system architecture:think::think::think:

But then, it's not MY grade we're talking about either :devil2:

Paul

SamT
10-21-2013, 06:35 AM
Paul,

IMO, Northwind is a very good example for beginners. It is not fully Normalized, but current opinion is moving towards thinking that normalization is not the be-all end-all of DBs. That sometimes Business Rules should take precedence. For example; Normalization requires that all persons' details should be in one table, Customers, Employees, and Supplier Contacts, but business practices show that it works better to have separate Tables for each. Customers and Employees have addresses, but Employees also have pay rate, departments, and managers, while Customers have shipping addresses. Supplier contacts only have names, departments, and phone numbers, not personal addresses.

@ Mr. Kabanenko,

I would break the Database into several separate DBs: Customers, Employees, Inventory and /or also Suppliers; and a Store activities DB, which includes all other tables. While Inventory sees quite a bit of activity in the In Stock Field, it, along with the rest of the first four DBs listed are relatively static. The Store activities DB is very busy. This allows you to have seperate Data Input Forms for each department, which serves to isolate them from the rest of the DB.

Excel is a very good DB design aid, since each DB Table corresponds to a Sheet, Fields are Columns and Records are Rows. In practice, I put all Index tables on the same sheet, since they are only two Columns wide.

DB Normalization simply means that every Column Label be unique across the DB. IOW, if you use one DB for for the store the First name Column labels should be "Customer First Name," Employee First Name," and "Supplier Contact First Name."

MrKabanenko
10-28-2013, 10:35 AM
Hi Guys,

Just came back from a holiday and now this is the moment when hard work starts. Thank you very much for the contribution.

The most difficult part for me is to architect a system which will incorporate all of the main features whilst ensuring that I don't make it too complicated for myself since my VBA and SQL programming skills are fairly limited. I will not create a website for this project since as SAMT mentioned I would need to learn to code in HTML.

Here is my relational database;

Tables - Customer, Stock, Sales, Suppliers - Customer (One) to Sales (Many), Stock (One) to Sales (Many), Suppliers (One) to Stock (Many)

GUI will be used for customers (to register their details and make a purchase (by choosing the products and quantity and entering their card details).
GUI will be designed for the manager who will be able to access financial information of company (profit/loss, cash-flow, inventory, historical data) and delete any irrelevant data from the database. Financial information will be restricted to managerial use only.
GUI will be created for the sales force to add or update the details of the customers and suppliers, whilst being able to order more stock and produce invoices/receipts to customers.

Stock levels will be monitored using what-if analysis (e.g. if specific product dips below 5 units, excel will give the employee an option to order more, for high quantity purchases I will use a Select Case feature to offer a % discount).



I am only starting to develop this, but please let me know if I am missing any important information or functions that I need to incorporate. : pray2:

I will check Northwind out thank you!

Paul_Hossler
10-28-2013, 12:17 PM
Just some thoughts and a sample XLSM to give you some ideas

3 Simple Databases (Products, Suppliers, Customers) with mockup data
1 Table with Sales related data that links to the data in the 3 tables

I like to use the VBA Property construct to get or set data so I added an example if you're interested

IMHO you need / should seperate Data from Processing from Reporting

So you could have

1. A UserForm or process to Add / Modify / Delete the Products table, etc.

2. A UserForm or process to Add a Sale

3. etc.

Don't forget error processing:

What is the user enters a non-existant Product, or try to add one that's already there?


I'd layout the data first and then figure out what to do with it. Just my style.

Also, don't make the project too big


Good Luck and hope you get an A+

Paul

MrKabanenko
10-28-2013, 04:48 PM
Thanks so much Paul for this, you are an absolute legend!:bow:

Paul_Hossler
10-28-2013, 05:12 PM
<blush>

Post follow up questions after you get further into the design if you want

BTW, in your #1 you mentioned using Access. Are you going with an all Excel approach now?

Paul

MrKabanenko
10-29-2013, 01:59 AM
Regarding using Access, than yes it is still part of the plans (since it is part of the criteria to combine both software's together).

At this stage, I do not worry about connecting both software's together since we are planning to study this in the next 2 lecturers.

I am planning to create a relational database with 4 tables; customers, suppliers, sales and stock.

Customer (One 2 Many) Sales
Stock (One 2 Many) Sales
Supplier (One 2 Many) Stock

As far as I understand, majority of the calculations and front-office stuff will be in Excel, whereas, Access will be used for data storage and manipulation using SQL.

Planning to start developing this project fairly soon so I guys can definitely expect more questions from my side!

Regards

MrKabanenko
12-02-2013, 06:49 AM
Hey guys,

This is me again. Hope all of you are well.

I am currently in the process of designing my Excel & Access project (as described above) and would like to ask your advise.

I have designed an inventory system for the online shop and want to create a function (using a button on the sheet) which will allows the user to specify the product and quantity to order. Once the person specified the specific product and quantity, the spreadsheet will update the worksheet with the new quantity.

Any help and advise would be much appreciated.

P.S. I have attached 2 screenshots of my relational database in Access as well as the screenshot of my inventory system.

Regards,

Dmitri

Bob Phillips
12-02-2013, 08:50 AM
In my experience of relational databases, which is in excess of 25 years, it has always been the case that normalization is NOT the be-all-and-end-all. And not because of business rules, the database design should cover that, but for pragmatism and performance, the real world. You might have a database for data input, and another that is used for reporting, and if so the input database could be considered for full normalisation, the reporting database could be something that is better to report from (star schema, pre-aggregations, and so on). Putting different business attributes in the same table is always a hostage to fortune in my view, and always has been, it will bite you one day. Customers and employees may be similar in some ways, but they are different in at least as many ways, and the business certainly sees them as very different beasts. We were always told to design a logical model that was fully normalised, then take it on to a physical model that was more aligned to the physical and logistical environment. Never let a purist DBA drive the application.

Business rules should always take precedence, you don't have a database if they don't. Other than that, I repeat the previous comment.

To me, normalization is all about minimising data redundancy, breaking the large initial dataset into smaller sets (tables) that facilitate data updates. I have never heard anyone feel that column labels must be unique, in fact the relationships always have the same name in every design I recall seeing (Customer:CustomerID is a primary key on Customer, Sales:CustomerID is a foreign key on sales, and they complete the relationship that says we make sales to customers). I wouldn't used a column name of Customer_First_Name in the Customer table, that is redundancy using Customer twice (4th normal form :)). Column names have to be unique within a table, but the DBMS should ensure that.

Bob Phillips
12-02-2013, 09:02 AM
Hey guys,

This is me again. Hope all of you are well.

I am currently in the process of designing my Excel & Access project (as described above) and would like to ask your advise.

I have designed an inventory system for the online shop and want to create a function (using a button on the sheet) which will allows the user to specify the product and quantity to order. Once the person specified the specific product and quantity, the spreadsheet will update the worksheet with the new quantity.

Any help and advise would be much appreciated.

P.S. I have attached 2 screenshots of my relational database in Access as well as the screenshot of my inventory system.

Regards,

Dmitri

Have you written any code to write data to your Access database, any code to read from it, any code at all? Exactly what help do you need other tha all of it?

Bob Phillips
12-02-2013, 09:20 AM
Looking at the screenshot, I don't think you need nor should have the Order ID on the customer table. You will repeat customer information this way, the link between orders and customer details is the customer, so ID is all that is needed.

Similarly, you don't want Order ID on the product table, but you do want Product ID on the orders table. Same reasons as above, and when you have that, the relationship should be one product to may orders (unless you have multi-product orders but let's not go there), the other way round to how you have it.

I can see that you are saying that a supplier may supply more than one product, but could one product be supplied by more than one supplier? How will you handle that if that is the case?