Can a form/subform structure be used for data in the same table

[Guest]

I am working on a clinical database requiring national harvest and structure
dictated by national entity. They require fields which have a parent-child
relationship to be part of the same table. Is it possible to set up a
form/subform for the parent-child fields if the fields are in the same table?
Tx
Rocky

 

[Guest]

You could do it by creating temporary tables from the main table, then
recreating the main table from the temporary tables afterwards, but why
bother?
It should be easy to come up with an alternative design not needing subforms.
A parent child relationship in one table is actually quite an elegant
structure. I'd call it a hierarchy where the basic structure is:
ID autonumber primary key
ParentID long integer (specifies another ID number)

Unfortunately, the SQL needed to unravel such a structure can be quite
daunting.

Dorian

 

[Guest]

That would be messy! Question, do they require that you store your data in
your database that way, or are the requiring that you submit the data to them
in that format?

I has a similar problem where a State agency required the data in a flat
format where there were child records. What we did was use our relational
model, but to send the data to them, we had a VBA routine that created the
records in the format they needed.

 

[MacDermott]

Maybe I'm missing something, but it doesn't sound messy at all to me.
I'd say go ahead and do the form/subform structure, and post back if you run
into specific difficulties.

 

[Vincent Johns]

I am working on a clinical database requiring national harvest and structure
dictated by national entity. They require fields which have a parent-child
relationship to be part of the same table. Is it possible to set up a
form/subform for the parent-child fields if the fields are in the same table?
Tx
Rocky

Other people have posted some good comments on this, but I don't see a
huge difficulty in doing what you apparently want to do. I do suggest
that you use Queries to display the contents of the Tables; for example...

Suppose you have a Table, [Names], containing records like these (in
which [ParentID] is a pointer to another record in [Names]):

NamesID First Last ParentID
---------- ------ ------- ----------
-819964391 John Miller
-819347395 John Jones
-174729864 Alicia Miller -819347395
1079702707 Mary Jones

You can define a Query, [Q_Names], to display the names of both linked
records:

SELECT Names.First, Names.Last,
Names_Parent.First AS ParentFirst,
Names_Parent.Last AS ParentLast
FROM [Names] LEFT JOIN [Names] AS Names_Parent
ON Names.ParentID = Names_Parent.NamesID
ORDER BY Names.Last, Names.First;

Notice that, in this SQL, both [Names] and [Names_Parent] refer to the
same Table, and Access doesn't get flustered.

Listing [Q_Names] in Datasheet View produces this:

First Last ParentFirst ParentLast
------- ------ ----------- ----------
John Jones
Mary Jones
Alicia Miller John Jones
John Miller

All this is easy to set up in Query Design View.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Learning as I go, and "Knowledge a bloody entrance doth make!"

Let me be a bit more specific about the fields and what I have tried so far.
The parent field is "category of disease" with 16 types of disease which are
defined in a combo box. There are two responses in the combo box which have
to do with infection which call for additional information in the next field
in the table "infection". The infection field also has a combo box defining
7 types of infections. I want to control use of the "infection" combo box so
data is only entered if one of the two infection values in chosen in the
"category of disease" field, otherwise I want to have the "infection" field
remain null.

As I understand it, Access will not allow fields to be used as part of
validation rules in tables. That being so, I decided to control the data
input by creating a form which has "category of disease" in a parent form,
and "infection" in a subform of that field. My problem now is twofold: 1)
I'm not sure I'm heading in the correct direction at all!, and 2) with my
limited knowledge of Access I do not know how to formulate a statement for a
validation rule which will control input in the "infection" field. One
option seems to be an Iff statement like,
"Iff([Forms]![frmInfection]![Category]=("Pleura - Infection" Or "Lung -
Infection"), "

but then I am not sure how to formulate a true and false statement to allow
input from the "infection" combo box or to mandate a null input for a false
response.

Let me know if you think is a reasonable approach or if I need something
entirely different including possibly a macro or more invovled structure.

Tx

 

[Rick Brandt]

Learning as I go, and "Knowledge a bloody entrance doth make!"

Let me be a bit more specific about the fields and what I have tried
so far. The parent field is "category of disease" with 16 types of
disease which are defined in a combo box. There are two responses in
the combo box which have to do with infection which call for
additional information in the next field in the table "infection".
The infection field also has a combo box defining 7 types of
infections. I want to control use of the "infection" combo box so
data is only entered if one of the two infection values in chosen in
the "category of disease" field, otherwise I want to have the
"infection" field remain null.

As I understand it, Access will not allow fields to be used as part of
validation rules in tables. [snip]

Untrue. A *field* validation rule defined in the table design cannot refer to
other fields, but a *table* validation rule can.

 

[Ed Warren]

Rocky, I'm curious as to what 'national entity' you are trying to please?
(and which of thier data sets you are trying to 'fill')? (an URL would be
great)

Note: I have an interest in the transfer of clinical data within the United
States.

I'm willing to take a look at their requirements directly and then give you
a hand.

Ed Warren

Learning as I go, and "Knowledge a bloody entrance doth make!"

Let me be a bit more specific about the fields and what I have tried so
far.
The parent field is "category of disease" with 16 types of disease which
are
defined in a combo box. There are two responses in the combo box which
have
to do with infection which call for additional information in the next
field
in the table "infection". The infection field also has a combo box
defining
7 types of infections. I want to control use of the "infection" combo box
so
data is only entered if one of the two infection values in chosen in the
"category of disease" field, otherwise I want to have the "infection"
field
remain null.

