1 Reply Latest reply on Apr 17, 2013 12:05 AM by DillipKumarMahapatra

    One more question!



      One more question!


           Thanks for the help with my other question - it was solved beautifully.

           I am having trouble doing one last function that I find to be quite imperative.  I need to be able to see how many residents I have assigned to each rotation by block.  For example:

                          Intern:                     Block 1                     Block 2
                          John                     MICU                     ED
                          Jane                     MICU                     Elective
                          Interns Assigned by Block:                                            
                          MICU                     2                     0
                          ED                     0                     1
                          Elective                     0                     1

           (In my setup Interns would be referenced on a Interns:::Name and the names of the rotations would be on Rotations::Rotation but both are viewable from the Interns Schedule table)

           I was hoping to have this all appear in one table like above (only there are ~50 interns, 13 blocks and 17 distinct possible values for our rotations) but I keep getting 0 whenever I try to use the Count function for each field (Block 1, Block 2, ...)

           I origionally tried using the "New Report" feature to build a table to display this like I did for vacation requests and several other aspects of the database, but the output is unusable.  It lists dozens of empty rows and doesn't seem to count beyond Block 1.

           Any thoughts?


        • 1. Re: One more question!


               Hi Justin,

               Well i am not sure about the table structure you are using currently. But looking at your data representation, you can use below structure. Assimilating all these datas in one table will leads to give unsable data structure. So its better to normalized the table structure as below.

               You can use 3 tables for this database as, INTERN (to keep info of all Interns), BLOCK (to keep info of all Blocks) and ROTATIONS (to keep info of all Rotations ). Now you can add a join table (lets say ASSIGNEDINTERN ) to keep info of all Intern, Rotations and Block by adding the foreign keys of these the tables. So when an Intern is assigned for a Rotaion by any Block you can set the foreign keys of these tables in this ASSIGNEDINTERN table. Set up your relationships by using foreign key of the base table to your ASSIGNEDINTERN table and extract the info you want.

          This will make easier to set up your Report based on these tables.

               Now if you are already using these type of structure then it should work. If not then use above structure and set up your relationship to get the result. Any more help will be appreciated.