I didn’t if I should start a new post or use the old one, but I do not see George on line.

He has been helping me with a db for disabled children. He normalized my tables and added many more. I am making this database for Part C of Early Intervention for Children and I have to make it on my own time. When I said I would set a db up for them (Marge) I had no idea that there was so much information, needing so many fields that needed to be related (connected) to each other.

George broke my tables into so many little ones that I don’t know how to put it together.

For the demographics of each child there are 4 tables; tbl_Child; tbl_ChildAddress; tbl_ChildDiabilityDiagnosis; tbl_ChildRecord. My first thought was to make subforms for the tbl_Child, tbl_ChildAddress, tbl_ChildDiabilityDiagnosis and put them into a main form of the tbl_ChildRecord and this would be where the first information of a new child would be entered.

An obvious problem is that it makes the form huge. I could put each table (subform) onto tab pages and have the tbl_ChildRecord as a subform, would that work?

My confusion is all the fields George put in the tbl_Child such as:
HouseParentID; HouseParentTypeID; ChildIntakeByID; ChildSexID, why doesn’t he just have HouseParent (name); HouseParentType(Guardian); ChildSexID (female). My original db had these fields at combo lists.

Can you explain the reason?

Then he has table that join fields. Example: trelChildServiceProvider the fields in this table are:
ChildProviderID PK auto
ChildRecordID number
ServiceProviderID number
ActiveDate date
ExitDate date
ChildProviderSetupDate date
ChildProviderSetupByID number

All I need for the Provider is his name, the agency he works for (a combo box because I have a table for Service Provider info), phone and address which would be pulled from the combo box in an event. The ExitDate is for the child. And I don’t know what the last two are for, something he thought was needed.

What is the prefix “trel”. I think part of my fear is that there is so many tables and I have never really made a db that was normalized and I don’t know where to start. Do I make forms or queries first? And How do I make queries with so many tables?

Please Help me!! I fill like it’s the first day of an Access class for beginners.

I have attached the database. If you need more information about the Part C program I will be happy to include it.

Lori
P.S. I have all day today to work on this. And Thank you to whom ever replies.

I do not have the time to go through your entire database, but I can tell you that the tables that George has prefixed with tblcbo are all lookups and will be Combo Boxes on your forms. In the tbl_Child the form will be based on the table, but controls like ChildSexID, ChildIntakeID, HouseParentID, etc. will all be combo boxes from which the user will select the appropriate data. For the ChildSexID they may select Male but the record will store the number 1. When you open the form and move between records you will see the childs sex as either Male or Female not 1 or 2 and this is how it should be. Lookup tables are good because you can easily add data to them and it does not disturb existing records and only requires a simple form for update purposes.

The tables with the prefix trel, in most cases, are 'junction' tables and they may end up as a subform with one of the fields the Chlld Link to the main form and the others combo boxes. I have not looked closely at this, but that is generally how it it done. Or you can use a query that use all three linked tables to show the data. At this point I am not sure what you will need to do so you will have to sort that out.

Using a tab control on your form will certainly help you to get more subforms on a single form and that is what I wold suggest. There may not be nearly as many subforms as you think as a lot of the tables are lookups and will be combo boxes on forms. For example tbl_Child would be a Main Form with tbl_ChildAddress being a subform along with tbl_ChildDisabilityDiagnosis, tbl_ChildRecord, trelChildDelayImpairment and trelChildLanguage. There may be more, but those seem to be fairly obvious.

I would start out with the Child form, a tab control for Address, Diagnosis, Impairment, language, etc. as subforms on the tabs. Create the queries as you need them.

I have done this very quickly and cannot guarantee that I have connected all the dots for you, but maybe it will give you a start....

hth,
Jack

Thank you Jack,
Lori

Lori -

You are most welcome! Good luck with this and you know that we are here to give you a hand if you have additional questions as you progress on your database.

Jack