tripptofer
Registered User.
- Local time
- Yesterday, 20:46
- Joined
- Aug 12, 2009
- Messages
- 20
I've posted here before and thought I made some progress, but it would seem that there is still something very fundamental that I am simply "not getting".
I have tried to use the examples in the Access2003 bible and even the results of Analyze table to automatically see how Access wants to do things. At first I thought it was simply I couldn't figure out to get access to do what I wanted, but now think it a fundamental problem between defining and then setting up candidate/super/foreign keys.
Problem: import grants data from a master spreadsheet into access, then tease apart information to form subtables with regard to the disbursements/grants themselves, grantors (who gave it), investigators (who got it) and recipient institutions (where they were at the time), plus city/state/country.
From the data source, there already exists a primary key for the disbursements (RCID). There are also “unique identifiers” from the NIH for grantor, investigator and recipient institution. Unfortunately there are also many synonyms for investigator names and many have multiple “unique IDs”.
I have teased out the unique investigator names, standardized them to the longest unique name and placed multiple “unique IDs” in separate columns.
I have also standardized recipient institution names, but stopped at the school/institute level as recipient organization IDs are at present only at the school/organization level- any greater granularity, while interesting, I think should await until I “get it”.
I understand primary keys, but I think I am getting hung up (actually I know I am) on foreign keys, candidate keys, superkeys- how to make them, how to set up the proper relationships and know that much of this is dependent upon structuring the database as a whole from the outset. I feel that I’m getting bogged down in symantics, but then different things are often called different names because they are indeed different if even subtly so…
I would like to be able to use referential integrity to backfill in the many holes that exist as well as to reconcile the master grants table with the new standardized investigator and recipient names ie, UCSD, UC San Diego, University of California at San Diego should all be set to the last example.
If I can get out of my rut and find out how to do one of these then I am hoping that the rest will fall into place…
I will also attach the original list of attributes from the spreadsheet and how I have tried to tease them apart. Putative PKs are in bold and FKs are in ital (is this even right?). The bits in square brackets are just headings and don't exist- in the original sheet like attributes are all over the shop and a good many are null, so this is just me trying to get organized. There's also a bit on the last page of my attempts to make sense of all of the potential relationships so that things are properly structured from the outset.
Eager to get out of this rut as I believe once I do that I'll be off to the races again!
Cheers in advance,
christopher
I have tried to use the examples in the Access2003 bible and even the results of Analyze table to automatically see how Access wants to do things. At first I thought it was simply I couldn't figure out to get access to do what I wanted, but now think it a fundamental problem between defining and then setting up candidate/super/foreign keys.
Problem: import grants data from a master spreadsheet into access, then tease apart information to form subtables with regard to the disbursements/grants themselves, grantors (who gave it), investigators (who got it) and recipient institutions (where they were at the time), plus city/state/country.
From the data source, there already exists a primary key for the disbursements (RCID). There are also “unique identifiers” from the NIH for grantor, investigator and recipient institution. Unfortunately there are also many synonyms for investigator names and many have multiple “unique IDs”.
I have teased out the unique investigator names, standardized them to the longest unique name and placed multiple “unique IDs” in separate columns.
I have also standardized recipient institution names, but stopped at the school/institute level as recipient organization IDs are at present only at the school/organization level- any greater granularity, while interesting, I think should await until I “get it”.
I understand primary keys, but I think I am getting hung up (actually I know I am) on foreign keys, candidate keys, superkeys- how to make them, how to set up the proper relationships and know that much of this is dependent upon structuring the database as a whole from the outset. I feel that I’m getting bogged down in symantics, but then different things are often called different names because they are indeed different if even subtly so…
I would like to be able to use referential integrity to backfill in the many holes that exist as well as to reconcile the master grants table with the new standardized investigator and recipient names ie, UCSD, UC San Diego, University of California at San Diego should all be set to the last example.
If I can get out of my rut and find out how to do one of these then I am hoping that the rest will fall into place…
I will also attach the original list of attributes from the spreadsheet and how I have tried to tease them apart. Putative PKs are in bold and FKs are in ital (is this even right?). The bits in square brackets are just headings and don't exist- in the original sheet like attributes are all over the shop and a good many are null, so this is just me trying to get organized. There's also a bit on the last page of my attempts to make sense of all of the potential relationships so that things are properly structured from the outset.
Eager to get out of this rut as I believe once I do that I'll be off to the races again!
Cheers in advance,
christopher