Is Access what I need? (1 Viewer)

[Inga]

I'm a complete newbie to Access, and I have no idea if it is what I should be using or not. Before I spend a lot of time learning and building a database, I was hoping I could get some feedback on if Access is the correct program to use. Basically, I'm currently using Excel to calculate my monthly Payouts for about 500 consignment vendors. We use Quickbooks POS which can export their total sales for a given period into excel which I then use to determine how much to pay them, what payment method they get paid by, if anything was owed to them from a previous month, if they owe us anything, etc.
Excel works for me, the issue I'm having is that as it is, I'm the only person that can use my excel payout. It becomes very complicated trying to show someone else what you import where etc., and there are lots of opportunities for mistakes. I'm hoping that Access will solve that problem. I was thinking I could have the Vendor information (tax status, payment method etc) as a table, import the vendor sales into another table and then have access create a report that says if Vendor A has 2 payment methods, pick method 1 if less than x amount and method 2 if not. If Vendor B is VAT registered, add X% VAT. I use a lot of vlookup(iferror) formulas in my program right now.
I basically want that anyone can export the vendor sales from our POS and import them into Access, and then just pull saved reports for payment method.
I hope that was clear enough.. and no too long winded. I can post a sample of my excel file if needed, but would need to clean up the file first which I've not got the time for as I'm supposed to be calculating the payouts right now!
Thanks!

 

[MajP]

You can do a ton of automation in Excel to make this much easier for others to use and provide a nice user interface. But that would take time to learn too.
My first guess is Access would be a better choice. People here will walk you through creating a Db. Especially if you can explain your processes in plain language not what the spreadsheets do.

 

[The_Doc_Man]

Based on your discussion of having a melded list of factors which sometimes need to be separated, I would say that you have a strong chance that Access would do what you needed done. Not 100% because it IS possible to manipulate Excel by hand. But from your description, it is increasingly complex as your list of current and historical information grows.

IF you are going to do this, though, beware of a stiff learning curve, because your description makes it clear that you have multiple factors involved in separation of your data. If I may, I would like to recommend some general reading material that will be invaluable to designing and implementing your proposed Access project.

You need to understand Database Normalization. You SERIOUSLY need to understand this BEFORE you try to build anything in Access at the level you have described. Otherwise, there is no way you will be able to make this work as you need it to.

Therefore,... IF you search for articles in this forum, which IS about databases, search for NORMALIZATION. IF you search the web, which covers too many topics, search for DATABASE NORMALIZATION. The reason is that normalization is a common term in chemistry, mathematics, diplomacy, psychiatry/psychology, and several other fields, so you need to qualify the web search to limit it. Further, if you do a web search, start reading articles from .EDU domains at first, because the .COM articles may try to sell you something. But once you have read a few articles on the concepts and methods of normalization, some .COM sites have decent articles too.

If you don't already have one, I might recommend getting some book that relates to a beginner's use of Access. I won't suggest a specific title because I have found that different writers related to their target audience in different ways. You need to go to a book store that has several Access books and take a quick peek in each one to see which one "connects" with you the best. Buy that one.

By the way, that was your first post. Welcome to the forum!

 

[MajP]

One big difference from Excel and Access is that in Excel the storage of data and display of data are not separated. In a Db how the data is stored is separate from how you work with and display. In a Db the most important thing in the beginning is getting the tables correct. Then you can focus on making it do whatever you want.

 

[plog]

Complexity has to go somewhere and someone has to deal with it. The more simple you make this for a user, the more complex your task of building it will be. A lot of people use Access to simply put a form and report on top of their one tab spreadsheet and have great difficulty in achieving that. What you want is much more difficult--multiple tables, multiple forms, multiple queries, multiple reports and business logic built into them all.

So, Access can accomplish this, but its not going to be easy or quick for you. My advice is to divide and conquer. Can you break your process into pieces? Can you use Access to do one part of your process? Get one thing working, perhaps something you can hand off to someone else and they can deliver back to you the results and then you complete the rest. Then keep adding more pieces to Access until the whole thing lives there and its simple to use.

 

[theDBguy]

Hi. Welcome to AWF!

I think Access will definitely help you with those needs. Good luck!

 

[Gasman]

