Table Design or Report Design?

[JD McLeod]

I have a table Risk_tbl which contains business risks. Each risk can be
assigned to multiple components or types, such as compliance risk, financial
risk, products risk, etc. Within each of these categories there are more
types. For example products risk could inlcude loans, deposits, investments,
etc. Right now, in my Risk_tbl, I have a separate field for each of the risk
types (compliance, financial, product, etc.). This is a yes/no type field.
The reason I set it up like this was because each risk only appears once in
the table, but can be assigned to multiple categories, so setting the
categories up as a separate field was the only way I knew how to do it.
Everything has worked good, until I got ready to create a report. I would
like to have the risk type (compliance, financial, product) in the report
header so that I can group and sort on that field. But that won’t doesn’t
work because they are not one field, but separate fields. Can someone help.
Is my problem with reports or is it with my database design. A work around
would be to create different reports with just that specific field for each
risk type in the header, but then if I ever changed the report, I would have
to redo it numerous times for each risk type. What about basing the report
off a parameter query, not sure. Any help would be appreciated. Thanks.

 

[Duane Hookom]

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

 

[JD McLeod]

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.

 

[Duane Hookom]

I don't see how the risk table is related to the other two tables. Is the
RiskTable a single "audit"?

I would think there should be a RiskDetails table with one record per risk
element (or whatever). It isn't clear whether a detail risk element record
should be created if there is no risk identified.
--
Duane Hookom
Microsoft Access MVP

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

 

[JD McLeod]

Hey Duane, I appreciate your patience in this.
My risk table was supposed to represent each risk that we identified in the
audit. I only want each risk to appear one time in this table. Also in this
table, i am storing the details of the risk, such as the type of risk
(Products, Cust Svc, IS, etc) as well as a few other items such as the
likelihood and impact of occurrence. The other tables, Risk Universe and
Risk Element are where some of these details are stored. To populate the
risktable, i have a form the user fills out where he types in the risk, then
using checkboxes and list boxes, he selects the details of that risk. One
risk can be assigned to more than one type of risk.

You could have a situation where no risks were identified for a particular
risk type element. But if one is identified, it has to have both the risk
universe (type) and element selected. Thanks.

I don't see how the risk table is related to the other two tables. Is the
RiskTable a single "audit"?

I would think there should be a RiskDetails table with one record per risk
element (or whatever). It isn't clear whether a detail risk element record
should be created if there is no risk identified.
--
Duane Hookom
Microsoft Access MVP

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

--
Duane Hookom
Microsoft Access MVP


:

I have a table Risk_tbl which contains business risks. Each risk can be
assigned to multiple components or types, such as compliance risk, financial
risk, products risk, etc. Within each of these categories there are more
types. For example products risk could inlcude loans, deposits, investments,
etc. Right now, in my Risk_tbl, I have a separate field for each of the risk
types (compliance, financial, product, etc.). This is a yes/no type field.
The reason I set it up like this was because each risk only appears once in
the table, but can be assigned to multiple categories, so setting the
categories up as a separate field was the only way I knew how to do it.
Everything has worked good, until I got ready to create a report. I would
like to have the risk type (compliance, financial, product) in the report
header so that I can group and sort on that field. But that won’t doesn’t
work because they are not one field, but separate fields. Can someone help.
Is my problem with reports or is it with my database design. A work around
would be to create different reports with just that specific field for each
risk type in the header, but then if I ever changed the report, I would have
to redo it numerous times for each risk type. What about basing the report
off a parameter query, not sure. Any help would be appreciated. Thanks.

 

[Duane Hookom]

I am confused again since I didn't notice anything like an AuditID in the
risk table. If the records in this table are unique risk details then you
should have a related table that identifies whether the risk is Products,
Cust Serv, and/or IS. This could possibly create 3 records in the related
table.

--
Duane Hookom
Microsoft Access MVP

Hey Duane, I appreciate your patience in this.
My risk table was supposed to represent each risk that we identified in the
audit. I only want each risk to appear one time in this table. Also in this
table, i am storing the details of the risk, such as the type of risk
(Products, Cust Svc, IS, etc) as well as a few other items such as the
likelihood and impact of occurrence. The other tables, Risk Universe and
Risk Element are where some of these details are stored. To populate the
risktable, i have a form the user fills out where he types in the risk, then
using checkboxes and list boxes, he selects the details of that risk. One
risk can be assigned to more than one type of risk.

