Full Version: Normalized Tables have me so Lost how to make a form or query
UtterAccess Discussion Forums > Microsoftģ Access > Access Forms
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.
CAn 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.
P.S. I have all day today to work on this. And Thank you to whom ever replies.
Jack Cowley
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....
Thank you Jack,
Jack Cowley
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.