I created (well for me) quite a complex commission system, that worked as per my employers business processes.
I was the one who used it.
Going on holiday, I had to show a colleague how to use it. There was also a manual.
After showing him, he remarked 'well that seems simple enough'

My response was 'you do not know how much hard work went in to making it work like that'

So you have a fair hill to climb.

 

[Pat Hartman]

You've already dealt with Quiclbooks and Excel. How hard can Access be It is certainly suitable for the task and will ultimately be easier and more flexible than Excel.

Quickbooks will give you some clue as to how your tables in Access will be defined. If you really want to automate your interface with Quickbooks, you can buy an ODBC driver that lets you view/update data held in Quickbooks from Access. This isn't necessary as long as you are willing to have the separate step of manually exporting transactions from Quickbooks into a spreadsheet and then importing them into Access. You also have the inconvenience of entering certain pieces of data in both applications such as some basic vendor information as well as product information if you don't go with the ODBC automation.

For starters, I would not try implementing the ODBC interface. It is a little complicated and at your skill level, may be a bad place to start. The transaction export/import is basically pushing a button in one app and a different button in the other app so that is pretty trivial. The difficult part is making sure you have some checks in place to ensure that you don't lose transactions in the process.

We've happy to help you. If you want a leg up, post a spreadsheet exported from Quickbooks and we'll help you to organize it into tables. Then, we'll need to see the spreadsheet you use to produce your output. If you don't want to post sensitive data, try to obfuscate it. It is really hard to look at a complicated workbook with no data in it.

 

[Gasman]

When I was volunteering with SSAFA as Divisional Treasurer, I used Quicken for the accounts.
At the end of the year, I would produce reports to help the Divisonal Secretary, as he had to report on how many transactions and amounts from each service etc.
I would export the years transactions to Excel.
Tidy up that sheet, get rid of balances and populate cells where where split transactions had occurred.
Then import into Access
Then run various functions to populate my tables with donors etc.
Then run the reports.

 

[Cotswold]

Follow the advice above. Initially it will be the trivial and the minor issues in Access that may not be detailed in the Access training books which will provide frustration and lose time. The simplest things will cause the most problems. Things that after a week or so you take for granted. I'd advise on obtaining a copy of the Language Reference, either by downloading or obtaining a copy of the book. When you have it, even casually reading it through is essential. Keep it to hand as it will probably be your main reference.

There is a mass of information online, and on Access-Programmers that include sample databases. Take time to look through the website.

Allow maybe six months before you have something that you are starting to be happy with. When you realise a better way of doing something, don't be put off, switch to the better way, even though it may negate your previous work.

The benefit of Access over Excel from security, reliability and multiuser operation will be worth the effort. It won't be easy, but it will be worth it. The first month in a new language is the worst and remember that is the same for everyone.

 

[MelissaMuz]

When you realise a better way of doing something, don't be put off, switch to the better way, even though it may negate your previous work.

I love this! I'm new to Access and have taken this leap a couple of times so far and its always worth it!!

 

[Inga]

Thanks everyone! Looks like I'm learning Access then! I'll definitely be asking for help when I get to something I can't figure out. I did get myself a copy of Access for Dummies, but so far I've not really looked much at it. I did watch a few YouTube videos on the very basics, but from those I couldn't tell if I would be able to do what I needed to, but you all have cleared that up for me! I really appreciate the very quick response too.

 

[Gasman]

Get your tables setup correctly and then that makes life so much easier.
However you appear to be doing much the same as I was, so not so complicated after all.

Break it all down into small steps and tackle one at a time.
Search here for normalization. Links have been posted multiple times that are worth looking at.

 

[GPGeorge]

Thanks everyone! Looks like I'm learning Access then! I'll definitely be asking for help when I get to something I can't figure out. I did get myself a copy of Access for Dummies, but so far I've not really looked much at it. I did watch a few YouTube videos on the very basics, but from those I couldn't tell if I would be able to do what I needed to, but you all have cleared that up for me! I really appreciate the very quick response too.

As previously stated: Start with Database Normalization. Do not pass Database Normalization. Do Not try to collect forms or reports until you have learned what Database Normalization is and why it is the crucial element in building any relational database application, with Access or any other database development tool. Then you can start building tables.