• 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

    • Ponies will finally have good games to play after replaying Last of Us for the 100th time. Oh and I lied, Silent Hill f looks pretty great too, but we already knew about that.
    • China blocks Apple-Alibaba AI venture in retaliation for the US trade war by Hamid Ganji iPhones sold in China, Apple's second biggest market, still lack AI features. While Apple tried to solve the issue by forming an AI venture with China's e-commerce giant Alibaba, the move has faced setbacks from China's regulator, presumably to get back at the US trade war under the Trump administration. According to a new report by Financial Times, citing people familiar with the matter, Apple and Alibaba have been working on their AI venture over the past few months, hoping to bring some AI features to iPhones sold in China. However, the Cyberspace Administration of China hasn't approved the collaboration. Every new iPhone sold worldwide has built-in ChatGPT as a result of the Apple and OpenAI partnership. Since OpenAI has no official presence in China, Apple must partner with local tech companies like Alibaba to offer AI capabilities on iPhones sold in the country. The move could help Apple navigate China's regulatory restrictions, but it's now stalled due to the US-China trade war. The Cyberspace Administration of China doesn't publicly confirm whether halting the Apple-Alibaba AI venture is a response to the US trade war. Still, sources claim this is China's response to the recent tariff clash with the US. China also has a pretty solid record of retaliating against the US reciprocal tariffs. However, the Apple and Alibaba AI partnership also has some opponents in the US. Lawmakers and government officials in Washington have raised concerns about the AI deal. They fear that this collaboration could significantly bolster China's AI capabilities.
    • Raspberry Pi Imager 1.9.4 released bringing performance improvements, bug fixes and more by David Uzondu Raspberry Pi Imager 1.9.4 is now out, marking the first official release in its 1.9.x series. This application, for anyone new to it, is a tool from the Raspberry Pi Foundation. It first came out in March 2020. Its main job is to make getting an operating system onto a microSD card or USB drive for any Raspberry Pi computer super simple, even if you hate the command line. It handles downloading selected OS images and writing them correctly, cutting out several manual steps that used to trip people up, like finding the right image version or using complicated disk utility tools. This version brings solid user interface improvements for a smoother experience, involving internal tweaks that contribute to a more polished feel. Much work went into global accessibility, adding new Korean and Georgian translations. Updates also cover Chinese, German, Spanish, Italian, and many others. Naturally, a good number of bugs got squashed, including a fix for tricky long filename issues on Windows and an issue with the Escape key in the options popup. Changes specific to operating systems are also clear. Windows users get an installer using Inno Setup. Its program files, installer, and uninstaller are now signed for better Windows security. For macOS, .app file naming in .dmg packages is fixed, and building the software is more reliable. Linux users can now hide system drives from the destination list, a great way to prevent accidentally wiping your main computer drives. The Linux AppImage also disables Wayland support by default. The full list of changes is outlined below: Fixed minor errors in Simplified Chinese translation Updated translations for German, Catalan, Spanish, Slovak, Portuguese, Hebrew, Traditional Chinese, Italian, Korean, and Georgian Explicitly added --tree to lsblk to hide partitions from the top-level output CMake now displays the version as v1.9.1 Added support for quiet uninstallation on Windows Applied regex to match SSH public keys during OS customization Updated dependencies: libarchive (3.7.4 → 3.7.7 → 3.8.0) zlib (removed preconfigured header → updated to 1.4.1.1) cURL (8.8 → 8.11.0 → 8.13.0) nghttp2 (updated to 1.65.0) zstd (updated to 1.5.7) xz/liblzma (updated to 5.8.1) Windows-specific updates: Switched to Inno Setup for the installer Added code signing for binaries, installer, and uninstaller Enabled administrator privileges and NSIS removal support Fixed a bug causing incorrect saving of long filenames macOS-specific updates: Fixed .app naming in .dmg packages Improved build reliability and copyright Linux-specific updates: System drives are now hidden in destination popup Wayland support disabled in AppImage General UI/UX improvements: Fixed OptionsPopup not handling the Esc key Improved QML code structure, accessibility, and linting Made options popup modal Split main UI into component files Added a Style singleton and ImCloseButton component Internationalization (i18n): Made "Recommended" OS string translatable Made "gigabytes" translatable Packaging improvements: Custom AppImage build script with Qt detection Custom Qt build script with unprivileged mode Qt 6.9.0 included Dependencies migrated to FetchContent system Build system: CMake version bumped to 3.22 Various improvements and hardening applied Removed "Show password" checkbox in OS customization settings Reverted unneeded changes in long filename size calculation Internal refactoring and performance improvements in download and extract operations Added support for more archive formats via libarchive Lastly, it's worth noting that the system requirements have changed since version 1.9.0: macOS users will need version 11 or later; Windows users, Windows 10 or newer; Ubuntu users, version 22.04 or newer; and Debian users, Bookworm or later.
    • Ancient CD app makes 64-bit comeback to support Windows 11 and probably Windows 10 too by Sayan Sen Remember when CDs or compact discs were a thing? While technically, they still are, their popularity and usage have dropped immensely with the rise in other standards like USB, as the latter continues to evolve, getting faster and gaining more features. Recently, Microsoft enforced some mandatory requirements for USB Type-C so as to ensure a uniform and consistent experience for Windows 11 users. On the topic of Windows 11 and CDs, a CD ripping tool from the Windows 95/98 era, dubbed "CD2WAV32," is back again after 16 years (from the Windows 7 era). The utility has now been updated to work on Windows 11 version 24H2, which is pretty cool. This was not planned, says the author, as they simply wanted to test the app on their newly upgraded Windows 11 PC, but ended up going all the way to make it fully work on Windows 11. Their Windows 11 runs an AMD Ryzen 9600X, 64 GB RAM, and an Nvidia GT 1030 (miswritten as "GT1300"). The developer of the tool notes that they did not run thorough tests on Windows 10, but it works on their Atom-based PC, which is another relic, given how fast technology moves. The author writes (Google-translated from Japanese to English): "From now on, it will only support Windows 11 (24H2). The reason is that this is the only environment the author currently has. I haven't done anything particularly fancy, so I think it will work properly on Windows 10, but I can't guarantee it. All I have left is an ATOM machine that I bought a long time ago that also runs Windows 10, so I've seen that it works lightly on that, but I can't do a detailed test." Atom, for those wondering, was Intel's low-power CPU lineup that it decided to axe back in 2016. The story is similar to how Microsoft gave up on Windows Lumia, as Intel, too, abandoned its mobile chip ambitions once the likes of Qualcomm and MediaTek took over. In terms of the underlying changes, the utility has been compiled now on Delphi 12.1 Community Edition, which is used to make native Windows apps as well as ones for macOS, iOS, and Android. The recent update also brings a significant overhaul in terms of compatibility as well as UX/UI. File sizes and other such metadata are now handled using a 64-bit format instead of the prior 32-bit approach, eliminating overflow issues and ensuring large file and disk space values are displayed correctly. This change is necessary given that large storage volumes are quite common these days. Additionally, support for 16-bit code calling functions has been entirely removed as Windows 11 is 64-bit only; thus, features like MSCDEX and TwinVQ compression are gone. Meanwhile, the font has been changed from MSP Gothic 9pt to Meiryo 10pt, so readability should not be a problem even on 4K screens. In terms of audio file encoding support, it is said to work with MP3 as well as WMA. So, should you download and run it? Probably not, given that the UI is entirely Japanese, but it is still a fun project to look at.
    • Xbox has lots of games… and there all coming to Playstation!
  • Recent Achievements

    • 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
    • First Post
      James courage Tabla earned a badge
      First Post
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      397
    2. 2
      +FloatingFatMan
      177
    3. 3
      snowy owl
      170
    4. 4
      ATLien_0
      167
    5. 5
      Xenon
      134
  • Tell a friend

    Love Neowin? Tell a friend!