Table Relations: linking data from multiple tables to create a single report

[Nashville DAS]

First, is there a "definitive" reference on this subject? I'm not adverse to
reading. In fact I've been pouring over the help fields but just can't seem
to glean the answer...if there is one.

Second, a better description of what I'm trying to do:

I have a monthly report that is broken up into various sub-sets (Service,
Installation, Performance, Training, etc...). Each sub-set requires various
similar entries - sometimes one or two entries and sometime six or seven
entries. With twelve sub-sets and say a maximum of ten entries my form get
huge quickly.

I have temporarily added these Sub-set entries into the main table but if in
the future additional sub-sets are needed (and they will be) then the table
ay become oversized.

So, what I'd like is to break these Sub-set categories out into smaller
manageable tables and link them to the main table via the ID Numbers, a many
to one relationship of sorts, and at the end of each month produce a report
that lists everything for the month (see, I have a query problem here too
apparently).

=========================================

What I've done to date is create sub-forms from the one master form with
macro links for navigation and that works ok. I've had to limit the number
of entry cells to each sub-set to five to keep the size of the report
manageable (currently two pages) but the limits this setup invokes are
problematic for the future.

Also, the number of entries on the form per sub-set is fixed (empty or not).

Also, some external operator controls have to managed - i.e. the form
generates a report directly for the month that I enter however if I do NOT
type over the previous form with the next months data my report will
increase in size - printing multiple months worth of data where I only
require the current months (or previous 30 days).

It didn't seem like I had so many issue when I started typing...LOL

Thanks

 

[TC]

First, is there a "definitive" reference on this subject? I'm not adverse to
reading. In fact I've been pouring over the help fields but just can't seem
to glean the answer...if there is one.

You need to understand a number of related concepts, in this order:
- database normalization: try
http://support.microsoft.com/support/kb/articles/Q100139.ASP for a dry but
instructive read.
- SQL language: start with online help for the SELECT statement.
- joins: start with online help for INNER JOIN and OUTER JOIN.

I suspect from the rest of your problem description, that you do not really
understand the concept of normalization properly. When you design a
normalized database, you generally >do not< do so on the basis of anything
to do with the forms or reports. You design the database to mimic the
natural (inherent) relationships between the various data entities,
completely regardless of how you may eventually enter or display that data
on screens or reports. Since you are changing your design to use "smaller,
managable tables", I suspect that you have not done this correctly

Forget your forms & reports for the moment. Post a brief description of each
table, along the following lines:

tblPerson - a record for each person known to the system.
PersonID autonumber - primary key
Surname, Forname, DateOfBirth etc.
(and so on)

Do all your tables have a primary key?
"Yes": good.
"No": Baaaaaad!
"What is a primary key?" - do more reading before you proceed!

HTH,
TC

 

[Nashville DAS]

TC,
Youre a Genius! That article got me on track with relationships. I was
aiming there but just couldn't hit the mark.
Thanks!

I'll delve into the SQL and "Join" stuff once I feel comfortable with the
relationships.

Very Cool!

 

[TC]

Thanks! Glad that it helped.

TC

TC,
Youre a Genius! That article got me on track with relationships. I was
aiming there but just couldn't hit the mark.
Thanks!

I'll delve into the SQL and "Join" stuff once I feel comfortable with the
relationships.

Very Cool!