Excel as relational database: How to mimic an Access database

**joeystraw** · 01-09-2019, 01:02 PM

My organization (government health care agency) has decided to remove all user-created databases within our network, leaving me to find an alternative replacement with current available and IT-approved applications. Excel 2013 seems to be the only application currently available for use as a replacement. After doing a lot of searching, I've found forums that describe creating relational databases in Excel (which I began to create) but I'm not understanding how to practically apply that information to create something that at least basically mirrors how Access handles data.

I've setup a primary key and a main table and have multiple secondary tables with related data and created a 1 to many relationship just as I did with Access.

My confusion is how is data entered into the secondary tables and how to query this information for analysis and reporting functions. In other words, Access utilizes forms with bound objects that are linked to fields and records in a table.

Below is the basic construction of the tables with their respective primary keys and fields:

Primary table [tblMain]: Fields > [PersonalID] (tblMain Primary Key) [LastName] [FirstName] [DOB]
Secondary table [tblReferrals]: Fields > [ReferralID] (tblReferrals Primary Key) [PersonalID] [Bed#] [DateAdmit]
Secondary table [tblTransfers]: Fields > [TransferID] (tblTransfers Primary Key) [TransferDate] [TransferLocation] [ReturnDate]

How could I go about creating something similar to Access?

Thanks in advance for any insight or direction that you could lead me toward to accomplish this.

**dflak** · 01-09-2019, 03:40 PM

If you can develop a sample workbook with some mocked-up data it would help a lot.

Step 1: Use Excel Tables. Excel tables know how big they are and copy down formulas automatically. You can also use column headers in both formulas and VB code. This makes it easier to debug the formulas or code. Intellisense will help a lot when you have tables defined. Here is more information on tables to get you started: http://www.utteraccess.com/wiki/Tables_in_Excel

Step 2: Put all information on a common drive. This is especially important if you have more than one person working on the project. One big disadvantage of Excel when compared to a database is concurrent users. Excel locks at the file level. That's analogous to locking at the table or even database level in a database. True databases only lock at a record level which means that two people can work in the same table at the same time.

Step 3: Learn Excel User Forms. Excel user forms look a lot like Access forms but you have to do a lot more programming to get things to work. However, forms will give you more control over data entry.

Step 4: Consider saving data in a CSV format. There is an application in Excel called MS-Query. You can set up a data connection to a directory that contains text files. Then you can use these files like database tables. There is an interface that is similar to the one in Access to build queries. However, it isn't drag and drop like Access. But at least you'll be able to pick specific columns and use where clauses to limit the data. I've never attempted something like a join against text files. I'll have to try that sometime. However, VLOOKUP works a lot like a join.

Another advantage of having data in individual text files is you can pick and choose what data files you want to include in a report. You will probably have some "tables" that are used in multiple reports.

Step 5: Composite Keys. The simplest way to do this is with a concatenation. For example =[@[Last Name]] & ":" & [@First Name]] & ":" & [@[DOB]]. This will produce something like Smith:John:17993. Where the 17993 is Excel's numeric value for 4/5/1949. It doesn't matter that it doesn't look like a date. The colon really isn't necessary, but I like to throw it in to make the key more human-readable.

That's what I can think of off the top of my head. It's unfortunate that your bosses are throwing out databases since they are generally better at this kind of thing. However, Excel can be pressed into service as a database if the tables are simple enough and the relationships are simple enough. Download your existing data as CSV files!

Attach a sample file, I'll be willing to show you a few tricks I've learned along the way.

**joeystraw** · 02-20-2019, 05:30 PM

Thank you dflak. I'm been away since my original post and just now responding to your recommendations.
See attached mock data. I have created 3 tables with relationships to each based on the 'Main' table's primary key (PersonID) and a related table ‘Referrals’ which have a one to many relationships with the tables Custody, OTC and OTM. Here is what I'm hoping to accomplish as time is running out to convert to Excel (Access is being organizationally removed soon):

Data Entry form with drop down lists in various fields to create new entries and edit existing records.
Show a current census list with names, ID numbers, and date of admission of each person.
Generate a ‘Status’ report showing totals and subtotals based on queries of the data.
Use queries to create trending data such as referral, discharge, and admission counts in a given period.

Thank you in advance for any assistance that you can provide. If I’m not clear in any way regarding my goals, please let me know. Hopefully this is do-able as I’m very limited in availability of application resources in my organization.

**dflak** · 02-21-2019, 05:14 PM

I got some work done on this. I made two forms:
1. To lookup patient information using last name and date of birth
2. To find date of birth based on last name.

I will work on a form to add new patient information.

I will need work on what you want to do with the other tabs. I assume that you want to find records based on a person's ID or add new records. With up to 49 fields, the form can get kind of long. Also, how many records do you expect for each patient in these tables and how do you want to select the one you want? I will assume that it will be on a combination of Patient ID and Referral date. From there we can get the referral number and link to the other tables.

It is too bad you do not have Excel 2016, you can use tables as data sources and then write queries (with joins) off them.

