• 0

Using Access over Excel for database management


Question

So... right now at my job I have 10 different outlook files for different sales people.  Each file has the accounts they handle and the monthly revenue going back - 10 years.

 

The problem - is having to update 10 different files each month...

 

I would love to be able to use access to have every account in one place and then when I update those each excel sheet gets updated for the staff?

 

Is that easily doable in Access?  

 

I consider myself a strong user of excel but have never used access..

 

Thanks in advance! 

12 answers to this question

Recommended Posts

  • 0

sure, but it does require a bit of programming to get the fields to do what you want, and if you want a front end well you will need to design that too. 

 

Might I suggest something like ACT!, it would probably work better or some other customer relations manager (crm).  Each can have their own logon and enter their own data.

  • 0
  On 20/07/2015 at 15:44, sc302 said:

sure, but it does require a bit of programming to get the fields to do what you want, and if you want a front end well you will need to design that too. 

 

Might I suggest something like ACT!, it would probably work better or some other customer relations manager (crm).  Each can have their own logon and enter their own data.

 

Gotta stick with MS suite... since that's what the company uses... 

  • 0

Yeah there are much better contact management systems other than outlook contact files that is for sure..  The mentioned ACT is one of the major players in that area.  http://www.act.com/

  • 0
  On 20/07/2015 at 15:56, Ice_Blue said:

If you give me an outline of what data you need to track, I may be able to give you some guidelines as to how to develop an access database to accomplish this.

 

 

We have 10 sales people who handle different accounts.  Right now I update 10 different excel worksheets every month so they have updated figures of how much those customers spent last month.  I am basically opening/closing 10 different workbooks.  And then lets say someone leaves and we hire someone new I then have to create a new sheet and transfer the accounts.

 

I would love to be able to have ONE master sheet and a field with a salespersons initials and then I update that one sheet monthly it automatically updates 10 individual sheets for the sales people.

 

Hope that makes sense.

  • 0
  On 20/07/2015 at 15:53, Sk8Surfr said:

Sorry, maybe my initial post wasn't clear... 

 

It is more about the DATA than it is the customers.  

 

I'm not concerned with addresses, and contact name etc.

 

It's about revenue 

Fully understand that, however instead of trying to recreate the wheel you could do something that is a bit better in doing what you are trying to do.  A crm like act is a bit more than just a contact list to look up customers in, it helps you manage the account...follow their purchases, alert you when you haven't heard from them in a while, let you know trends as well as their weekly, monthly, quartly, yearly, 5 year, etc totals.  

 

square peg in a round hole, you have a lot of filing to do to make it work, vs getting a solution talored to do what you want.  There are some free/open source crm solutions out there. 

  • 0
  On 20/07/2015 at 16:01, Sk8Surfr said:

We have 10 sales people who handle different accounts.  Right now I update 10 different excel worksheets every month so they have updated figures of how much those customers spent last month.  I am basically opening/closing 10 different workbooks.  And then lets say someone leaves and we hire someone new I then have to create a new sheet and transfer the accounts.

 

I would love to be able to have ONE master sheet and a field with a salespersons initials and then I update that one sheet monthly it automatically updates 10 individual sheets for the sales people.

 

Hope that makes sense.

When you open Microsoft Access 2013 (not sure about 2010) you can choose from a selection of database templates.

The one you are looking for is the "Desktop Northwind 2007 Sample Database".

 

Click on this and enter a name and path for the database. It will be downloaded and opened directly in Access.

 

See if this is something like what you want.

If so, this database can be easily modified to more suit your needs.

 

The Northwind Traders sample database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world.

  • 0

Connect an Access database to your workbook

 

It's certainly possible. There are many things to consider though: data integrity, data security, data consistency. There are too many moving and distinct parts. I would consider what sc302 said, and said well: don't reinvent, you probably don't have the time, the support or the experience. Besides, already built solution will sound good when you're about 10-15% into this new, heavy thing and you realize expertise doesn't happen overnight. 

  • 0