You could have a situation where no risks were identified for a particular
risk type element. But if one is identified, it has to have both the risk
universe (type) and element selected. Thanks.

I don't see how the risk table is related to the other two tables. Is the
RiskTable a single "audit"?

I would think there should be a RiskDetails table with one record per risk
element (or whatever). It isn't clear whether a detail risk element record
should be created if there is no risk identified.
--
Duane Hookom
Microsoft Access MVP

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.


:

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

--
Duane Hookom
Microsoft Access MVP


:

I have a table Risk_tbl which contains business risks. Each risk can be
assigned to multiple components or types, such as compliance risk, financial
risk, products risk, etc. Within each of these categories there are more
types. For example products risk could inlcude loans, deposits, investments,
etc. Right now, in my Risk_tbl, I have a separate field for each of the risk
types (compliance, financial, product, etc.). This is a yes/no type field.
The reason I set it up like this was because each risk only appears once in
the table, but can be assigned to multiple categories, so setting the
categories up as a separate field was the only way I knew how to do it.
Everything has worked good, until I got ready to create a report. I would
like to have the risk type (compliance, financial, product) in the report
header so that I can group and sort on that field. But that won’t doesn’t
work because they are not one field, but separate fields. Can someone help.
Is my problem with reports or is it with my database design. A work around
would be to create different reports with just that specific field for each
risk type in the header, but then if I ever changed the report, I would have
to redo it numerous times for each risk type. What about basing the report
off a parameter query, not sure. Any help would be appreciated. Thanks.

 

[JD McLeod]

Yes, that sounds like what i am looking for. I was trying to use this table
to enter the risk ID, risk description and what type of risk it was such as
products, customer servie, etc by using the yes/no check boxes. But you are
saying that should be done in a separate table? That this table should only
be for the risk itself and that a second table should be used to match the
risk to the different types? That makes sense, but can you tell me how i
would set it up so that the risk could be assigned to more than one type?
thanks again for your help.

I am confused again since I didn't notice anything like an AuditID in the
risk table. If the records in this table are unique risk details then you
should have a related table that identifies whether the risk is Products,
Cust Serv, and/or IS. This could possibly create 3 records in the related
table.

--
Duane Hookom
Microsoft Access MVP

Hey Duane, I appreciate your patience in this.
My risk table was supposed to represent each risk that we identified in the
audit. I only want each risk to appear one time in this table. Also in this
table, i am storing the details of the risk, such as the type of risk
(Products, Cust Svc, IS, etc) as well as a few other items such as the
likelihood and impact of occurrence. The other tables, Risk Universe and
Risk Element are where some of these details are stored. To populate the
risktable, i have a form the user fills out where he types in the risk, then
using checkboxes and list boxes, he selects the details of that risk. One
risk can be assigned to more than one type of risk.

You could have a situation where no risks were identified for a particular
risk type element. But if one is identified, it has to have both the risk
universe (type) and element selected. Thanks.

I don't see how the risk table is related to the other two tables. Is the
RiskTable a single "audit"?

I would think there should be a RiskDetails table with one record per risk
element (or whatever). It isn't clear whether a detail risk element record
should be created if there is no risk identified.
--
Duane Hookom
Microsoft Access MVP


:

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.


:

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

--
Duane Hookom
Microsoft Access MVP


:

I have a table Risk_tbl which contains business risks. Each risk can be
assigned to multiple components or types, such as compliance risk, financial
risk, products risk, etc. Within each of these categories there are more
types. For example products risk could inlcude loans, deposits, investments,
etc. Right now, in my Risk_tbl, I have a separate field for each of the risk
types (compliance, financial, product, etc.). This is a yes/no type field.
The reason I set it up like this was because each risk only appears once in
the table, but can be assigned to multiple categories, so setting the
categories up as a separate field was the only way I knew how to do it.
Everything has worked good, until I got ready to create a report. I would
like to have the risk type (compliance, financial, product) in the report
header so that I can group and sort on that field. But that won’t doesn’t
work because they are not one field, but separate fields. Can someone help.
Is my problem with reports or is it with my database design. A work around
would be to create different reports with just that specific field for each
risk type in the header, but then if I ever changed the report, I would have
to redo it numerous times for each risk type. What about basing the report
off a parameter query, not sure. Any help would be appreciated. Thanks.

 

[Duane Hookom]

