Populating one table from another table

[snutan1]

Hi all,
I am designing a database about proposals and the grants for that proposals. In that database I have a table named Prfessors with fields Prfessor ID
Role
ProfessorName
Department
College
I have separate tables for Professors names, departments and colleges. I need the professor, depatment and college names to be filled from their respective tables. I have created a form for professors where I can add these names but finally in the professors table I am only getting the IDs of the fields not the names. What should I need to do for that. And I also want to add a text search feature in the professors form to search on professor name. I also need to create a look up table for departments since when I get the data to be filled I will have only the code for the department not the department name. In departments table I have put this field but I dont know how can I combine both of them. I also need to have the college name filled automatically after the corresponding department is filled. I am attaching my database which I have designed so far. Please help me with this guys..

Thanks
Shilpa

 

[dkinley]

On your professors form, you have a drop down box where the name is. You can't add a new professor when you can't add them to the form. When you call from the professor list, it's looking for the proposal form?

Also, you are requiring a ProposalID before a professor can be added to the table? I would think it would be the other way around.

Honestly, and I apologize if I sound brutal, I don't have much confidence in the overall design aspect and it working for you in the long run.

From the relational aspect, you may have too much relationing going on. You map the departments to the colleges and then map each of them to a professor. Why not just flatten those two out and map one, or, only map department to college, map department to the professor and let the department link handle all of the college information (or vice versa)?

I guess the confusing part of this for me is probably because I am looking for some sort of overall theme here. Is the over goal to track the professors and the proposals they submit (and are awarded?). Or is the over goal to track the proposals and what professors contributed to it?

Again, I am not an expert extraordinaire, but you are posting a *free* website.

-dK

 

[dkinley]

Anyway ... felt bad about what I said ... so ....

I made some changes. I wasn't sure of scope so not 100% confident in the design. Reworked the tables and relationships to get those in a more manageable mode, deleted and rewrote the forms for simplicity and navigation (once you add some more buttons), deleted all the queries - added 3 back. Removed all the coding - added about 15 lines back. Needs some buttons and cosmetics but it should get you on the right track if its going in the direction you are.

I personally don't like finding stuff through combo boxes; I could never settled on the correct presentation for the user interface. So I put in a different search method in frmMain.

I wasn't sure about all that table copying stuff you needed to go down, so I made everything flow up to your professor form so there wouldn't be a need to copy tables.

Not that this is the end all be all, but, again, it should get you on the right track.

-dK

 

[snutan1]

Actually I dont have much idea about MS Access I have just done looking at some examples. I know my design is not good enough. Anyway thanku for the reply. I will follow this and post again to you if I have any doubt in this regard. Once again thanku for your time.

Shilpa

 

[dkinley]

This might help then. What I couldn't settle on was the proposal versus professor relationship. In the grant work I have done in the past, my experience says this should be a many-to-many relationship; however, your was not completely set up this way, thus my confusion and setting that aside to try and accomplish your goal.

In the grant work I've seen, there can be many professors contributing to the proposal, one PI for the actual grant (after award) and only one award per proposal. The only time if there were multiple awards for was non-compete grants after the initial award (but that would be a seperate 'project').

Also, I didn't see much of in-kind contribution (required by some grants) and where that would be located unless that is what the agency table was for. Speaking of, that sort of confused me, too, unless you are not required to track it. In retrospect, I am now thinking the grant agency is the sponsoring agency (for the grant), not the agency providing program management for the grant.

I am just throwing things out there to get you thinking in that 3000 ft perspective and then drilling down so you are capturing the data in the best possible way. My best recommendation is to suggest reviewing your requirements for the overall outcome and breaking that down into sub goals for the best design so you don't have problems or can mitigate any extras that might pop up down the road.

You're welcome for what I could do. Again, the rework is just to provide some insight on data flow with some examples so you can possibly accomplish your task a little easier.

-dK