Creating a form with twosubforms and multiple records

[TonyWilliams via AccessMonster.com]

I have 3 tables called tblmonth, tblcompany and tblhivalue.

tblmonth has a date field txtmonth
tblcompany has a text field txtcompany
tblhivalue has a date field txtmonth and a text field txtcompany.
All the date fields are formatted mmmm yyyy
tblhivalue also includes a number of other fields which are a mixture of text
and number fields let’s say, txtnbr1,txtnbr2, txtnbr3, txttext1, txttext2,
txttext3.

The records for tblhivalue look like this:

txtmonth txtcompany txtnbr1 txtnbr2 txtnbr3 txttext1
txttext2 txttext3.
June 2009 ABC Ltd 1 46 54 abl
UK part
June 2009 ABC Ltd 2 54 45 abl
Non UK sole
June 2009 ABC Ltd 3 32 64
hybrid Non UK part
June 2009 XYZ Ltd 1 13 51 abl
UK sole
June 2009 CDE Ltd 1 21 34 abl
UK sole
September 2009 ABC Ltd 1 45 44 hybrid
Non UK part
September 2009 ABC Ltd 2 23 23 abl
UK sole
September 2009 XYZ Ltd 1 12 45 abl
UK sole
September 2009 XYZ Ltd 2 54 21 hybrid
Non UK sole
September 2009 CDE Ltd 1 32 12 abl
UK part


Txtnbr indicates the transaction number for that company in that month so eg
ABC Ltd had 3 transactions in June 2009 and 2 in September 2009.

I want to create a form like this:

Main form txtmonth from tblmonth

Subform1 based on tblhivalue with txtcompany from drop down list with a
record source tblcompany and saving the value to txtcompany tblhivalue,
linked to main form by txtmonth

Subform of Subform1 based on tblhivalue with txtnbr1 txtnbr2 txtnbr3
txttext1 txttext2 txttext3 from tblhivalue, linked to Subform1 by
txtcompany (and txtmonth?) so that this sub forms shows all the records for
that company for that month.


I have tried to do this as it seemed reasonably straight forward but
subform1 scrolls through all the transactions for the month for each company
whereas I only want Subform1 to scroll through the a single company record
for each month and the second subform to scroll through the transactions for
that company for that month.

Sorry this is so long but I hope I've explained my problem.

Can anyone help?
Thanks in anticipation.
Tony

 

[ruralguy via AccessMonster.com]

I do not have a solution, just some suggestions. A rule of thumb is that if
you will never be using the numerical value in a calculation (ie: SS# or
PhoneNumber) then store it in a Text (string) field. Otherwise store it in a
numerical field of some kind. It seems as if your numbers should be in
numerical fields to me, but I could be wrong. If you store a date as a
DateTime value you can always display it any way you want. Yours are string
fields. You may also want to examine your table structures for normalization.
There seems to be at least one issue here.

 

[TonyWilliams via AccessMonster.com]

Hi there. The dates are in date fields, i just tend to used the txt prefix
for most field descriptions. The numbers are also number fields as I need to
sum them for a report. I suspect you're right in that it's a normalisation
issue but I can't see where to go and was hoping someone could help.
Thanks for your comments.
Tony

I do not have a solution, just some suggestions. A rule of thumb is that if
you will never be using the numerical value in a calculation (ie: SS# or
PhoneNumber) then store it in a Text (string) field. Otherwise store it in a
numerical field of some kind. It seems as if your numbers should be in
numerical fields to me, but I could be wrong. If you store a date as a
DateTime value you can always display it any way you want. Yours are string
fields. You may also want to examine your table structures for normalization.
There seems to be at least one issue here.

I have 3 tables called tblmonth, tblcompany and tblhivalue.

[quoted text clipped - 58 lines]

Thanks in anticipation.
Tony

 

[ruralguy via AccessMonster.com]

