Record Dependency

[Raven]

I am creating a db for our reporting area. The db will store information,
such as the type of reports, frequency, processed by, etc. I am having
trouble however, setting the relationship of the tables. In addition, when I
create the form to enter the data, the table I created does not update,
properly with the info. Can anyone help with this issue?

 

[Steve]

Please provide more description of what you do in your reporting area and I
will help you design appropriate tables.

Steve
(e-mail address removed)

 

[John W. Vinson/MVP]

I am creating a db for our reporting area. The db will store information,
such as the type of reports, frequency, processed by, etc. I am having
trouble however, setting the relationship of the tables. In addition, when I
create the form to enter the data, the table I created does not update,
properly with the info. Can anyone help with this issue?

Not without knowing more about the problem, no. You can see your
database; we cannot. What are the tables? HOw are they related? What
is the Recordsource of the form?

 

[Raven]

Sorry, I thought the additional information went through.
I would like to keep a track of all the report requests that's processed
through our reporting dept.
I have created an inventory table that will be used to store the data that I
input on the inventory form.
On the table I have the following fields:
Report ID; Report Type; Category/Description; Customer/ID/Location;
Frequency/Date; and Processor
I have created child tables based on these fields with autonumbered ids.
This is where I get confused... I'm not sure how to create the relationship
of these tables based on the ids from each table. Hope that makes sense.

Thanks, for spreading the knowledge.
Raven

 

[Steve]

TblLocation
LocationID
<Location Fields>

TblCustomer
CustomerID
<Customer Contact Fields>
LocationID

TblProcessor
ProcessorID
<Fields That Identify A Processor (Employee??)>

TblCategory
CategoryID
Category

TblReportType
ReportTypeID
ReportType

TblReport
ReportID
ReportDate
CustomerID
CategoryID
ReportTypeID
ProcessorID

.......... Don't know what frequency is ...........

Create the following relationships:
LocationID in TblLocation ===> LocationID in TblCustomer
CustomerID in TblCustomer ===> CustomerID in TblReport
CategoryID in TblCategory ===> CategoryID in TblReport
ReportTypeID in TblReportType ===> ReportTypeID in TblReport
ProcessorID in TblProcessor ===> ProcessorID in TblReport

Steve

 

[Ken Sheridan]

Essentially the Reports table models the many to many relationships between
the others in the way Steve describes, by having foreign key columns which
reference the primary keys of each of the referenced tables.

One point you need to look a little more closely at is that of the
relationships involving report categories and report types. It may be that
by referencing both the Categories and ReportTypes tables in the Reports
table you are introducing redundancy. If all reports of a particular Type
fall into the same Category, then you only need reference the ReportTypes
table in the Reports table, i.e. you only need a ReportTypeID column not a
CategoryID column. The ReportTypes table would then have a categoryID
foreign key column referncing the primary key of Categories, so the
relationships are like so:

Reports>-----ReportTypes>----Categories

It could be the other way round of course, with category implying type
rather than type implying category. However, if its as above, in the more
formal terminology of the relational model, to have both ReportTypeID and
CategoryID columns in Reports mean that the CategoryID column is transitively
functionally dependent on the key of Reports, ReportID determines TypeID
determines CategoryID. This would mean the table is not in Third Normal Form
(3NF), which requires all non-key columns to be functionally dependant on the
whole of the key of the table. Where a transitive functional dependency is
present the door is open to inconsistent data; in your case there would be
nothing to stop a row being entered in Reports with the same ReportTypeID and
different CategoryID values.

If neither type implies category, nor category implies type then there is no
transitive functional dependency of course, so having both columns in the
Reports table is legitimate.

You may have noticed that I differ from Steve (and many others!) in the
naming conventions I use. I favour the approach recommended by Joe Celko
that table names should as far as possible be plural or collective nouns
(reflecting the fact that tables are sets), and that column names should as
far as possible be singular nouns (reflecting the fact that each column
represents an attribute). In both cases the names should be as close as
possible to real English words describing the entity type or attribute type
in question, avoiding tags like 'tbl' as these merely get in the way of the
semantics. I'm by no means proscriptive about this though; each to his own.

Ken Sheridan
Stafford, England

 

[Raven]

Thanks for the assistance, Steve.

The Frequency field is used to determine how often the report is run. ie,
monthly, daily, etc.

I was able to create the ids as you specified and entered them on the
respective tables. However, would I just create the link on the relationship
tab or should I use the join or enforce referential integrity? Because, when
I start creating the form, I would will use combo boxes to autofill or sync
to other related fields. For instance, when the "customer" name is entered
or selected, the "location" field is automatically filled. So, I just want
to make sure that I am linking the tables correctly in order to make this
process work, effectively. Hope that makes sense.

 

[Raven]

Hi Ken.
Thank you so much for your input.
As I stated in my profile, I have created access dbs in the past, but mostly
through the help of wizards and trial and error.
I truly appreciate this forum and would someday like to be able to provide
my knowledge and support to people with less knowledge as myself. It is hard
to determine the best method for creating tables, forms, etc. As you
mentioned, "each to his own". I am willing to try the approach that works
best. However, I don't know what that is, yet. So, I am willing to take
advise and try this method or that one, whichever comes closer to meeting my
needs. Again, I thank you for sharing your knowledge.
Raven

 

