Database Replacement Solutions in Excel 365

Database Replacement Solutions in Microsoft Excel 365 with Power Query

~ All Applications lead to Excel. Why not just start there ~

Many businesses hire us to make what we term “Database Replacement Solutions” in Microsoft Excel 365, with Power Query. These are not Databases, as this is Microsoft Excel.

Why it works: The advanced use of Power Query, Excel Tables, Relationships, Primary Keys, Joins, those allow us to make a robust alternative to expensive, complicated, Access databases, right in MS Excel.

Size Matters: So many of our Access clients did not need to use Access, as their databases were so small. Microsoft Access is expensive to program, 5 to 6 digits. In these situations, Access was overkill.

What these clients needed was a database replacement solution based on Power Query, in Microsoft Excel 365.

Save Money: So if the need is small, then why not simply use Microsoft Excel 365 w/ Power Query to build a relational solution in Excel? One based on Tables, Joins, Queries, etc.

Excel based Database Replacement Solutions are Cost Effective:

Microsoft Access Databases projects start at $xx,000 to $xxx,000.
Database Replacement Solutions start at $x,000, can go to $xx,000.
HUGE Difference in cost. Same output – Tables.
1. Note: Access data usually finds its way to Excel for reporting and analysis.

Save Money: Relational Solutions in Excel 365 w/ Power Query for Business

~ Because it is so much easier this way ~

Contact us for a Free Consultation Today

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Power Query Data Form

Easily Edit, Append, Delete, or Duplicate Contact Records

Power Query Data Forms, are Powerful.

All database applications lead to Excel. Why not just start there.

Yes, Excel is not a database. I know, I wrote the post. But we can make it function like one. Our customers don’t care if Excel is a ‘database’ or not. Our customers care that we’ve solved their problem with an easy, robust, affordable solution. “Power Query Data Forms” do just that.

Power Query Data Forms at Excel and Access, LLC & Associates, a new method of Excel development.

Tom Nevitt

I have had the absolute pleasure of working with Christopher from Excel and Access LLC on our new membership Excel database.

The solution is incredible – far more advanced than I had ever thought possible for Excel.

Not only is the team at Resolve saving lots of time, but we are picking up on leads we would have missed and seeing a tangible impact on our bottom line.

The entire service from Christopher has been flawless.

He is very patient, he knows how to explain things clearly in the right level of detail depending upon the user, and is the ultimate Excel guru.

We really could not be happier and are so grateful for all of Christopher’s time and efforts that have gone into this solution – particularly with our last minute changes!

HIGHLY RECOMMEND

Microsoft Excel is NOT a Database

I know, I wrote that Blog Post. While Excel is not a database, you can make it work like one.

We are not saying that Excel is a database, rather we are saying if you leverage Power Query, you can make it function like one.

How a Relational Solution in Excel 365 Works

~ Simple, you create relationships between Tables, just as in Access, SQL Server, Azure, etc. ~

~ You write Queries based on Joins ~

It is not that hard to setup, as you will see. If you understand relationships, if you know Power Query, if you understand data, you can do this.

( Yes, this is a simple example, so that you can easily follow it )

Simple, you create relationships between Tables, just as in Access, SQL Server, Azure, etc.

Setting up the File: In Microsoft Access, Microsoft SQL Server, Microsoft Azure, Microsoft Excel/Power Query, or other Database, you start with the Tables.

In Microsoft Access, you start with a blank file, then you begin adding normalized Tables.
In SQL Server, first you must setup the new database, do all of the DBA stuff, and then you can start to add normalized Tables.
In Excel you start with a blank file, and you add normalized Tables.
All are pretty much the same thing, Attributes and Data Types.

In Microsoft Excel 365:

Create one or more Tables.
1. Identify the Primary Keys in your Tables.
Upload your Tables into Power Query
Write Queries.
1. Save as Connection Only.
  1. Reference via other Queries, like a Select Query in Microsoft Access
2. Output as Tables to Sheet, as needed, like an Action Query in Access ( Append Query ).
  1. Power Query Tables, Refreshable
  2. Pivot Tables, Refreshable
Write Reports
1. Write reports directly in Power Query, or output as a Table and allow onsheet functions to do that.
  1. Use PIVOTBY, GROUPBY, LAMBDA: To create single-cell, single-function reports.
Optional: Create Data Entry Forms [ Keep the users out of the data Tables ~ VeryHidden, and protected ]
1. Use UserForms
  1. At Excel and Access, LLC we’ve deprecated old-style User Forms because they are slow, require excess programming, and their Active X controls are being sunset by Microsoft.
2. Use Onsheet Forms
  1. We do not recommend this in most situations, just use Tables.
3. Use Tables as Forms ( Most robust & Stable of the options available )
  1. At Excel and Access, LLC we use both Excel Tables and Power Query Tables as Data Manipulation Forms
    1. Excel Tables for New Records, works like an Append Query in Access.
    2. Power Query Tables to Edit Existing Records, works like an Update Query in Access.
      1. Lately we have merged both methods into the same Form.