I'm going to explore this a bit further. I think I could do something similar in 2013.

**joeystraw** · 02-22-2019, 06:26 PM

The other tabs mirror what I have in the Access database: a main table with basic demographic information to identify each person. This table has a primary key as a unique identifier. The other tables have foreign keys that link them to a person listed in the main key. I created relationships between the various tables again just as I have in my current dB. To give you an idea of what I have now and hope to have something somewhat functionally similar, I've attached 2 screen shots, 1 of the main page ('Compass') showing the current census list with some additional demographic info and the other image is what opens when you double-click on a person's name from the Compass. This is a form ('Patient Data') has all the fields from the previously mentioned tables linked via a Primary key.

I'd like to be able to switch from a list of current patients to a detail form and back again easily if possible similarly to how I have the Access dB setup. Hope this isn't asking too complicated and labor-intensive; I'll understand if these requests sound unreasonable or impossible.

Thanks again.

**dflak** · 02-27-2019, 02:36 PM

I did some consolidation on how to add / lookup / select a patient. I am ready to take the next step which is to automate the referrals.

My plan for this is to display a list box with all the referrals for the patient. There will be a button to add and a button to edit the records. Either one will bring up a form with the 49 fields listed on it. However, there is only so much real estate in the listbox to display data. What fields do you want to display in the "abbreviated" version to show for selection?

Also, the second picture in the previous post seems to be "cut off." I am assuming that you would like me to get the referral form to look like this as much as possible. I see some drop-down information on these forms. These will be fed from other tables that you will have to maintain, but if you have some sample data, that would help a lot.

**joeystraw** · 03-01-2019, 05:08 PM

Dflak,

First of all, thank you for your patience and willingness to help me in my situation. Thought it might be helpful to provide additional information to you to help with the design considerations and your
recommendations.

1. The PersonID number
a. This is a number that is predetermined by another system and is the primary identifier (or Primary Key) of the person. In other words, this number can’t be auto generated, I must use the number already assigned.
ReferralID numbers can be auto generated (see graphic below).

2. Start Page
a. I would like to have the primary opening worksheet to be a form that lists the complete current inpatient census (in other words, any referrals that have been accepted and admitted but not discharged). Kind of a bird’s eye view of all patients (‘active patients’) that are currently being treated. This list would have some basic information in a table format (see the Compass View jpg I previously sent).

3. Patient Data Detail
a. From that form, I would like to go to any active patient in this list to another form that has additional details (if possible). See the ‘Patient Data’ form jpg previously sent and the simplified graphic below showing the census list and clicking or selecting a person which opens up a person-specific page.

4. Table relationships (One to Many)
a. Each person can have 1 or more referrals. Each of these referrals will have a related tables named Custody, OTC, and OTM. As mentioned, the PersonID number is already established and cannot be auto generated or created at the time of data entry. The ReferralID numbers should by generated to identify distinct referrals (the person may have been referred many multiple times).
b. Each of these unique referrals have their own tables that related to them. See below.

As far as your questions on the 49 fields, I've trimmed them down somewhat in the last spreadsheet you sent me (changed the 'Headers' tab and replaced the respective columns on the Referrals and Custody tabs.

Addressing the drop down fields, I created a PowerPoint to show you the lists of any of the fields that have drop down lists. The complete lists are at the bottom of each page.

Again, I am very appreciative of your assistance. I know that what I am presenting is complicated (from my view) and am grateful for your expertise.

**dflak** · 03-01-2019, 05:29 PM

I hope that patience is a two-way street. This is a rather large project and I prefer to work with it when I have some contiguous time to do so.

I am shooting blind here because I have not yet looked at the materials. I am going on a verbal description.

Personal ID number -manual entry - got it

OK, I can start with an active referrals sheet.

I'll have to look at the patient detail form - I can go about this two ways. If a patient has two active referrals I can list the patient twice and let you select the record ... or ... can list that the patient has a referral and then launch a second form to select which one.

I can picture that on the "master referral sheet" I will have additional windows showing for Custody, OTC, and OTM.

I will do what I can, but I have a busy weekend scheduled and all my end of month reports will come home to roost next week.

Tell your boss that this really should be done in a database and that an expert told you so!

**joeystraw** · 03-01-2019, 05:47 PM

Yes, no rush from me at all. And if I could strong arm these administrators for continued use of Access, I would. Please keep in mind when designing this that I manage a unit that has 45 beds with patients. I need to see the current census daily (so probably a query-based list like my dB uses now) to filter only current patients (referrals that were admitted and not discharged). I guess that's what you mean by 'Active' referrals...that makes sense.

As for patient with 2 referrals, there will never be a time when there are more than 1 active referrals on a single patient. If they were referred previously, they were either accepted and admitted or rejected outright. The rejected referral becomes archived. The active one is only one that is in the current census.

Hope this all make sense.

Have a great weekend and I'll wait for your update when you are available.

**dflak** · 03-11-2019, 09:45 AM

