Relationship/Table linking suggestions/help

sw31

New Member
Joined
May 10, 2011
Messages
5
Hi all,

New to the board, but not to the woes of Access database structuring issues.

Here is my issue, and I thank you for any input:

I am making a database that is based on drug trends- whats new, whats going generic, how much they cost, so forth. This is essentially a fusion of a number of excel spreadsheets in various forms of disarray.

I am using the drug's generic name as the common field, with a caveat. There are many common names across all files, but some have unique entries. Just a quick example:

File A has drug1, drug2, drug3, drug4
File B has drug1-4 AND drug 5, drug6, drug7
File C has drug2-3 AND drug 7

As you can see, it's a mess; this is the only common file to work with, but each 'common' field is slightly different from the other.

So to my question (finally): what approach is best?

Somehow massaging the data to make a 1-many relationship, and link tables accordingly

Create a linker table, and use a many-many relationship

Another clever idea I've yet to think of.

I welcome your input, many thanks.

sw31
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I'd probably want to Pull/Push the data into a single table. If necessary, use an ID field to "flag" the original data source. Then you will have a "data warehouse" to work with.
ξ
 
Upvote 0
I am having problems with this still-- how do I go about the push/pull? I am trying to normalize the data in order to create some sort of unique indentifier, but when I set up a relationship, nothing seems to happen if I do a test query.
 
Upvote 0
By push/pull I mean either push it all into a single table or pull it all into a single table -- it's relative to which side you looking at it from whether that's "pushing" or "pulling".

Creating this cleaned up and centralized data store might help because your data as it stands seems rather chaotic and hard to work with (based on your short description).

--------------------------

As far as unique identifiers go, I can't help you. If the name of the drug (drug1, drug2, drug3) is not good enough then you're probably in trouble. You'll have to spend time cleaning it up or mapping drugs to IDs of some kind or another.
 
Last edited:
Upvote 0
It's a mess; this future database will be the amalgamation of a number of separate excel files from a number of people using different naming conventions that are loosely associated by a drug name. I literally have no column that is 100% in common with all files.

Enough of the winging and whining; thank you again xenou for the advice.
 
Upvote 0
Follow up question: this database will be dynamic, in that it will be updated monthly. Does this affect my approach, or will Access be able to handle updates via query?
 
Upvote 0
Well, my strategy (you must take it with a grain of salt though - call it a suggestion) would be to create a "data warehouse" of information. This basically means that you will load new data into your data store with a modicum of control - the idea being that you can clean it up and sort it out when you put it into your database. In this type of scenario, you'd probably take the various excel spreadsheets at the end of the month and pull out the data, make sure its good, clean it up as needed (perhaps here addressing the problems with drug names), and then push it into your "data warehouse" (that is, your database). Access will certainly not have problems with this - I suppose it can be a lot of work depending on how much you need to validate and check the data that goes in.
 
Upvote 0
In terms of creating a unique ID, I was hoping to get the boards thoughts on an approach I've worked on:

For each spreadsheet, I have taken the drug names columns, and have merged them into one table, then removed duplicates. I not have a 1 column table called 'Names', with the sole column being the unique key. I am then creating a 1-m relationship between the other tables, which have data specific to each drug.

I tried to make a query, but it didnt work, so I'm not sure I am approaching this in the right way.

Thoughts?
 
Upvote 0
Don't know for sure. If two sources use different names for the same drug you would have to map them to a single name:

acetaminophen acetiminophen
acetaminophen Tylenol
acetaminophen Anacin
ibuprofen ... ibuprofin
ibuprofen ... Advil
ibuprofen ... IB Pro


This would essential be a way to go from the names on the right side (such as three different names for tylenol) to the names on the left side (where you'd resolve them down to a single name acetaminophen).

I'm not sure what you mean by a query not working. That could be anything.

Don't pharmacists have any kind of standardization on drug names? I can't imagine with all the hundreds of thousands of drugs out that you'd be left on your own with this - there has to be indexes that already have this information. Talk to your local pharmacist ;) .
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,579
Messages
6,179,656
Members
452,934
Latest member
mm1t1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top