Add use of VBA, M-Code, Office Scripts, etc., as desired.

Allow the user to easily set defaults, which will be used in Power Query.

A primary key is a column or a set of columns in a database table that uniquely identifies each row

If your data does not have a Primary Key, no worries, they are easy to create.

Simple demo, clearly shows how to do this.

Easy to follow demo, give it a try

Demo Filtering to Null, interesting.

Use Data Entry Forms – The Users are not allowed access to the Tables:

If you want to allow the user to manually enter data into the system, use Forms.

Microsoft SQL Server does not have Forms.
Microsoft Access uses Forms, to allow a person to enter data into Table(s).
In Microsoft Excel you can do pretty much the same thing as in Access, using Excel UserForms. Both are labor intensive to program, both take huge amounts of vba.
We recommend either doing onsheet Forms, or Forms based on Tables/Queries. Yes, Forms based on Excel Tables or Power Query Tables.

The Output is the Same – Tables Tables Tables

No matter which Microsoft Database you use ( Access, SQL Server, Azure, Power BI), the output will be the same, Tables.

The Custom Excel 365 w/ Power Query Database Replacement Solution will provide the same exact output, Tables.

Databases are built on Tables. Look inside QuickBooks, even that is Tables based.
Databases are based on Normalized Tables.

Database output comes in the form of Tables;

MS Access allows Excel to access to Tables.
SQL Server allows Access to access Tables.
Power Query allows users to access Tables.

Tables Tables Tables

Everything is Tables based. Simply create relationships based on Primary Keys, apply Joins, write your queries.

The output from Access will be the same as the output from Excel. But most likely, the output from Access will be sent to Excel.

We are here to discuss your needs live, is a Database Replacement Solution what your business needs?

Let’s schedule a Zoom call to discuss live, as we offer free consultations. We will look at your current files, discuss your needs. We can then show you demos that pertain to your unique situation. We will show you exactly how this works. You will see how simple it actually is. We will discuss the cost.

Or do you need a full-blown Access database? How big is your need? How big is your budget?

If you need an Access Database, contact Armen at J Street Technical

But, if you are like the others, you will be very excited and you will be very interested in discussing this further.

We will answer all of your questions, provide education, and then you can decide if this is the best solution for your needs and budget. Maybe a full-blown Access database is exactly what you need, maybe it is not, we can help you decide. We know both.

We are here to help.

Hire Excel and Access, LLC 877-32-3539

Conclusion – Custom Database Replacement Solutions in Excel 365, with Power Query

~ Save Money ~

If your need is not large, then consider using a database replacement solution, built in Microsoft Excel 365, w/ Power Query. For small to medium needs, it is a viable alternative to an expensive Access database.

We have been building these for several years now, we have an amazing team of experts focused on this

Contact us for a Free Consultation Today – Hire Excel and Access, LLC

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Coming Soon: Case Study: Database Replacement Solutions

Microsoft Excel 365 w/ Power Query Database Replacement Solution, replace Microsoft Access database that we built for this client 10-years ago. We are relacing an Access database that we built, with Microsoft Excel 365 and Power Query.

The Excel version is fully integrated with QuickBooks, Read-Write abilities, to the QuickBooks Tables.

The Access Database that we replaced. We also built that database.

Power Query Data Form 1

This Power Query Data Form is used to allow the Admin to Review, Edit, and Append desired records. Thes records are then available to the users of the file.

Power Query Data Form 2

This form allows the user to select up to four records for testing. The results are sent to the next form.

Power Query Data Form 3

The provides form populated this form, now the user can enter the test data, hit append, and they are done.

Power Query Data Form 4

Once a record has been Approved and Calculated, it can be Duplicated, Revised, or Deleted, easily, using the Power Query Data Form.

Power Query Data Form 5

If you want to add a new record, without the defaults and such, here is a simple form.

DAF Status Report

I love the GROUPBY Function, I use it all the time, it allows me to show the Status of records in the Tables. Users need to see this.

Other Tables

I use Mark’s EOTG Add-ins. The one that allows you to use a Table, for file paths, my favorite. It is in every file I develop.

Checkout EOTG for Tables, Power Query, Dynamic Array Functions and more. That is where I learned everything new in Excel 365.

Excel Tables – Always Excel Tables

That is where all of the data goes, always.

If your data is in proper normalized Tables, then you can use it just like a small database. Yes, Excel is not a database; but we can make it function like one.

Post coming soon, once the solution is complete. Fully integrated with QuickBooks, concurrent users, database replacement solution. Excel has changed. So should Excel programming and development.

Elizabeth Hand

16:38 15 Jul 21

The team and Excel and Access are very efficient, friendly and professional. I'm confident that when I send a project to them, it will be done quicker than I expected it and done perfectly.Thanks!

BC Synapse

23:32 02 Feb 21

