New to Access - is it right for me?

[Mike1402]

Hi all,

I used Access very briefly at college around 11 years ago ... I know some of the basics that Access can offer and it's advantages over using Excel but I would like to know what the experts think to my situation and whether it would be worthwhile learning how to develop an Access database myself. The biggest problem in learning a new tool and not knowing much about it is learning whether it can solve the problems we have in the first place.

I work for a company in a product and pricing role. There aren't many people who are particularly proficient with Microsoft programs, or any computer software full stop ... I'm no where near as skilled in Excel as a lot of the people I see on here (I regularly visit here to find answers to problems with VBA etc for Excel) but I can manage most of the day to day stuff and have developed many of the spreadsheets we use myself. At this moment I'm unable to give any examples of the spreadsheets but if required I could probably create a "dummy" version of them to better explain what I would like to do.

So one of the first spreadsheets we operate is our product and price file. This has a separate tab for each product segment. Each tab is mostly the same, the left hand starts with a product code, lists various attributes to do with the product and then we have an area for notes and progress on certain lines as they enter development through to order ton our UK subsidiary. Following this comes the price section. We have our previous "retail guide" price, followed by multiple discount rates different customers can achieve. Then there is a column showing our rate of change as a percentage, followed by the same pricing structure again for our current price. Each time we realign our prices, the current becomes the previous and a new current is calculated. At the end of all this we have our unit cost price from the factory.

This spreadsheet is used to create price lists that we send to our customer base. Put simply, the relevant product and price columns are copied from the master file into a separate file, listing only the relevant information that a customer needs.

The second major spreadsheet we operate is our competitor price file. This again lists much of the product side of things (and therefore whenever we add or remove products in the master file, this action has to be mirrored in the competitor file so our product range is the same) but then differs from there onwards. We show our retail price and a net price that we can change using varying levels of customer terms. Following on from this we list the competitor part code for the same product and their retail price, discount and net price to customer. We list many of these across for as many competitors as we can gather information for, and then we have simple index table showing all the indexes to our own price.

If you're still reading, thanks for coming this far. My main queries (hohoho ..) are

1. Can I simply list all the data I've described into a database and more importantly, do you think it's a worthwhile exercise? To be clear, we have some 1500 products across all brands and segments currently listed
2. We currently update our competitor information as and when we can get it using a vlookup against their own product codes - can this be done in access to update the competitor information?
3. In using Access I would therefore like to set up a way of automatically creating the price list to send to customers (possible back in a spreadsheet) competitor analysis etc without having to hide, copy, paste, format the data every time we need something from Excel

I'm sorry if this question is either too broad or too vague to give an accurate response, but I'd appreciate any thoughts. If you have any questions or need any further information please let me know.

Thanks.

 

[Chrisdontm]

Access would be great for making the "inquiries", or in access it is called "Queries"
thus eliminating the unnecessary data in any reports.
It takes more time setting it up, but once it is set up, it will be real simple for anyone to use.
But yes, it is learning another program, but rewarding.
I learned a bit from a guy on Youtube, he teaches you to setup a database for a Hotel.
Check it out and see if it looks like something you want to get into...


https://www.youtube.com/watch?v=B3BOcV-wJcU&index=1&list=PLLNPs0V_Il08_M24uh2OMAmRurVXYyTle

 

[Micron]

I would have to say I'm more adept with Access than Excel, so that's my frame of reference in this comment.

Access excels (no pun intended) at data storage, manipulation and grouping. It can also present information in a very professional manner by way of reports that list and summarize, and it can 'trade' information with Excel and interact with other Office software. Using simple tools, it can automatically send email (even without anyone being there to start it). Based on your post, I'd say that once set up and running smoothly, it's less maintenance than what you're doing now, and yes, it can handle what you describe. What Access does not do well IMHO, is charting, and I have posted that opinion here and elsewhere before. AFAIC, that will never change as long as MS can sell you software that handles charting in such a big way (Excel). For this reason, I have 'married' Access and Excel processes before - one to provide the data storage and retrieval, the other to chart. However, all that power and flexibility comes with a big learning curve, and only you can decide whether or not to embark down that road. One thing for sure - you CANNOT overlook the subject of normalization and database design principles or you will only hamper your efforts down the road, probably exponentially as you ask more and more of what you create. I recommend you buy, beg, borrow (or whatever) books on the subject. Sounds old fashioned when there are so many video tutorials available, but they're hard to bookmark and re-hash when the need arises. Vid's are better suited to people who already have a foundation. Perhaps you feel your brief introduction so long ago has provided that, but I don't see how vids beat having in-depth code examples and detailed explanations at your fingertips. Some books even have cds with all the written code and decent database structures. I point that out because I often see comments about the MS Northwind database not being the best to learn from. In fact, I don't think I've ever opened it.