A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a form with twosubforms and multiple records



 
 
Thread Tools Display Modes
  #1  
Old January 3rd, 2010, 07:44 PM posted to microsoft.public.access.forms
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default Creating a form with twosubforms and multiple records

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

ruralguy wrote:
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



--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

  #2  
Old January 3rd, 2010, 08:19 PM posted to microsoft.public.access.forms
ruralguy via AccessMonster.com
external usenet poster
 
Posts: 1,172
Default Creating a form with twosubforms and multiple records

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.

TonyWilliams wrote:
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]
Thanks in anticipation.
Tony



--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

  #3  
Old January 4th, 2010, 09:58 AM posted to microsoft.public.access.forms
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default Creating a form with twosubforms and multiple records

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

ruralguy wrote:
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]
Thanks in anticipation.
Tony



--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via http://www.accessmonster.com

  #4  
Old January 4th, 2010, 11:35 AM posted to microsoft.public.access.forms
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default Creating a form with twosubforms and multiple records

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

TonyWilliams wrote:
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]
Thanks in anticipation.
Tony



--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

  #5  
Old January 4th, 2010, 06:31 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Creating a form with twosubforms and multiple records

On Mon, 04 Jan 2010 10:35:07 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote:

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?

--

John W. Vinson [MVP]
  #6  
Old January 4th, 2010, 07:32 PM posted to microsoft.public.access.forms
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default Creating a form with twosubforms and multiple records

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

John W. Vinson wrote:
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?


--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

  #7  
Old January 4th, 2010, 10:30 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Creating a form with twosubforms and multiple records

On Mon, 04 Jan 2010 18:32:44 GMT, "TonyWilliams via AccessMonster.com"
u56994@uwe wrote:

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.
--

John W. Vinson [MVP]
  #8  
Old January 5th, 2010, 11:13 AM posted to microsoft.public.access.forms
TonyWilliams via AccessMonster.com
external usenet poster
 
Posts: 117
Default Creating a form with twosubforms and multiple records

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.

John W. Vinson wrote:
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.


--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.