Databases

[rharri1972]

Just a general question.
I know most say to build databases in Access. However, I am wondering if it can be done in Excel with VBA.
My wife and I are starting a business in which I will need a customer database to not only keep contact information but to also be able to create or take orders in which it can maintain several orders over time for each individual customer. I will also need to make orders from vendors for supplies and would want to maintain that history as well for each individual vendor I order from. I would also need a products database in which i would maintain product part numbers and cost.

Is this possible to build in excel with vba?

Any advice and/or suggestions would be appreciated!!

Thanks
RH

 

[igold]

I have done something very similar to this in both Excel and in Access. It can be done in Excel, the most important part of doing it successfully in Excel (IMHO) is setting up your data correctly in the worksheets. A project like this screams for Access. If your business is successful, two years from now you will be banging your head against the wall for not doing it in Access. Also, you can become fluent in Access without knowing VBA, just get comfortable with the Access Macro Builder.

 

[rharri1972]

I have done something very similar to this in both Excel and in Access. It can be done in Excel, the most important part of doing it successfully in Excel (IMHO) is setting up your data correctly in the worksheets. A project like this screams for Access. If your business is successful, two years from now you will be banging your head against the wall for not doing it in Access. Also, you can become fluent in Access without knowing VBA, just get comfortable with the Access Macro Builder.

igold...i have been intimidated by Access for years. Maybe its the queries...IDK...however, any way of pushing me in the right direction to knowing..or understanding Access?

 

[igold]

All I can really say is that I am self taught. Creating queries is easy once you get the gist of what Access wants and is the same exact format as the MSQRY32 program that used to come with Excel. To be honest, I found Access 2010 to be easier than Access 365. If you think about it, an Access table is identical to an Excel worksheet. Start off small and reverse engineer to teach yourself. Try to make yourself a small address book with an easy query to look up names. You don't want your business project to be your first brush with Access.

As a start, create a table in Access with fields like "Last Name", First Name", "Phone", and then populate it with a couple of rows of data. Then Create a query by using a blank query and dragging your table to the top half. You can then drag your table fields to the bottom half of the query. in the "column" that gets the "Last Name" field, in the criteria row of that column enter something like this "[Enter Last Name]" sans quotes. Run the query and you will be asked for the Last Name you are looking for and the query will return whatever you searched for.

 

[rharri1972]

All I can really say is that I am self taught. Creating queries is easy once you get the gist of what Access wants and is the same exact format as the MSQRY32 program that used to come with Excel. To be honest, I found Access 2010 to be easier than Access 365. If you think about it, an Access table is identical to an Excel worksheet. Start off small and reverse engineer to teach yourself. Try to make yourself a small address book with an easy query to look up names. You don't want your business project to be your first brush with Access.

As a start, create a table in Access with fields like "Last Name", First Name", "Phone", and then populate it with a couple of rows of data. Then Create a query by using a blank query and dragging your table to the top half. You can then drag your table fields to the bottom half of the query. in the "column" that gets the "Last Name" field, in the criteria row of that column enter something like this "[Enter Last Name]" sans quotes. Run the query and you will be asked for the Last Name you are looking for and the query will return whatever you searched for.

wondering if you can help me with some baby steps...I am practicing at this point...but I want to be able to go to customer form...scroll to or type in customer needed and then when selecting that customer... I can hit a button "Enter New Order" and an Order form pops up...autonumber to populate for PO number... I will enter part number which will auto populate price...a typical order form..qty...part number..pricing..blah blah. I of course need the PO information retained in cases of having to look it up later if needed. I see that the forms are typically based on tables...but for "new" orders... this would be new information needing to be kept. I hope i've made sense. I realize a Customer Table and Inventory Table will be involved.

 

[Joe4]

I agree that Access is the way to go. While you can "force" it into Excel, Access is a Relational Database program and Excel is not (and what you have is a Relational Database).
Doing it in Excel is very cumbersome and inefficient, and you may not be happy with the performance, especially as your data grows.

That being said, one of the most important things is to design the database tables correctly, precisely following the rules of Database Normalization.
If you do this, you will be able to do just about whatever you want with the data in Access. If you don't, you may find even seemingly simple tasks difficult to do (been there, done that).
There is a lots of good documentation out there. Here is a good one that kind of touches on a variety of things which should be helpful for creating databases in Access (pay special attention to the Database Normalization section): Database design basics

Another good thing is that there a ton of pre-created Access templates which you can find on the internet, many of which are free, especially when it comes to business stuff like this.
I would be willing to bet that you may be able to find something pretty darn close to what you need already created out there.

 

[rharri1972]

I agree that Access is the way to go. While you can "force" it into Excel, Access is a Relational Database program and Excel is not (and what you have is a Relational Database).
Doing it in Excel is very cumbersome and inefficient, and you may not be happy with the performance, especially as your data grows.

That being said, one of the most important things is to design the database tables correctly, precisely following the rules of Database Normalization.
If you do this, you will be able to do just about whatever you want with the data in Access. If you don't, you may find even seemingly simple tasks difficult to do (been there, done that).
There is a lots of good documentation out there. Here is a good one that kind of touches on a variety of things which should be helpful for creating databases in Access (pay special attention to the Database Normalization section): Database design basics

Another good thing is that there a ton of pre-created Access templates which you can find on the internet, many of which are free, especially when it comes to business stuff like this.
I would be willing to bet that you may be able to find something pretty darn close to what you need already created out there.

Thank you!

 

[Joe4]

You are welcome.