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
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