DataBase - Form and Subform for single ID
Asked By forest8
29-Jan-10 01:13 AM
Hi
I am in the process of creating a Case Management database.
Every week, a coach meets with the students for Case Management purposes.
When the youth becomes involved in Case Management, they complete surveys at
6 months intervals.
I also have several tables (forms) which are based on survey questions which
must be completed at six months intervals. The main youth form is in the
Participant table (there is YouthID is the PK). I have created a One-to-Many
relationship between this table and the survey tables but there is no
referential integrity since I have more youths in the Participants table than
Survey tables. (At the moment, no additional surveys have been inputted for
any of the participants).
The questions on the survey are the same. For instance, one of the questions
is: Please describe your relationship with your mother, father, brother(s),
sister(s), etc.
I want to create a form which provides the responses for each youth for six
month interval that may be updated at any time.
That is, this form would have the youth's responses from all completed
surveys so that the coach can see how the youth responded previous to the
questions and whether it is changed during the non-survey time periods. (I
hope that makes sense).
So the Parent Form has the weekly updates and the subforms have the survey
responses.
How would I create this so that only the information for the youth in
question is in the subform? The subforms are not "talking" to the main form.
Thank you in advance.
(Sorry for the convoluted question).
Every wee
Database
(1)
Hookom
(1)
Duane
(1)
Surveys
(1)
Months
(1)
Sister
(1)
Week
(1)
Allen Browne replied to forest8
The answer to your question is that you will you need to use the Link Master
Fields and Link Child Fields properties of the subform control so the
subform records are only those for the YouthID in the main form.
Before you can design this form, you will need a good table design to handle
all the one-to-many relationships in this data, *with* referential
integrity. Note that enforced RI does not mean every student must have a
survey; it means that you can have a survey for a YouthID that is not a valid
person, i.e. if there is no 987 in the Participant table, then you cannot have
a survey for person 987.)
For a (free) sample database on how to do surveys, see this one from Duane
Hookom:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Library Database DataBase Firstly, I am not sure if Library database is the correct term. I am talking about Dbs to which you set a reference drew blanks last time. Now assuming I have several referenced (library?) databases in my main database, how do I get the names of all the forms in all the Dbs? CodeProject AllForms.Count gives me the correct number of forms if I am in that referenced database. What I think I need is a method of looping through all the different CodeProject names in the main database, but there is not a sort of CodeProjects parent object. Or somehow moving the focus And, No, there is not any parent object for all the code projects. Because each database only knows about its own objects (or documents), you have to use a procedure in each database to refer to the objects in that database. E.g. to open a form in
Might be outgrowing Access but daunted by SQL Server DataBase I am close to completing the consolidation of various small Access databases and a couple of Excel spreadsheets that my little company uses (5 staff) into an all encompasing Access database and I was planning on splitting the database when I was finished to allow simaltaneous use of it by staff on our small to me that I am going to need to give staff access to this new database from remote locations (outside of our office network). Two staff members are regularly overseas, one been playing around with trying to link tables from remote locations (ftp path of the database on our Network Storage Device at the office) and needless to say - that do not It all started when I came across a very encouraging article on keeping your Access database as your front end application and linking to SQL Server database tables. I am finding it MUCH more complicated than I had hoped. Can anyone suggest a simpler way of achieving remote access to my database? I have at my disposal a Network Storage Device which has remote access capabilities (this
Exporting Report from Access into PDF DataBase Hi everybody. I am hoping someone may be able to help me out. I have a database report that I want to export into PDF for each individual customer. Basically, I want time this source data changes. I am trying to work with Stephen Leban's A2000ReportToPDF database right now, but it is far more advanced than my puny range of code knowledge I am working with right now: Dim qdf As DAO.QueryDef Dim dbs As DAO.Database Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset Dim strSQL As String, strTemp As Close strTemp = strQName ' * ** code to set strSQL needs to be changed to conform to your ' * ** database design - - ManagerID and EmployeesTable need to ' * ** be changed to your table and field names ' Get rstMgr.EOF = False ' * ** code to set strMgr needs to be changed to conform to your ' * ** database design - - ManagerNameField, ManagersTable, and ' * ** ManagerID need to be changed to your table and field names Acct#", "Customers_To_Email", _ ' * ** code to set strSQL needs to be changed to conform to your ' * ** database design - - ManagerID and EmployeesTable need to ' * ** be changed to your table and field names strSQL everybody. I am hoping someone may be able to help me out. I have a database report that I want to export into PDF for each individual customer. Basically, I want
SQL query slow DataBase Hi In the code below I found that it takes 1.3 seconds to load suited to handle databases beyond stamp collection size and lacks most features of a "real" database management system. Consider using a relational database, i.e. SQL Server Express, its free. Make sure to set primary key properties to to worry about problems like SQL injection attacks to you application. best, MiB. The JET database engine is often faster and more efficient than similar retrieval with SQL Server. And simply can be summed up as your general ignorance on the matter how = 20 computers and database engines function and operate in our industry. So your galactic stupidity and ignorance is well years - version 4 came with Office 2000. If JET is so vastly superior to regular database servers, kindly share the pearls of your wisdom and explain why companies like Oracle (guess rationale, but I am sure you will enlighten us. Can you also explain, please, why database client products like Sparxsystems Enterprise Architect using the same relational schema, same index structures, and to criticize an important MVP like you. Maybe this source is: http: / / databases.aspfaq.com / database / what-are-the-limitations-of-ms-acc = ess.html. I would like to humbly hint
Restated: "Fields are expensive, records are cheap" DataBase Hi, First let me apolozie for the empty question below. I hit the Post button multiple small rows versus one large row. And we are assuming a 3rd normal form database design. And it may well be that I am totally misinterpreting the ???Fields are expensive your comments. Dennis Access Tables DB Design Discussions Access 2007 (1) Oracle (1) VBA (1) Database (1) Report (1) MVFs (1) Sharepoint (1) Attributes (1) The mantra in the subject line is purely a rule-of-thumb for beginning database designers. It has no bearing on your question about data access. Your question revolves around use up 510 bytes (unicode) of disk space to store those 2 characters. Internally, the database keeps track of where each field starts in each record. Some fields (memo, OLE, attachments being faster to retrieve. There are many other factors at work here, including whether the database has been compacted recently, whether you are using page- or record-locking, whether the disk just for performance reasons. The management of those relationships alone represents unnecessary complexity for the database and for developer. You may be forced to go that route if you are subclassing thinking in posting.) Having said that, having 60 fields in one table is unusual. The database I happen to be working on right now has 93 tables, and none of them