As I understand it, Access will not allow fields to be used as part of
validation rules in tables. That being so, I decided to control the data
input by creating a form which has "category of disease" in a parent form,
and "infection" in a subform of that field. My problem now is twofold: 1)
I'm not sure I'm heading in the correct direction at all!, and 2) with my
limited knowledge of Access I do not know how to formulate a statement for
a
validation rule which will control input in the "infection" field. One
option seems to be an Iff statement like,
"Iff([Forms]![frmInfection]![Category]=("Pleura - Infection" Or "Lung -
Infection"), "

but then I am not sure how to formulate a true and false statement to
allow
input from the "infection" combo box or to mandate a null input for a
false
response.

Let me know if you think is a reasonable approach or if I need something
entirely different including possibly a macro or more invovled structure.

Tx

Maybe I'm missing something, but it doesn't sound messy at all to me.
I'd say go ahead and do the form/subform structure, and post back if you
run
into specific difficulties.

 

[Ed Warren]

Rocky, I have attempted to pull out in a simple logic statement what I think
you have said. Is the logic below correct. If so then I think the people
on the list can be of some help.

-----------------------------------------------
Category of Disease (16 types)
Infection(category) (2 categories)
Infection(type) (7 types)
--------------------------------------------------
Logic:
Pick a Disease Category
Select an Infection Category
IF category =X then
Select Infection type from a combobox
Else
Infection type is null
end if
-------------------------------------

Learning as I go, and "Knowledge a bloody entrance doth make!"

Let me be a bit more specific about the fields and what I have tried so
far.
The parent field is "category of disease" with 16 types of disease which
are
defined in a combo box. There are two responses in the combo box which
have
to do with infection which call for additional information in the next
field
in the table "infection". The infection field also has a combo box
defining
7 types of infections. I want to control use of the "infection" combo box
so
data is only entered if one of the two infection values in chosen in the
"category of disease" field, otherwise I want to have the "infection"
field
remain null.

As I understand it, Access will not allow fields to be used as part of
validation rules in tables. That being so, I decided to control the data
input by creating a form which has "category of disease" in a parent form,
and "infection" in a subform of that field. My problem now is twofold: 1)
I'm not sure I'm heading in the correct direction at all!, and 2) with my
limited knowledge of Access I do not know how to formulate a statement for
a
validation rule which will control input in the "infection" field. One
option seems to be an Iff statement like,
"Iff([Forms]![frmInfection]![Category]=("Pleura - Infection" Or "Lung -
Infection"), "

but then I am not sure how to formulate a true and false statement to
allow
input from the "infection" combo box or to mandate a null input for a
false
response.

Let me know if you think is a reasonable approach or if I need something
entirely different including possibly a macro or more invovled structure.

Tx

Maybe I'm missing something, but it doesn't sound messy at all to me.
I'd say go ahead and do the form/subform structure, and post back if you
run
into specific difficulties.

 

[Guest]

To all who replied with offers of help, thank you.
I think I have found my way partly through the wilderness, using your
concepts if not your exact path.
I am using a combination of forms, control properties within forms, and
macros to direct the flow of data input.
If on a main form the user inputs "infection", the control property directs
them to a macro which opens a subform and sets the focus to the field
(control) describing the type of infection. That control's property then
directs them back to the main form.
If on the main form, a choice other than infection is chosen, the macro does
not open the subform but directs them to the next field in the mainform.
This seems to be a functional way solve the problem and will be a model for
several similar fields in this database.
Please let me know if you see pitfalls in this approach. At this time I do
not have enough knowledge of Visual Basic to write code, however I am taking
a Visual Basic for Access course later this month. So references to Visual
Basic might be useful later on.
Tx
Rocky

Rocky, I have attempted to pull out in a simple logic statement what I think
you have said. Is the logic below correct. If so then I think the people
on the list can be of some help.

-----------------------------------------------
Category of Disease (16 types)
Infection(category) (2 categories)
Infection(type) (7 types)
--------------------------------------------------
Logic:
Pick a Disease Category
Select an Infection Category
IF category =X then
Select Infection type from a combobox
Else
Infection type is null
end if
-------------------------------------

Learning as I go, and "Knowledge a bloody entrance doth make!"

Let me be a bit more specific about the fields and what I have tried so
far.
The parent field is "category of disease" with 16 types of disease which
are
defined in a combo box. There are two responses in the combo box which
have
to do with infection which call for additional information in the next
field
in the table "infection". The infection field also has a combo box
defining
7 types of infections. I want to control use of the "infection" combo box
so
data is only entered if one of the two infection values in chosen in the
"category of disease" field, otherwise I want to have the "infection"
field
remain null.

As I understand it, Access will not allow fields to be used as part of
validation rules in tables. That being so, I decided to control the data
input by creating a form which has "category of disease" in a parent form,
and "infection" in a subform of that field. My problem now is twofold: 1)
I'm not sure I'm heading in the correct direction at all!, and 2) with my
limited knowledge of Access I do not know how to formulate a statement for
a
validation rule which will control input in the "infection" field. One
option seems to be an Iff statement like,
"Iff([Forms]![frmInfection]![Category]=("Pleura - Infection" Or "Lung -
Infection"), "

but then I am not sure how to formulate a true and false statement to
allow
input from the "infection" combo box or to mandate a null input for a
false
response.

Let me know if you think is a reasonable approach or if I need something
entirely different including possibly a macro or more invovled structure.

Tx

Maybe I'm missing something, but it doesn't sound messy at all to me.
I'd say go ahead and do the form/subform structure, and post back if you
run
into specific difficulties.

I am working on a clinical database requiring national harvest and
structure
dictated by national entity. They require fields which have a
parent-child
relationship to be part of the same table. Is it possible to set up a
form/subform for the parent-child fields if the fields are in the same
table?
Tx
Rocky