Relationships and Reports

[stephen.belyea]

I'm working on setting up a BMP database for our town office, and I'm
trying to figure out the best way to construct the table. The gist of
it is as follows:

Each business owns multiple properties.
Each property has multiple BMPs on it.
There are multiple types of BMPs.
Each property is inspected multiple times.

Is there a simple and elegant way to make this work? And is there a
way I can print reports that include all of this data on it?

Thanks everyone.

 

[Jason Lepack]

What is a BMP?

Other than that, this table structure should get you off to a good
start. Make sure that you create the required relationships in Tools-

Relationships. Then when you create a query you just need to add the

tables and select your fields. The joins will already be done for
you.

Business:
BusinessID - Autonumber
BusinessName

Property:
PropertyID - Autonumber
BusinessID - Relates to Business.BusinessID
PropertyName

BMPType:
BMPTypeID - Autonumber
BMPTypeName

BMP:
BMPID - Autonumber
PropertyID - Relates to Property.PropertyID
BMPTypeID - Relates to BMPType.BMPTypeID
BMPName

Inspection:
InspectionID - Autonumber
PropertyID - Relates to Property.PropertyID
InspectionDate

Cheers,
Jason Lepack

 

[stephen.belyea]

What is a BMP?

Other than that, this table structure should get you off to a good
start. Make sure that you create the required relationships in Tools->Relationships. Then when you create a query you just need to add the

tables and select your fields. The joins will already be done for
you.

Business:
BusinessID - Autonumber
BusinessName

Property:
PropertyID - Autonumber
BusinessID - Relates to Business.BusinessID
PropertyName

BMPType:
BMPTypeID - Autonumber
BMPTypeName

BMP:
BMPID - Autonumber
PropertyID - Relates to Property.PropertyID
BMPTypeID - Relates to BMPType.BMPTypeID
BMPName

Inspection:
InspectionID - Autonumber
PropertyID - Relates to Property.PropertyID
InspectionDate

Cheers,
Jason Lepack






- Show quoted text -

Thanks for the help Jason! Just for reference, BMPs are Best
Management Practices for dealing w/ stormwater runoff. I don't think
this is the correct place to pose this question, but I'll go ahead and
put it up here.

I have the table relations created like mentioned above, but when I
try to create a report that shows all the information from each of the
tables (with some fields hidden like the AutoID Numbers), the query/
report shows up blank. I'm trying to create a form that would allow a
user to edit all the information for a current business, which would
include the properties, BMPs and inspections. I'm able to create forms
like this that include the business, properties and either BMPs or
inspections, but not both BMPs and inspections within the same single
form.

Is there a work around for this that would allow me to have all 4
tables editable on a single form?

Thanks for the prompt response Jason!

 

[John W. Vinson]

Is there a work around for this that would allow me to have all 4
tables editable on a single form?

A Form for the "one" side table, with Subforms for the "many".

Don't try to do it with a Query joining all the tables - you'll only see
records where data exists in all the tables already, and it likely won't be
updateable.

John W. Vinson [MVP]

 

[stephen.belyea]

A Form for the "one" side table, with Subforms for the "many".

Don't try to do it with a Query joining all the tables - you'll only see
records where data exists in all the tables already, and it likely won't be
updateable.

John W. Vinson [MVP]

I think the problem I'm encountering is that my hierarchy is this:

Business -> Property -> BMP
Business -> Property -> Inspection

Property is a sub form of Business, but the Property sub form has two
sub forms below it - BMP and Inspection. When I create a form, I can
get it to show either BMP or Inspection, but not both at once. Is
there a way to make this work? Or is there another way to approach
this problem?

 

[stephen.belyea]

For clarification of my above post, tblProperty has a 1:Many
relationship with tblInspection & tblBMP. tblProperty is a sub form of
tblBusiness

..

 

[John W. Vinson]

I think the problem I'm encountering is that my hierarchy is this:

Business -> Property -> BMP
Business -> Property -> Inspection

Property is a sub form of Business, but the Property sub form has two
sub forms below it - BMP and Inspection. When I create a form, I can
get it to show either BMP or Inspection, but not both at once. Is
there a way to make this work? Or is there another way to approach
this problem?

By default, the form wizard creates subforms in Datasheet view - in which you
indeed cannot create a sub-subform.

However, you can change the Default View property of the Property subform to
"Single" - and then you CAN have two sub-subforms on it.

If this isn't satisfactory (for user interface reasons) post back - there are
other methods that can be used.

John W. Vinson [MVP]

 

[John W. Vinson]

For clarification of my above post, tblProperty has a 1:Many
relationship with tblInspection & tblBMP. tblProperty is a sub form of
tblBusiness

ummm...

No.

A Table *is not a Form*, and a Form is not a table.

Tables have relationships. Forms don't. A Form is just a window, a tool to let
you manage data in tables. Often (but not obligatorily) a Parent-Child related
pair of tables will be displayed on a Form and Subform; but a table cannot
*be* a subform.

John W. Vinson [MVP]

 

[stephen.belyea]

I don't think my earlier post went through, so I apologize if this is
a duplicate.

When I create a form that is based off of the record tblBusiness, and
then add the subform tblProperty subform and the sub-subform tblBMP
subform, everything works. The same for when I do tblProperty subform
to tblInspection subform. This is all done using the Wizard to create
the form.

When I attempt to create the form using the wizard to place
information from tblBusiness and tblProperty, however, I am unable to
successfully add tblBMP or tblInspection. When I try to create a
relationship with the tables, only the fields from the form created
for tblBusiness show up - not tblProperty - making it impossible for
me to establish a relationship from tblBMP subform and tblInspection
subform to tblProperty subform. This occurs even after setting
tblProperty subform Default View to "Single".

My vision is to have a form where the user can input information into
all of the tables - tblBusiness, tblBMP, tblProperty, tblInspection -
in a tabbed form, and be able to print out reports that include all of
the relevant information on them. If this isn't feasible, please let
me know, and I'll go back and start planning again.

Thanks for all the help you've provided!

 

[stephen.belyea]

Working on the problem again today, I think I found a solution.

I created a form (called frmProperty) with information from
tblProperty as the record source, and then was able to create two
subforms to tblBMP and tblInspector. I then created another form based
off of tblBusiness, and dragged and dropped frmProperty onto it - and
it works!

I've yet to attempt this to make a report, but I've got high hopes
that it'll work. Thanks for all of the help John!