Adding the Type of Risks to your main record is very similar to adding
OrderDetails to the Order table. You would probably use a subform for the
multiple types of risks.

--
Duane Hookom
Microsoft Access MVP

Yes, that sounds like what i am looking for. I was trying to use this table
to enter the risk ID, risk description and what type of risk it was such as
products, customer servie, etc by using the yes/no check boxes. But you are
saying that should be done in a separate table? That this table should only
be for the risk itself and that a second table should be used to match the
risk to the different types? That makes sense, but can you tell me how i
would set it up so that the risk could be assigned to more than one type?
thanks again for your help.

I am confused again since I didn't notice anything like an AuditID in the
risk table. If the records in this table are unique risk details then you
should have a related table that identifies whether the risk is Products,
Cust Serv, and/or IS. This could possibly create 3 records in the related
table.

--
Duane Hookom
Microsoft Access MVP

Hey Duane, I appreciate your patience in this.
My risk table was supposed to represent each risk that we identified in the
audit. I only want each risk to appear one time in this table. Also in this
table, i am storing the details of the risk, such as the type of risk
(Products, Cust Svc, IS, etc) as well as a few other items such as the
likelihood and impact of occurrence. The other tables, Risk Universe and
Risk Element are where some of these details are stored. To populate the
risktable, i have a form the user fills out where he types in the risk, then
using checkboxes and list boxes, he selects the details of that risk. One
risk can be assigned to more than one type of risk.

You could have a situation where no risks were identified for a particular
risk type element. But if one is identified, it has to have both the risk
universe (type) and element selected. Thanks.


:

I don't see how the risk table is related to the other two tables. Is the
RiskTable a single "audit"?

I would think there should be a RiskDetails table with one record per risk
element (or whatever). It isn't clear whether a detail risk element record
should be created if there is no risk identified.
--
Duane Hookom
Microsoft Access MVP


:

Below I list out the table structure and after that, I attempt to give an
example of what it is I am trying to achieve.

[RiskTable]
RiskID – PK – Autonumber
RiskDescription – Memo field
RiskImpact – Number
RiskLikelihood – Number
Products – Yes/No
CustomerService – Yes/No
InformationSystems – Yes/No

[RiskUniverse_tbl]
RiskTypeCode – Text – PK
RiskType – Text
--the types are the same as the Yes/No fields in the Risk Table

[RiskElements_tbl]
RiskUniverseCode – Text
--this is the same as the risk types in the earlier tables
RiskElement – Text


RISK=Loss of data could prevent us from timely filing our financial
statements.
RiskUniverse=InformationSystems
RiskElement=Daily back-up Plan

However, in the example above, it would be possible in some cases to have
one risk assigned to multiple RiskUniverse components and risk elements.
Such as
Risk=Customer data passing through out internet banking site is susceptible
to ID theft or misuse.
RiskUniverse=InformationSystems
RiskElement=Vendor Management

AND

RiskUniverse=ProductsServices
RiskElement=Internet Banking

If we were only assigning each risk to one component, I think I could handle
it, but it is this one to many issue that is fouling me up. I truly
appreciate any help you may have. This is a database that we will use
repeatedly over time, so I want to make sure I get it right. Thanks again.


:

You are correct that your un-normalized table structure is your issue. You
can either change the structure or use a normalizing union query to alter the
data.

Since you haven't provided any table and field names, we can't be much more
specific about the union query.

--
Duane Hookom
Microsoft Access MVP


:

I have a table Risk_tbl which contains business risks. Each risk can be
assigned to multiple components or types, such as compliance risk, financial
risk, products risk, etc. Within each of these categories there are more
types. For example products risk could inlcude loans, deposits, investments,
etc. Right now, in my Risk_tbl, I have a separate field for each of the risk
types (compliance, financial, product, etc.). This is a yes/no type field.
The reason I set it up like this was because each risk only appears once in
the table, but can be assigned to multiple categories, so setting the
categories up as a separate field was the only way I knew how to do it.
Everything has worked good, until I got ready to create a report. I would
like to have the risk type (compliance, financial, product) in the report
header so that I can group and sort on that field. But that won’t doesn’t
work because they are not one field, but separate fields. Can someone help.
Is my problem with reports or is it with my database design. A work around
would be to create different reports with just that specific field for each
risk type in the header, but then if I ever changed the report, I would have
to redo it numerous times for each risk type. What about basing the report
off a parameter query, not sure. Any help would be appreciated. Thanks.