Newbie question on table design (1 Viewer)

[dunebuggy]

I'm very new to Access. I work in outsourcing for a drug company and we employ contract companies to work on our behalf. Currently I've set up a table called CompaniesT which has one-to-many relationships to other tables called ContactsT (our contacts at the vendors) and StudiesT (the clinical trials to be performed). So far this just about hangs together as it should.

What I'd like to be able to show is each Company's progress through a tendering process for each Study, the steps of which are called "RFI", "RFP" and "Award". So several companies will be in the RFI stage, fewer from the same group in RFP, and one will get the Award.

Ideally I'd like to have a Companies Form which would include our "history" together, i.e. display their progress through each study tendering progress, e.g. RFI for studies 1, 6 and 9, RFP for studies 6 and 9, and Awarded study 9.

I've tried setting up separate tables for RFI, RFP and Award, and using a listbox to select multiple companies, but this outputs multiple comma-separated text into a single field, and I can't work out how to use this appropriately. I'm stuck!

Hope this makes sense, would appreciate some help.
Thank you.

 

[Minty]

I think you have your tables all wrong - you wouldn't store RPI , Award etc. in different tables. Those are merely a status with a study.

In order to provide a history of the status 'events' you should probably have a table tblStudyStatus with the Study ID as a FK, the Status (I'd pull this from in another table, tblStatus), and a status date.

If this makes no sense post up a picture of what you have in the relationship window.

 

[dunebuggy]

Thanks for the quick response Minty.

So, would the tblStatus include Yes/No data type against fields for RFI, RFP and Award?

 

[Minty]

Not just the status itself. something like this;

I always include an ID field, because I store data is SQL Server, and it's much easier to deal with a linked table with a numeric primary key, and if you add any other fields it becomes much easier to reference.

This way you have as many companies per study as you want and can add as many status events and status types as you ever need.

 

[dunebuggy]

Thanks again.

Let's see if I follow, is this correct?

tblCompStudies would capture the list of companies that were in the running for a particular study

tblStudyStatus is for the status of a study, e.g. planned, setup, ongoing, completed

tblStatus lists events such as....RFI received? This one I find a bit confusing

Cheers

 

[Minty]

Yes tblStatus is a list of your available status's.
So at a guess (I don't know your business process) , you would have

RFI recieved
RFI provided
....
Awarded
etc.
etc.

You may possibly want to add a NoteTxt to the tblStudyStatus to include any extraneous notes, and probably an EmployeeID from an employee's table so you know who made the status update.