I finally got a chance to digest what you sent me last week. I got the lookup tables entered. Also I started on the referral sheet.

Here is what I have to do.
- If the referral appears on the list, you can select it and update it. Then I need to fill in the sub-forms (not yet developed) with the information for that referral.
- However I also need to make provisions to add a new referral either for an existing patient or a new patient. I can tap into the forms that I already created for that. I will also compute the referral numbers automatically.

I also made it so when you add a new patient, the patient ID is entered manually and not computed. I also added checks to make sure you add all required information.

**Kyle123** · 03-11-2019, 10:10 AM

As an alternative approach (understanding the common reasons Access is removed from within an organisation), could you ask the IT department to migrate the backend to SQL server? This is usually straightforward and you may find that they are much more amenable to having only the frontend distributed (that you wouldn't need Access installed for).

**CK76** · 03-11-2019, 10:19 AM

I second Kyle's suggestion.

Excel isn't really meant to be used as relational db and it will cause more headache down the line than what it's worth. Especially when data integrity and multi-user is considered.

It would be best if your IT can migrate backend to MS SQL (even Express), but it can work with other open source dbs. Such as MySQL, MariaDb, Postgre (though this one is bit more tricky).

You can still use Excel as frontend, and use database as data store.

Edit: If above isn't an option, may be IT is open to single Access DB with data store, and another with only frontend. You can store Access in secure network drive. Then only have admin group have access to full Access. Others can access DB through Access frontend (users only need runtime, rather than full db/application).
See link for article on splitting Access DB to front and back end.
https://www.fmsinc.com/microsoftacce...abaseSplitter/

**Richard Buttrey** · 03-11-2019, 10:31 AM

Have you considered using one of the fields in the Primary Table to lookup EVERY field in one of the various secondary tables so that all fields then exist in the Primary Table which then becomes a very big database.
The field to look up might be a field that is entered directly in the primary table data or a field that has been populated by an earlier lookup formula in the database.

For instance you could lookup the Personal ID from the Primary table in the tblRefferals to then grab all the fields from the tblreferrals

Maybe you example is a bad once since I couldn't see any common field in the tblTranfers table that mapped to either the tblMain or tblReferrals. Obvously it's going to be necessary to be able to have a common field in at least two tables so that there's a path that can connect every field back to the database fields, whether directly or indirectly through a secondary table.

**joeystraw** · 03-11-2019, 02:33 PM

Thank you Kyle123 and CK76 for your input and recommendations. The organization here is not very progressive nor open to alternatives to my dilemma and our local data management needs. MS Access availability has been granted to me on a temporary basis only pending an alternative solution and setting up a backend is not an option due to the lack of local IT application support.

I am forced to develop a systematic method of handling data with the ability to query this data for trending, reporting, and quality management responsibilities, which is why I sought the expertise of ExcelForum. The organization chiefs want this data management yet do not provide an organization-wide solution that will meet these demands, so I am stuck trying to develop a solution that utilizes supported applications (namely MS Office without Access dB).

To give you an idea of the situation, we've been running on Win7 Enterprise for the last 3 years with Win10 to be upgrading within the next couple of weeks. Once upgraded, Access will not be part of that new OS package.

dflak has been so kind as to provide his Excel expertise to assist me which I am very grateful for.

**joeystraw** · 03-11-2019, 02:38 PM

Thank you for your input Richard. In 1 of the attachments in my post dated 3/1/19, there's a pdf file named 'Proposed Forms' which has a graphic at the bottom of the page showing the tables and their relationships to each other.

Not sure if I communicated that well in this visual or if there are errors in the relationships, but this is basically how these tables are connected.

**joeystraw** · 03-11-2019, 02:42 PM

Thank you dflak, just looking at it right now to see the work you've done. Thank you so much.

**dflak** · 03-12-2019, 04:54 PM

I now took it one step further. When you select a referral, it now fills in some of the details on the form. Use Marge Simpson (Referral 120) as an example.

I need your help. I need a "map" between the fields on the forms and the headers in the Referral Table. I did not know this for some of the fields which is why they show: "Not Found."

I am not too sure on how to deal with attachments. How to look them up and how to add them. Somehow they will have to be saved with file names referencing the referral ID. I'll table this thought

Right now I need to develop the rest of the form and get the fields matched to the column headers. Also I have to initialize the drop-down boxes.

Another "out there" is to look up prior referrals, but that might not be as difficult as it looks now.

Excel as relational database: How to mimic an Access database

LinkBack

Thread Tools

Rate This Thread

Display

Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Re: Excel as relational database: How to mimic an Access database

Thread Information

Users Browsing this Thread

Similar Threads

Import relational Access database into Excel 2007?

Excel Data connection to Access database restricts exclusive access to database

Import Excel to Relational Access Database

Excel Add-in to Create a Relational Database

Convert Excel Flat File Into Relational Database

[SOLVED] Excel as relational database -> data rows

[SOLVED] Excel query of Access database - changed database filename, now error

Bookmarks

Bookmarks

Posting Permissions