Well that a little better. SubFormControls (used on forms to display SubForms)
have LinkChild/MasterFierlds properties to keep the SubForm in sync with the
MainForm. These properties can contain more than one value separated by a ";"
but both properties need the same number of values in the same order.

Hi there. The dates are in date fields, i just tend to used the txt prefix
for most field descriptions. The numbers are also number fields as I need to
sum them for a report. I suspect you're right in that it's a normalisation
issue but I can't see where to go and was hoping someone could help.
Thanks for your comments.
Tony

I do not have a solution, just some suggestions. A rule of thumb is that if
you will never be using the numerical value in a calculation (ie: SS# or

[quoted text clipped - 10 lines]

 

[TonyWilliams via AccessMonster.com]

Thanks ruralguy. I understand the concept of link fields as I said I think my
problem may lie in the make up of my tables, that's what I need to consider?
Cheers
Tony

Well that a little better. SubFormControls (used on forms to display SubForms)
have LinkChild/MasterFierlds properties to keep the SubForm in sync with the
MainForm. These properties can contain more than one value separated by a ";"
but both properties need the same number of values in the same order.

Hi there. The dates are in date fields, i just tend to used the txt prefix
for most field descriptions. The numbers are also number fields as I need to

[quoted text clipped - 8 lines]

 

[TonyWilliams via AccessMonster.com]

Hi, I think I’ve worked out a solution and would be grateful if someone would
comment on it for me. I have created an extra table, tblhvcomp with 2 fields
txtmonth and txtcompany. So my form structure is now like this:

Main form with 1 control based on txtmonth from tblmonth

Subform1 with 2 controls based on txtmonth and txtcompany from tblhvcomp
linked to main form on txtmonth

Subform of Subform1 with all my data controls and linked to subform1 on
txtmonth and txtcompany.

It seems to work but am unsure as to whether there is an easier way as I have
now 3 tables which all contain the txtmonth field and 3 tables which all
contain the txtcompany field.

Any comments would be helpful.
Thanks
Tony

Thanks ruralguy. I understand the concept of link fields as I said I think my
problem may lie in the make up of my tables, that's what I need to consider?
Cheers
Tony

Well that a little better. SubFormControls (used on forms to display SubForms)
have LinkChild/MasterFierlds properties to keep the SubForm in sync with the

[quoted text clipped - 6 lines]

 

[John W. Vinson]

Hi, I think I’ve worked out a solution and would be grateful if someone would
comment on it for me. I have created an extra table, tblhvcomp with 2 fields
txtmonth and txtcompany. So my form structure is now like this:

Main form with 1 control based on txtmonth from tblmonth

Subform1 with 2 controls based on txtmonth and txtcompany from tblhvcomp
linked to main form on txtmonth

Subform of Subform1 with all my data controls and linked to subform1 on
txtmonth and txtcompany.

It seems to work but am unsure as to whether there is an easier way as I have
now 3 tables which all contain the txtmonth field and 3 tables which all
contain the txtcompany field.

You've described your Forms. However, your Forms are *very much secondary*.
The Tables are fundamental! A relational database design starts with a
properly normalized and related set of tables; only when you have those nailed
down do you even *start* on Forms.

What are your Tables?

What Entity (real-life person, thing or event) does each table represent?

How are the Tables related?

 

[TonyWilliams via AccessMonster.com]

Hi John. I'll try and explain. The database collects statistical data for
quarters from a number of companies. So tblmonth stores the value of the
quarter (a date field formatted as mmmm yyyy) tbl company stores details of
the companies eg name, previous name UK or Euro company and tblhvcomp stores
the company names and the quarters and tblhivalue contains all the data for
each company for each quarter. I tend to define relationships in queries as I
need them ratherthan set them up at the start, I don't know whether that's a
good practice? Does that help?
Tony

Hi, I think IÂ’ve worked out a solution and would be grateful if someone would
comment on it for me. I have created an extra table, tblhvcomp with 2 fields

[quoted text clipped - 11 lines]

now 3 tables which all contain the txtmonth field and 3 tables which all
contain the txtcompany field.

You've described your Forms. However, your Forms are *very much secondary*.
The Tables are fundamental! A relational database design starts with a
properly normalized and related set of tables; only when you have those nailed
down do you even *start* on Forms.

What are your Tables?

What Entity (real-life person, thing or event) does each table represent?

How are the Tables related?

 

[John W. Vinson]

Hi John. I'll try and explain. The database collects statistical data for
quarters from a number of companies. So tblmonth stores the value of the
quarter (a date field formatted as mmmm yyyy) tbl company stores details of
the companies eg name, previous name UK or Euro company and tblhvcomp stores
the company names and the quarters and tblhivalue contains all the data for
each company for each quarter. I tend to define relationships in queries as I
need them ratherthan set them up at the start, I don't know whether that's a
good practice? Does that help?

It is NOT good practice.

If you do not have relationships with referential integrity enforced, you *do
not have a database* - you have a bunch of unrelated, uncontrolled,
unvalidated spreadsheets. Relationships help you prevent entering garbage data
(say a tblvalue record containing a company which does not exist).

I cannot see any point to having a table consisting of nothing but a date
field; what "attributes" - discrete bits of information needed for your
application - does a date have? The format of a date is not part of the data
nor stored in the table; it just controls the display and can be done on a
form or report to control the appearnce.

I did ask for the entities represented by your tables; now could you describe
the structure of the tables (fieldnames and datatypes)?

I really think you're on the wrong track (probably because of jumping to forms
too soon!) and may be painting yourself into a corner.

 