I arranged for two 1.5 hour sessions on Microsoft Excel Tips and Tricks sessions for my organization in January 2021. I am 100% satisfied with the service. Chris and Maria were very responsive to my initial inquiries and helping me with setting up the virtual sessions. This company provide consulting services on various applications and I liked how I was able to customize my sessions by selecting the tips and tricks to be shared- it was 100% customizable.Our trainer, Bob Umlas, was superb in the delivery of both sessions. He provided an Excel workbook and shared his screen to show all the tips and tricks on how to use Excel more efficiently. The sessions were also recorded so that staff could rewatch the lessons again at a later date. Overall I was very please with the company's services and would work with this company again should future opportunities arise.

Christine Williams

20:52 01 Feb 21

These guys are the excel experts! They take our complicated needs and turn them into easy tools that we can use and run on the daily basis. Quick turnaround as well, the latest project we needed help with they completed and had back to me in 24-hours!

John Gebbia

22:45 18 Dec 20

Excel and Access was a great pick for our excel training needs. The training class was very easy to follow and Bob our instructor was very knowledgeable. Would definitely recommend and will be going back to them for future training needs.

Kelly WILKEN

20:01 03 Dec 20

Jacob was fantastic to work with. I was tasked with developing an easy way to track our Corporate Training across all departments and positions based on company requirements and hire date. I knew there had to be a way to do it in Excel with formulas, but it was way above my skill set. I contacted Excel and Access, and they listened to my needs and paired me with Jacob. He was very responsive and was quickly able to come up with precisely what I needed. He was great at making the necessary tweaks, including creating a “GENERATE” button in bright red so that I couldn’t miss it. I can now run all of my reports with all of my different conditions with the click of that big beautiful red GENERATE button. I will definitely reach out to Excel and Access and Jacob for my future needs.

Steven Kurtz

20:12 22 Aug 20

I'm not Pepsi, Amazon or any of their big clients...just a guy with a small, boutique private psychology practice 3,000 miles away in NY. Every time I need these guys they are there...got my project started ASAP, turn around time was amazing and best of all, any and all tweaks were handled ASAP until we got all the bugs out. Admittedly I was afraid we'd settle on a price and I might not be able to make edits. But no worries - each and every edit, debug done with a virtual smile and ASAP. Well worth the money and they won't disappoint. A pleasure to write a positive review for them!

Jerelene Tan

00:18 24 Feb 18

I have engaged Jacob to help me with an Aging Report. He quickly provided me with a solution which I didn't think of. I would highly recommend his service to anyone who needs excel help.

Demetrios Lambrou

03:30 17 Oct 17

I have been assisted once again, by Paul this time, quick and effectively, even though it has been a weekend. So many thanks!!!! Such a great support to Excel users and Excel programmers alike!

Mitch Goins

15:06 01 Aug 17

I had an Excel problem that I could not crack without extreme effort on my part. I contacted Excel and Access via email. I received a prompt response then described my problem and indicated that I really wanted to remunerate them for their (Christopher and Jacob) time and effort. They honed in on my problem description with only 2 very helpful emails that showed me how to solve my problem. I was very impressed and grateful. There was never a mention of any charges. They are consummate professionals. I can see why they are Microsoft MVP's.

Michael Bell

12:22 09 Jun 17

Chris and Paul were amazing. We hit them with a project last minute and they dropped everything and came through within hours. The work was done professionally and exactly as we needed and at a very reasonable price. We were very pleased.

Bret Linford

20:16 30 May 17

Excel and Access, LLC were super responsive. They really went above and beyond my expectations. They were able to customize directly to my needs. The automation they delivered is very fast and easy to use. I'd use them again in a second. AND they're nice people, too. ?

Chris DeSantis

19:10 07 May 17

Our experience with Excel and Access. LLC was excellent. Chris and his team of very skilled professionals were able to help us out immediately. After just a brief discussion of the project we hired them for, they completed the work and exceeded our expectations in just hours. I have used Chris and his team on numerous projects over the span of 10 years. I am consistently impressed with how quick the payback is, and how well his team understands my requirements and delivers a product that exceeds my expectations. His team is always available anytime of day or night to help or answer questions. We highly recommend Excel and Access, LLC for anyone searching for highly effective and easy to use productivity tools.

Jacqueline

13:10 21 Mar 17

I had the pleasure of working with Excel & Access on a very intricate Excel project involving a massive spreadsheet that housed years’ worth of data. After having two other firms take on the project and then bail, I was panicked and desperate to get the project completed in what was now a very tight time frame. I worked with Christopher and Jacob and couldn’t be more delighted with the level of customer service I received. Jacob is extremely knowledgeable, and also incredibly responsive. My e-mails were answered immediately and the project was completed quickly, ahead of my deadline, thanks to his working nights and even over the weekend to get it done. I felt as if I were the only client he was working with at the time! Jacob made a difficult, tedious assignment look like child’s play. Excel & Access are fabulous to work with and are now my go-to for any Excel need, large or small. Good customer service is difficult to come by, but they nail it! If you are looking for help with an Excel project, I recommend Excel & Access without reservation. You won’t be disappointed.