[John... Visio MVP]

......... Don't know what frequency is ...........

Steve

Showing off the quality of your work? I thought at one time you pretended to
be an engineer? Any first year student would understand frequency and would
know that the OP was talking about how often a report is produced.

These newsgroups are provided by Microsoft for FREE peer to peer support.
Your type of help is not needed.

John... Visio MVP

 

[Raven]

Oops, Ken, I forgot one thing...
I may not be using the correct terms for my fields, so I wanted to clarify
how I'm using the Report Type, Category. The Report Type is to show if the
report was an Ad Hoc Query, Batch, Interface, etc. The Category is to show
the type of Ad Hoc Query, such as Headcount, Turnover, etc.
Thanks, again.
Raven

 

[Steve]

Hi Raven,

Regarding Frerquency, add another table to my original recommendation:
TblFrequency
FrequencyID
Frequency 'monthly, daily, etc.

Then add another field to TblReport:
FrequencyID

Add this relationship:
FrequencyID in TblFrequency ===> FrequencyID in TblReport

Create your relationships in the Relationships window. When creating
relationships, add the appropriate tables to the relationship window then
drag the primary key and drop it on the foreign. For example, n the above
relationship, TblFrequency and TblReport need to be in the relationship
window then drag FrequencyID in TblFrequency and drop it on FrequencyID in
TblReport. Look in the Help file for Referential Integrity and Cascade
Update and Cascade Delete to see what they are all about. (Hint: You want
Referential Integrity for all your relationships but no Cascade Delete.
Cascade Update is optional.)

Regarding autofilling, that is done by basing your form on an appropriate
query and has nothing to do with relationshipd other than relationships
determine which tables you are able to join in a query.

By The Way, what do you think of the likes of John Marshall? He has no help
to offer you but merely shows a malicious personnal attack upon me. Do you
think that is appropriate conduct for an MVP?

Steve

Thanks for the assistance, Steve.

The Frequency field is used to determine how often the report is run. ie,
monthly, daily, etc.

I was able to create the ids as you specified and entered them on the
respective tables. However, would I just create the link on the
relationship
tab or should I use the join or enforce referential integrity? Because,
when
I start creating the form, I would will use combo boxes to autofill or
sync
to other related fields. For instance, when the "customer" name is
entered
or selected, the "location" field is automatically filled. So, I just
want
to make sure that I am linking the tables correctly in order to make this
process work, effectively. Hope that makes sense.

 

[John... Visio MVP]

He has no help to offer you but merely shows a malicious personnal attack
upon me.

Interesting term for the truth, are you denying all the claims that are
mentioned at http://home.tiscali.nl/arracom/steveup.html

Do you think that is appropriate conduct for an MVP?

The MVPs are known for helping users of Microsoft products. Keeping users
from being scammed IS appropriate conduct.

Remember, if steve was any good, he would have enough repeat business that
he would not need to troll the newsgroups for work.

 

[Raven]

OK. I found that the previous setup information I was previously supplied
with did not work. Can someone please help me set up my tables, so that I can
affectively create my forms. When I added all the IDs to my main table I did
not know what to do with them on the forms.
I am starting from scratch.

 

[Raven]

Steve,
I am new to this forum. I appreciate your assistance and anyone else who
provides help. I am not here to judge anyone, but to just get the help and
knowledge I need to complete my database. I do not want to be a part of
anything else other than that.

 

[Steve]

Raven,

Are you saying that you were not able to implement the tables I gave you?

Steve

 

[Raven]

Steve,
Sorry, but I got a little discourage with the help I was expecting from this
forum, due to personal attacks between the contributors of this site. I
decided to try elsewhere for support with my database. I am still working on
it and seem to be making some progress. Thank you for your input.

 

[Raven]

Steve, I don't think my last attempt to post this message went through, as I
was kicked off my system. So, forgive the redundant message if the message
does post.

After my last post on 7/25, I got somewhat discouraged based on the insults
between two of the contributors of this site, and decided to seek support
with my database, elsewhere. I am still working on my database on making
some progress. Thank you for your input.

 

[Larry Linson]

After my last post on 7/25, I got somewhat discouraged
based on the insults between two of the contributors of
this site, and decided to seek support with my database,
elsewhere.

That's fine, Raven, none of us is paid for answering questions in this
newsgroup. Steve, however, trolls the newsgroup to try to find paying work,
but his demonstrated knowledge of Microsoft Access is not such that users
should be paying him for it, especially since better assistance is
available, free, right here, and he has been know to hold the truth in low
regard.

Steve is not the poor, poor pitiful little victim that he pretends to be, so
you need not waste time sympathizing with him over "insults" -- one of the
insulters, if you'll check back in the thread was Steve.

I am still working on my database on making
some progress. Thank you for your input.

There are other sources of good information. You might try the "Utter
Access" site. I don't believe Steve is allowed to clutter the message
threads there.

Larry Linson
Microsoft Office Access MVP
(information on the Microsoft MVP program is at
http://mvp.support.microsoft.com --
you will not find Steve's profile on the list there, just FYI)