Sub Form problem

[andy_dyer]

Hi,

My users have just asked for some more fields to be added...

The structure is there will be one intake group and then within this there may be smaller groups.

I'm trying to get my form to show the total group info and then on a tab have a subform that can have the rest of the info for each of the smaller groups...

What i don't know is how to structure my tables and set relationships so that the sub form actually works (I've created the tab and the subform but these aren't linked to the group record yet...)

Any help gratefully appreciated...

Thanks

Andrew

 

[sbooth]

Hi Andy. Can you provide either your table structure and/or a copy of your database? If you attach a copy of your database make sure you delete any sensitive data.

 

[andy_dyer]

here you go...

please be gentle it's been several years since I've touched access and i know my naming conventions are all over the place...

the subform section is on the collaborative tab on the frmInput...

 

[sbooth]

Okay, I looked at real quick. A couple of things jumped out. One is tblProgramme. It has fields 1 thru 9 to select providers. That is a many to many relationship. What you want is a table that combines these. So you create a table with an autonumber pk and a fk for both the providers table and the programme table. This will make everything much easier later.

Another table that jumps out at me is the tblInput. I am not sure exactly what you are trying to accomplish here, but it is definitely not normalized. You have information from multiple tables, and you have fields like CQICompletionRate1, CQICompletionRate2 , etc. If you could provide a little more information on what this table is, we may be able to help you better.

I am attaching a copy of your db with the changes to the tblProbiders and tblProgramme.

 

[andy_dyer]

Thanks ever so much for your help and time on this!

The other fields in tblInput are linked to having to capture 5 lots of data regarding the rating of the content of workshops, 5 lots of ratings for facilitator etc this is why this has so many fields... it looks messy but can't see anyother way to capture those...

I'm just going to look at what changes you've made.

 

[andy_dyer]

Ok... I'm being thick... I've no idea what that new combined table does, how it works and how I can actually get the information in it...

Then I'm back to my original problem of how I get the contents of the subform collaborative attached to a cohort id in tblInput...

Sorry if I'm not explaining myself well can't think what info you'll need in order to best help...

 

[sbooth]

In your tblProgramme you had 9 fields to enter Providers. There are many providers (more than one). There were many fields in tblProgramme where providers could be entered. This make it a many to many relationship which you want to avoid. Looking at your tables, very rarely are all nine fields filled in. This leaves fields with null values. Someone once told me (sorry, can't remember who) that a table should be a rectangle. You don't want blocks that are not filled in. Also, what happens if for some reason a programme needs to have 10 providers. I assume that right now this seems impossible, but it is better to prepare for things like this. Is there any reason why a provider would have been placed in the number 1 slot instead of the number 2 to 9 slots? Setting up your tables correctly will make setting up your forms, queries, and reports so much easier.

As far as getting information in to the new table. Simply setup a form that allows you to select the provider and the programme. I am not sure how data will flow thru the project you are trying, but you could put a button on the form where you enter programme information. This would take you to a form with all the providers that are already affiliated with the programme. Here providers could be deleted or added. Another option (this would probably be my choice) would be to setup a tab on the form that lists the providers for the programme. Again the user would be able to delete or add providers.

Back to the tblInput, are there always "5 lots of data regarding the rating of the content of workshops" and "5 lots of ratings for facilitator etc "? Also, can the same information be put in any 1 to 5 of these fields? If either of these are true than this needs to be fixed.

 

[boblarson]

Sounds like you should probably use a good read as well:
http://support.microsoft.com/kb/283878

 

[andy_dyer]

Ok - thanks to you both for that I think I now understand how that new table works and what it means...

How do I get my subform data table related to the main data table?

Thanks ever so much!

 

[boblarson]

If you use the wizard to add the subform, it will automatically suggest the links. But, if you already have it then you can go to the properties dialog after selecting the subform container control (control that houses the subform on the main form) and look in the data tab for the Master / Child Link properties and click in there and an ellipsis (...) will show up. Click on that and a dialog will pop up where you can select the field to link on between the main form and subform.