Record Dependency

On Thu, 24 Jul 2008 15:46:10 -0700, Raven
<Raven.TakeThisOut@discussions.microsoft.com> wrote:

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

--

John W. Vinson/MVP

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


"John W. Vinson/MVP" wrote:

> On Thu, 24 Jul 2008 15:46:10 -0700, Raven
> <Raven DeleteThis @discussions.microsoft.com> wrote:
>
> >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?
>
> --
>
> John W. Vinson/MVP
>

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

> 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
>
>
> "John W. Vinson/MVP" wrote:
>
> > On Thu, 24 Jul 2008 15:46:10 -0700, Raven
> > <Raven.TakeThisOut@discussions.microsoft.com> wrote:
> >
> > >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?
> >
> > --
> >
> > John W. Vinson/MVP
> >

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



"Raven" <Raven RemoveThis @discussions.microsoft.com> wrote in message
news:57014CA2-0637-42CE-B4F2-0D3351DD7FE5@microsoft.com...
> 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
>
>
> "John W. Vinson/MVP" wrote:
>
>> On Thu, 24 Jul 2008 15:46:10 -0700, Raven
>> <Raven RemoveThis @discussions.microsoft.com> wrote:
>>
>> >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?
>>
>> --
>>
>> John W. Vinson/MVP
>>

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.

--
Thanks, for spreading the knowledge.
Raven


"Steve" wrote:

>
> 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
>
>
>
> "Raven" <Raven RemoveThis @discussions.microsoft.com> wrote in message
> news:57014CA2-0637-42CE-B4F2-0D3351DD7FE5@microsoft.com...
> > 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
> >
> >
> > "John W. Vinson/MVP" wrote:
> >
> >> On Thu, 24 Jul 2008 15:46:10 -0700, Raven
> >> <Raven RemoveThis @discussions.microsoft.com> wrote:
> >>
> >> >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?
> >>
> >> --
> >>
> >> John W. Vinson/MVP
> >>
>
>
>

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

--
Thanks, for spreading the knowledge.
Raven


"Ken Sheridan" wrote:

> 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" wrote:
>
> > 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
> >
> >
> > "John W. Vinson/MVP" wrote:
> >
> > > On Thu, 24 Jul 2008 15:46:10 -0700, Raven
> > > <Raven.RemoveThis@discussions.microsoft.com> wrote:
> > >
> > > >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?
> > >
> > > --
> > >
> > > John W. Vinson/MVP
> > >
>