Your scenario sounds like a good use of MS Access. Keep all your data in one Access .mdb file, and modify your spreadsheets to query the mdb and refresh the contents.  If you like, you can build forms to make it easier to keep the Access data up to date.  An alternative is to use the free version of MS SQL, which offers a better database (full use of T-SQL) but doesn't have the built-in forms development that Access offers. (However, an Access front-end can use a SQL back-end db.)

 

If the xls is just serving as a report and not being used for calculation, you could use Access Reports or SQL Reports instead of distributing the xls files. That way the reports would always be current and the spreadsheets would not need to have embedded queries.  Reports can be exported to xls if desired.

  • 0

I have to agree with sc302 here, why are you wanting to reinvent the wheel here.  There are lots of companies that do this for their bread and butter.. Something like sugarcrm which does have a FREE community version if you don't want to just license its use.  Salesforce, Zoho crm 2 other major players in the CRM market.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Get this massive 4TB WD_BLACK SN7100 SSD at a new all-time low price by Taras Buria Xbox owners recently received a new storage upgrade option, which allows them to equip the Xbox Series X|S with 4TB of extra space. That card, however, has an eye-watering price tag—nearly as much as the 1TB Xbox Series S. On the PC side, though, things are much better. Right now, if you need a 4TB SSD without selling a kidney, you can get the WD_BLACK SN7100 PCIe Gen4 SSD. It is now available on Amazon at a new all-time low price after a 16% discount (nearly half the cost of the new 4TB Xbox Storage Expansion Card). The SN7100 is a fast, reliable, and, more importantly, affordable Gen4 solid-state drive. Its speeds are rated up to 7,000MB/s sequential read and 6,700MB/s sequential write, while random input-output speeds are rated at 900K IOPS read and 1,350K IOPS write. These specs are not record-breaking, but they are fast enough for modern gaming (DirectStorage is supported), fast loading times in games and apps, and quick file transfer. The WD_BLACK SN7100 has a limited five-year warranty and endurance rating up to 2,400 TBW. You can monitor the health of your drive in the WD_Black Dashboard app on Windows. As for compatibility, you can use the SN7100 in any PC that accommodates M.2 2280 PCIe Gen4 drives, including PlayStation 5. It is compatible with PCIe Gen3 systems, but the maximum speeds will be limited. 4TB WD_BLACK SN7100 PCIe Gen4 Solid-State Drive - $249.99 | 16% off on Amazon US This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • Snagit 2025.2.0 by Razvan Serea Snagit is the most complete screen capture utility available. Showing someone exactly what you see on your screen is sometimes the quickest and clearest way to communicate. With Snagit, you can select anything on your screen – an area, image, article, Web page, or error message – and capture it. Then, save the screen capture to a file, send it to Snagit​'s editor to add professional effects, share it by e-mail, or drop it into PowerPoint®, Word®, or another favorite application. Capture and share images, text or video from your PC. Create beautiful presentations, flawless documentation and quickly save online content. The latest version of Snagit offers a totally new interface and workflow - making SnagIt easier for beginners to use, while still providing maximum convenience and flexibility for the screen capture experts. Snagit 2025.2.0 changelog: Edit Images from Camtasia Snagit can now be used to seamlessly edit images from Camtasia. Requires Camtasia version 2025.2.0 or later. In the Camtasia Media Bin, right-click an image and select the Edit in Snagit option. In Snagit, make your edits. When finished, click Send in the "Send changes to Camtasia" notification to replace the image on your Camtasia timeline. Step Capture Improved the Step Capture template to accommodate longer auto-populated step text. Annotations such as Callout or Arrow tool objects now anchor to and move with sections as sections are added to, reordered, or deleted from templates. Subscription Software Updated the Account dropdown to open automatically when new subscription related messages are present. Performance Improvements Improved the startup time for Snagit Capture application. Updates for IT Administrators Updated BouncyCastle.Cryptography package to address CVE-2024-29857 and CVE-2024-30172. Removed dependency on Xceed Zip. Updated LeadTools DLLs. Fixed an issue where Snagit might not recognize offline subscription activation. Fixed an issue where the video recording toolbar could be hidden from users when using Snagit in virtual environments or with remote desktop applications. Bug Fixes Fixed an issue where using Step Capture with the Share destination set to File in the Capture Window could result in data loss. Fixed a crash that could occur when capturing on some HDR monitors. Fixed an issue where the cursor object in a capture might include some of the background image in certain situations. Fixed an issue with the Box share destination to use the default browser for authentication. Fixed an issue where the Blur tool Color property could show when the blur or pixelate Type was selected. Fixed an issue where the privacy policy link in Snagit's installer might not open in the expected language. (PONRPD) Download: Snagit 64-bit | 419.0 MB (Shareware) Links: SnagIt Home Page | Release Notes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Markdown's creator weighs in on rumored Apple Notes export feature by David Uzondu The rumor mill is churning as we draw closer to WWDC2025, and one of the interesting developments being discussed is a report from 9To5Mac that claimed Apple Notes in iOS 19 iOS 26, will finally get Markdown export capabilities. This caught the attention of many, including the person who actually invented Markdown. John Gruber, the creator of Markdown, shared his thoughts on this potential new feature on his weblog. For those who don't know, Markdown, which Gruber developed back in 2004 with significant input from Aaron Swartz (RIP!), is a lightweight markup language designed for creating formatted text using a plain text editor. Its main advantage is that it is easy to read and easy to write. When the news first broke, some interpretations suggested Apple Notes would gain full Markdown support, transforming it into an application where users could directly type and see Markdown syntax, much like how specialized editors like Obsidian operate. These tools are intended for users to work directly within the Markdown framework for all their note-taking. Gruber himself indicated that he does not believe Apple Notes should become a full-fledged "Markdown editor," even as an option. He stated that such a change would be a "huge mistake." His reasoning is rooted in his original vision for Markdown and his view of Apple Notes' purpose. He reiterated that he initially designed Markdown as a "text-to-HTML conversion tool for web writers" and for contexts requiring plain text file storage. He feels Apple Notes serves a different, valuable role with its current WYSIWYG (What You See Is What You Get) rich text editing. This interface, he argues, is excellent for quickly capturing thoughts, particularly on an iPhone, and aligns with the Macintosh philosophy of user-friendliness. He pointed out that creating a syntactically incorrect markdown is trivial, whereas a malformed note should not be possible with Apple Notes. Despite his reservations about a complete Markdown overhaul for the editing experience, Gruber finds the prospect of exporting notes in Markdown format very appealing. He wrote that this specific capability "sounds awesome." He pointed out, quite rightly, that Apple Notes' current export functions are rather limited, primarily offering PDF and Pages document formats. Adding Markdown export would provide a much more flexible way for users, especially those in the "niche" he identifies with, to move their content out of Notes and into other applications. Gruber did express curiosity about how Apple might handle images embedded in notes during a Markdown export, as image handling can be a tricky aspect of Markdown.
    • What? Every single app I've installed from the Microsoft Store comes from its intended developer and works perfectly fine. What apps do you install?
    • Microsoft Store is such a weird place filled with so much absolute garbage and with reputable apps that somehow come from questionable sources. Like, the app name is known, the images back it up but the publisher is just some weird name that's not mentioned for the apps we know.
  • Recent Achievements

    • Week One Done
      CHUNWEI earned a badge
      Week One Done
    • One Year In
      survivor303 earned a badge
      One Year In
    • Week One Done
      jbatch earned a badge
      Week One Done
    • First Post
      Yianis earned a badge
      First Post
    • Rookie
      GTRoberts went up a rank
      Rookie
  • Popular Contributors

    1. 1
      +primortal
      419
    2. 2
      +FloatingFatMan
      182
    3. 3
      snowy owl
      181
    4. 4
      ATLien_0
      176
    5. 5
      Xenon
      139
  • Tell a friend

    Love Neowin? Tell a friend!