[TonyWilliams via AccessMonster.com]

Thanks for that reality check John! I'll certainly rethink the relationships
within the database. The main problem I have here is this is a database I
inherited. It was already designed this way with bits added on over the uears
and apart from completely rewriting the whole thing I'm trying to work with
what I've got.

The function of the database is to collect quarterly data and all the
reporting is based around the quarter dates. It would appear that the table
containing just the quarterly data was thought of as the anchor for all the
other data and the field in it is used in nearly all the queries, forms and
reports to group the data together. So that table has just a field with a
date. The tblcompany has all the details of the companies who provide data
and is a mixture of text fields eg name, previous name and a number field to
store an ID number and a YES/NO field to indicate whether they are a UK or
Euro company. The main table which contains the staistical data is made up of
number fields and also includes a date field to identify the quarter and a
text field to identify the company.

The names and field types are probably too numerous to list here but I could
certainly email you a list from the Access Documentor if you thought it would
be useful.

So in creating the new forms I'm not really starting with the forms, I'm
trying to add new forms and data into an already built database because of
the need to add new statistical data fields.

As you will have gathered I'm not an Access expert, I'm a 65 year old self
taught amateur, but I love a challenge and really do want to do things the
right way. The solution I described however crude seems to work but I really
do take on board what you said and will consider the possiblility of doing a
complete rewrite when time allows.

Hi John. I'll try and explain. The database collects statistical data for
quarters from a number of companies. So tblmonth stores the value of the

[quoted text clipped - 4 lines]

need them ratherthan set them up at the start, I don't know whether that's a
good practice? Does that help?

It is NOT good practice.

If you do not have relationships with referential integrity enforced, you *do
not have a database* - you have a bunch of unrelated, uncontrolled,
unvalidated spreadsheets. Relationships help you prevent entering garbage data
(say a tblvalue record containing a company which does not exist).

I cannot see any point to having a table consisting of nothing but a date
field; what "attributes" - discrete bits of information needed for your
application - does a date have? The format of a date is not part of the data
nor stored in the table; it just controls the display and can be done on a
form or report to control the appearnce.

I did ask for the entities represented by your tables; now could you describe
the structure of the tables (fieldnames and datatypes)?

I really think you're on the wrong track (probably because of jumping to forms
too soon!) and may be painting yourself into a corner.