Forms, Subforms, Tables & keys

[Bunky]

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Ken Sheridan]

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

 

[Bunky]

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Ken Sheridan]

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Bunky]

Ken,

One problem; the mgmt does not want the user to have to pull down. They
want to see all resorts on the screen when the form opens. They will allow
the user to pull down for their name and that is about it.

The columns for counting are in fact 1 column for each exchg company we deal
with. So your gut was correct. Now if I read you right, you say have the
count field and then an identifier field too. So if a user worked on the
same resort for exchg company XYZ for 8 items and exchg company ZXY for 9
items, I would have two rows instead of one on the table but it would be much
more versitile if we started dealing with other exchg companies. Is that
right?

Thanks for your patience and your help!

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

:

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Ken Sheridan]

My Plan B, using a list box on the parent form should keep the management
happy; just make sure the list box is big enough to show all the resorts.

As far as the logical model is concerned you are quite correct; there'd be
one row per company for each user/resort combination. This is both correct
in terms of the principles of the relational model, and, as you say, allows
for additional companies to be introduced into the database.

There is an alternative model, of course, which is a transactional one. In
this each dealing (transaction) a user had with a company would be a separate
row in the subform's underlying table, and the numbers would be obtained by
aggregation, using the COUNT operator in a query or in an expression as the
ControlSource of an unbound control in a form or report.

Ken Sheridan
Stafford, England

Ken,

One problem; the mgmt does not want the user to have to pull down. They
want to see all resorts on the screen when the form opens. They will allow
the user to pull down for their name and that is about it.

The columns for counting are in fact 1 column for each exchg company we deal
with. So your gut was correct. Now if I read you right, you say have the
count field and then an identifier field too. So if a user worked on the
same resort for exchg company XYZ for 8 items and exchg company ZXY for 9
items, I would have two rows instead of one on the table but it would be much
more versitile if we started dealing with other exchg companies. Is that
right?

Thanks for your patience and your help!

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

:

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

:

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Bunky]

Thanks for all your time and effort! Now all I have to do is put everything
together!!

Thanks again!
Kent

My Plan B, using a list box on the parent form should keep the management
happy; just make sure the list box is big enough to show all the resorts.

As far as the logical model is concerned you are quite correct; there'd be
one row per company for each user/resort combination. This is both correct
in terms of the principles of the relational model, and, as you say, allows
for additional companies to be introduced into the database.

There is an alternative model, of course, which is a transactional one. In
this each dealing (transaction) a user had with a company would be a separate
row in the subform's underlying table, and the numbers would be obtained by
aggregation, using the COUNT operator in a query or in an expression as the
ControlSource of an unbound control in a form or report.

Ken Sheridan
Stafford, England

Ken,

One problem; the mgmt does not want the user to have to pull down. They
want to see all resorts on the screen when the form opens. They will allow
the user to pull down for their name and that is about it.

The columns for counting are in fact 1 column for each exchg company we deal
with. So your gut was correct. Now if I read you right, you say have the
count field and then an identifier field too. So if a user worked on the
same resort for exchg company XYZ for 8 items and exchg company ZXY for 9
items, I would have two rows instead of one on the table but it would be much
more versitile if we started dealing with other exchg companies. Is that
right?

Thanks for your patience and your help!

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

:

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

:

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

:

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Bunky]

Ken,

One more question (isn't that always the case!).

Is there a way to have the boxes for the number of verifications done to be
totally propogated with the resort ids and names?


Thanks,
Kent

My Plan B, using a list box on the parent form should keep the management
happy; just make sure the list box is big enough to show all the resorts.

As far as the logical model is concerned you are quite correct; there'd be
one row per company for each user/resort combination. This is both correct
in terms of the principles of the relational model, and, as you say, allows
for additional companies to be introduced into the database.

There is an alternative model, of course, which is a transactional one. In
this each dealing (transaction) a user had with a company would be a separate
row in the subform's underlying table, and the numbers would be obtained by
aggregation, using the COUNT operator in a query or in an expression as the
ControlSource of an unbound control in a form or report.

Ken Sheridan
Stafford, England

Ken,

One problem; the mgmt does not want the user to have to pull down. They
want to see all resorts on the screen when the form opens. They will allow
the user to pull down for their name and that is about it.

The columns for counting are in fact 1 column for each exchg company we deal
with. So your gut was correct. Now if I read you right, you say have the
count field and then an identifier field too. So if a user worked on the
same resort for exchg company XYZ for 8 items and exchg company ZXY for 9
items, I would have two rows instead of one on the table but it would be much
more versitile if we started dealing with other exchg companies. Is that
right?

Thanks for your patience and your help!

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

:

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

:

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

:

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!

 

[Bunky]

Ken,

I'm sorry to bother you again, but I am obviously not understanding correctly.

Two Tables - Resort and Verification
The resort table has Resort ID, Resort name, and then specialist and date of
entry.
Key is Resort ID
The Verification table has specialistm date of entry, resort ID plus the
different verification counts and the company the counts represent.

My main form is Resort. However, when I try to enter some data, it is
putting the specialist and date in row with the resort id and resort name. I
did not want for this to be the main form control source so I changed it to
the Verification Table but now I have a list box that shows all of the
resorts and the ids based on the Resort table but have not been able to tie
it successfully to the resort ID. Plus, I need to have all the count areas
shown next to the resort names.

HELP!

My Plan B, using a list box on the parent form should keep the management
happy; just make sure the list box is big enough to show all the resorts.

As far as the logical model is concerned you are quite correct; there'd be
one row per company for each user/resort combination. This is both correct
in terms of the principles of the relational model, and, as you say, allows
for additional companies to be introduced into the database.

There is an alternative model, of course, which is a transactional one. In
this each dealing (transaction) a user had with a company would be a separate
row in the subform's underlying table, and the numbers would be obtained by
aggregation, using the COUNT operator in a query or in an expression as the
ControlSource of an unbound control in a form or report.

Ken Sheridan
Stafford, England

Ken,

One problem; the mgmt does not want the user to have to pull down. They
want to see all resorts on the screen when the form opens. They will allow
the user to pull down for their name and that is about it.

The columns for counting are in fact 1 column for each exchg company we deal
with. So your gut was correct. Now if I read you right, you say have the
count field and then an identifier field too. So if a user worked on the
same resort for exchg company XYZ for 8 items and exchg company ZXY for 9
items, I would have two rows instead of one on the table but it would be much
more versitile if we started dealing with other exchg companies. Is that
right?

Thanks for your patience and your help!

Kent:

In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.

The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.

txtuser;txtDate;lstResorts

and the LinkChildFields property would be extended to:

[user];[date];[resort id]

Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.

I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.

Ken Sheridan
Stafford, England

:

Ken,

Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!

I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).

I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day

I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.

Thanks for responding.
Kent

:

The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:

RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).

You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.

txtuser;txtDate

and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.

[user];[date]

though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.

That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.

Ken Sheridan
Stafford, England

:

I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.

ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.

Ideas are certainly welcome!