Linking multiple tables alternatives

[dskillingstad]

I've been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn't work, created multiple queries
and that didn't work, played around with various relationships, and
that still didn't work.

Here's what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I'm trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I've been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer's information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I'm not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

[Phil Stanton]

Looks highly complicated.

Have a table of ContractorTypes:- ContratorTypeID AutoNumber, ContractorType
Text
A Table of Contractors:= ContratorID AutoNumber, ContractorName Text,
ContractorTypeID Long and all the other details like address and phone etc
A Table of Permits:- PermitID AutoNumber, PermitDetail Text, Etc
A Table of JoinPermitContractor PermitID Long, ContractorID Long Make this a
combined key
This last table "Joins" the permit to as many of the contractors as needed,
and because you are using a combined key, it will only allow you to use each
contractor once for each permit.

Set up the obvious relationships.

The Main Form has the Permit information, and have a subform with the first
field a comboBox to pick the Contractor where you can pick as many or as few
contractors as you like. You will also need a second form to input the
Contractor's details and use a comboBox on this to select the ContractorType

HTH

Phil
<ds***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I've been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn't work, created multiple queries
and that didn't work, played around with various relationships, and
that still didn't work.

Here's what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I'm trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I've been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer's information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I'm not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

[David Hodgkins]

Run the data vertically instead of horizontally and use lookup tables.

--
David Hodgkins, MCSD, MCDBA, MCSE
JSTAR Software Solutions
4402 Sweet Cherry Ln.
Kalamazoo, MI 49004
www.jstarsoftware.com - Home of AutoCompact for Access Databases
269-382-2931

<ds***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I've been struggling with this problem for some time and have tried
multiple solutions with no luck.

Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.

I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then created a form based on this
query. I was to the point where I could create new records, but could
not edit existing records. Based on suggestions from this group, I
tried using subforms, and that didn't work, created multiple queries
and that didn't work, played around with various relationships, and
that still didn't work.

Here's what I have for tables:
tblapplicant
tblarchitect
tblengineer
tblcontractor
tblpermitmain
tblplumbingcontractor
tblmechanicalcontractor
tblproperty
tblinspection

I'm trying to create a permit database and I need a single form where I
can input all of this information. The problem is, not all permits
will have say an architect or an engineer, or plumber, but at some
point they may. I've been asked to create one main form (for ease of
use) where an employee can simply input the architect, or plumber, or
engineer's information to an existing permit, and I have not been able
to do this.

If I combine all of the tables above into one or two tables, I could
end up with a PermitMain table (with all of the actual permit info) and
say a Personal table (with all of the applicant, architect, engineer,
contractor info), I will have a lot of empty fields for any given
record, because each record may not always have an engineer, architect,
or plumber, and from what I have read about normalization, this is bad
database design. So what is the best way to do this??

Any suggestions? I'm not looking for someones help to design my
database,just help in getting me pointed in the right direction.

Thanks!!
dskillingstad

[admspam]

it sounds like sub-forms are the way to go here, but your bigger issue
is the design of your tables and the relationships between them.

i don't know the details of your project, but i would probably go with
something like:

tblContractors (for name, address, phone, etc)
tblPermits
tbllnk_Permits_and_Contractors
tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various contractor types
(tblPlumbers, tblElectricians, etc.) that contain ONLY the details that
distinguish one type from another. i don't really know enough about the
industry to say what, if any, these differences might be, and which of
them you need to keep track of.

as far as the subforms go, there are a bunch of different ways you
could implement them in this case, but one way would be to have your
main permit form have an empty subform control on it. also on the main
form, put a combo-box that has a list of all the contractor types in
it.

in the "After Update" property of the combo box, add a function that
changes the subform based on the value in the combo box. this is very
rough, but it would look something like:

function fnChangeSubform()
Select Case me.cboContractorType
Case "Plumber"
me.subContractor.sourceobject = "frmContractors_subPlumbers"
Case "Electrician"
me.subContractor.sourceobject = "frmContractors_subElectricians"
End Select
end function

Link the childfield and masterfield properties on the subform control
(the shell on the main form that holds the subform) to "PermitID" or
whatever.

In my opinion, the subforms should probably only be used for capturing
details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about the
contractor in general (such as phone and address info). That info
should be stored in separate tables, linked to permits via a
ContractorID and be edited on separate forms connected to each table.

To be more clear, from a data architecture point of view, it sounds
like you want a "linking table" that connects contractors to permits.
This table would have at least two columns -- PermitID and ContractorID
-- each of which is a "foreign key" from tblPermits and tblContractors,
respectively, and then possibly a bunch of other columns to track info
related to this particular connection between the contractor and the
permit. Name this table something link tbllnkContractors_and_Permits.

Again, there are many ways to skin this cat, but the above is at least
food for thought.

If you reply to this thread again, describe in more detail the kinds of
things about the permits and contractors you want to capture and then
it'll be easier to suggest some directions to consider for your tables
and the relationships b/t them.

Good luck!

adm

[dskillingstad]

Thanks for the help and the response!

Adms: First I'll list what I have for tables, and then I'll explain the
method to this madness.

tblPermitMain
tblPermitPlumbing
tblPermitMechanical
The above tables contain permit information. tblPermitMain is the
building permit table which contains the permit info. This table has
various field such as the permit number, permit type, square feet of
structure, etc and a whole host of yes/no fields. The PlumbingPermit
and MechanicalPermit tables contain plumbing permit and mechanical
permit info.

tblProperty (contains property info)
propertyID - pk (autonumber)
parcelnumber - text
address - text
subdivision - text
lot - number
block - number

tblApplicant
tblConstContractor
tblConstArchitect
tblConstEngineer
tblConstPlumbing
tblConstMechanical
The above tables all contain the same info, Fname, Lname, Address,
City, State, Zip, Phone, etc. etc.

Now for the logic (madness). I've talked with our permit person and
they would like to have a single form to input all of this information
so that he does not have to switch between various forms, all of the
information is visible (eas of use for the user). I could create one
or two tables to keep all of this info (approximately 75 fields) but I
don't want to, based on what I've read about normalizations, and here's
why.

Not every permit will have an Architect, Engineer, Contractor, Plumber,
or Plumbing or Mechanical Permit. If I had one or two tables I would
end up with a lot of blank fields in my tables and I'm trying to
minimize that, so I thought I'd create separate tables for these
entities.

Thanks a lot for the help on this!!

dskillingstad

ad*****@yahoo.com wrote:

it sounds like sub-forms are the way to go here, but your bigger issue is the design of your tables and the relationships between them.

i don't know the details of your project, but i would probably go with something like:

tblContractors (for name, address, phone, etc)
tblPermits
tbllnk_Permits_and_Contractors
tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various contractor types (tblPlumbers, tblElectricians, etc.) that contain ONLY the details that distinguish one type from another. i don't really know enough about the industry to say what, if any, these differences might be, and which of them you need to keep track of.

as far as the subforms go, there are a bunch of different ways you
could implement them in this case, but one way would be to have your
main permit form have an empty subform control on it. also on the main form, put a combo-box that has a list of all the contractor types in
it.

in the "After Update" property of the combo box, add a function that
changes the subform based on the value in the combo box. this is very
rough, but it would look something like:

function fnChangeSubform()
Select Case me.cboContractorType
Case "Plumber"
me.subContractor.sourceobject = "frmContractors_subPlumbers"
Case "Electrician"
me.subContractor.sourceobject = "frmContractors_subElectricians"
End Select
end function

Link the childfield and masterfield properties on the subform control
(the shell on the main form that holds the subform) to "PermitID" or
whatever.

In my opinion, the subforms should probably only be used for capturing details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about the
contractor in general (such as phone and address info). That info
should be stored in separate tables, linked to permits via a
ContractorID and be edited on separate forms connected to each table.

To be more clear, from a data architecture point of view, it sounds
like you want a "linking table" that connects contractors to permits.
This table would have at least two columns -- PermitID and ContractorID -- each of which is a "foreign key" from tblPermits and tblContractors, respectively, and then possibly a bunch of other columns to track info related to this particular connection between the contractor and the
permit. Name this table something link tbllnkContractors_and_Permits.

Again, there are many ways to skin this cat, but the above is at least food for thought.

If you reply to this thread again, describe in more detail the kinds of things about the permits and contractors you want to capture and then
it'll be easier to suggest some directions to consider for your tables and the relationships b/t them.

Good luck!

adm

[Phil Stanton]

The problem with your approach is that every time you decide to have a new
type of contractor you need a new table and have to redesign your form.. For
example where is your table of heating contractors, lighting contractors
etc. As you said, the all have the same information, but the obvious one
that you missed out is what sort of contractor they are. That is simply a
lookup.
I am not sure what information is in the tblPermitPlumbing and
tblPermitMechanical tables, but I suspect they are again identical tables
with just a lookup to determine whether it is a Plumbing or mechanical
permit.
Again I am guessing, but isn't there a 1 to 1 relationship between that
tblPermitmain and the tblProperty, in which case they should be just one
table.
I repeat my original message, Keep the number of tables down to a reasonable
level and join the main Permit to whichever of the contractors and sub
permits you need. You would probably need a main form and 2 subforms

Phil
<ds***********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks for the help and the response!

Adms: First I'll list what I have for tables, and then I'll explain the
method to this madness.

tblPermitMain
tblPermitPlumbing
tblPermitMechanical
The above tables contain permit information. tblPermitMain is the
building permit table which contains the permit info. This table has
various field such as the permit number, permit type, square feet of
structure, etc and a whole host of yes/no fields. The PlumbingPermit
and MechanicalPermit tables contain plumbing permit and mechanical
permit info.

tblProperty (contains property info)
propertyID - pk (autonumber)
parcelnumber - text
address - text
subdivision - text
lot - number
block - number

tblApplicant
tblConstContractor
tblConstArchitect
tblConstEngineer
tblConstPlumbing
tblConstMechanical
The above tables all contain the same info, Fname, Lname, Address,
City, State, Zip, Phone, etc. etc.

Now for the logic (madness). I've talked with our permit person and
they would like to have a single form to input all of this information
so that he does not have to switch between various forms, all of the
information is visible (eas of use for the user). I could create one
or two tables to keep all of this info (approximately 75 fields) but I
don't want to, based on what I've read about normalizations, and here's
why.

Not every permit will have an Architect, Engineer, Contractor, Plumber,
or Plumbing or Mechanical Permit. If I had one or two tables I would
end up with a lot of blank fields in my tables and I'm trying to
minimize that, so I thought I'd create separate tables for these
entities.

Thanks a lot for the help on this!!

dskillingstad

ad*****@yahoo.com wrote:

it sounds like sub-forms are the way to go here, but your bigger

issue

is the design of your tables and the relationships between them.

i don't know the details of your project, but i would probably go

with

something like:

tblContractors (for name, address, phone, etc)
tblPermits
tbllnk_Permits_and_Contractors
tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various contractor

types

(tblPlumbers, tblElectricians, etc.) that contain ONLY the details

that

distinguish one type from another. i don't really know enough about

the

industry to say what, if any, these differences might be, and which

of

them you need to keep track of.

as far as the subforms go, there are a bunch of different ways you
could implement them in this case, but one way would be to have your
main permit form have an empty subform control on it. also on the

main

form, put a combo-box that has a list of all the contractor types in
it.

in the "After Update" property of the combo box, add a function that
changes the subform based on the value in the combo box. this is very
rough, but it would look something like:

function fnChangeSubform()
Select Case me.cboContractorType
Case "Plumber"
me.subContractor.sourceobject = "frmContractors_subPlumbers"
Case "Electrician"
me.subContractor.sourceobject = "frmContractors_subElectricians"
End Select
end function

Link the childfield and masterfield properties on the subform control
(the shell on the main form that holds the subform) to "PermitID" or
whatever.

In my opinion, the subforms should probably only be used for

capturing

details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about the
contractor in general (such as phone and address info). That info
should be stored in separate tables, linked to permits via a
ContractorID and be edited on separate forms connected to each table.

To be more clear, from a data architecture point of view, it sounds
like you want a "linking table" that connects contractors to permits.
This table would have at least two columns -- PermitID and

ContractorID

-- each of which is a "foreign key" from tblPermits and

tblContractors,

respectively, and then possibly a bunch of other columns to track

info

related to this particular connection between the contractor and the
permit. Name this table something link tbllnkContractors_and_Permits.

Again, there are many ways to skin this cat, but the above is at

least

food for thought.

If you reply to this thread again, describe in more detail the kinds

of

things about the permits and contractors you want to capture and then
it'll be easier to suggest some directions to consider for your

tables

and the relationships b/t them.

Good luck!

adm

[admspam]

If there are no differences between Plumbing Permit and Mechanical
Permit or whatever, there should only be one table. If there are
substantial differences between them, there should be three tables: a
PermitMain, PermitPlumbing, and PermitMechanical.

If a property can have more than one permit associated with it (ever)
then, as you say, you should have tblPermitMain and tblProperty.
tblProperty holds all the details of a given property, and PropertyID
will be stored as a foreign key in tblPermitMain.

Likewise with the contractors, as I suggested in my original response.
If substantial differences exist in the kind of info you need to
capture for mechanical contractors vs. plumbing contractors, then you
should have one main table -- tblContractors -- and a separate (but
linked) table for each contractor subtype. The problem Phil raises
(that you need a new table for every new contractor type) is correct,
but it is necessary if you need to track substantively different info
for each type...for precisely the reason you mention: you don't want a
table with 75 columns, most of which are empty for most contract
records.

Anyway, based on what you've said, I would probably implement this
using the dynamic subform approach I described in my earlier response.
Maybe other people can suggest how they would handle it, if they have
different ideas.

The main benefit of this approach is that it would appear to the user
that there is only one form, which you've stated is a design
requirement. Behind the scenes, of course, there would be multiple
forms, but the user would only see the one s/he needs.

adm

[dskillingstad]

ad*****@yahoo.com wrote:

If there are no differences between Plumbing Permit and Mechanical
Permit or whatever, there should only be one table. If there are
substantial differences between them, there should be three tables: a
PermitMain, PermitPlumbing, and PermitMechanical.
The Plumbing and Mechanical permit tables are very different, they have
the same value field types (yes/no & number field)but the information
is different (toilets/tubs vs. air conditioners/heaters). If a property can have more than one permit associated with it (ever)
then, as you say, you should have tblPermitMain and tblProperty.
tblProperty holds all the details of a given property, and PropertyID
will be stored as a foreign key in tblPermitMain.
This is what I've done, however, as I point out in the previous post, I
cannot edit existing records. For instance, if for some reason we do
not have an address for a specific permit (new parcel), assign one a
week later, I cannot enter that address for the permit at a later point
(see previous Phil response)
Likewise with the contractors, as I suggested in my original response. If substantial differences exist in the kind of info you need to
capture for mechanical contractors vs. plumbing contractors, then you
should have one main table -- tblContractors -- and a separate (but
linked) table for each contractor subtype. The problem Phil raises
(that you need a new table for every new contractor type) is correct,
but it is necessary if you need to track substantively different info
for each type...for precisely the reason you mention: you don't want a table with 75 columns, most of which are empty for most contract
records.

Anyway, based on what you've said, I would probably implement this
using the dynamic subform approach I described in my earlier response. Maybe other people can suggest how they would handle it, if they have
different ideas.
I guess I'll have to re-read your previous post to learn about dynamic
subforms. I've tried the normal subform route and get the error
messages saying I cannot enter a record in the one side of an outer
join.
The main benefit of this approach is that it would appear to the user
that there is only one form, which you've stated is a design
requirement. Behind the scenes, of course, there would be multiple
forms, but the user would only see the one s/he needs.

adm

Thanks for all your help!!

[dskillingstad]

Thanks for the response... Here's a few more tid bits
Phil Stanton wrote:

The problem with your approach is that every time you decide to have a new type of contractor you need a new table and have to redesign your form.. For example where is your table of heating contractors, lighting contractors etc. As you said, the all have the same information, but the obvious one that you missed out is what sort of contractor they are. That is simply a lookup. Actually, there will only ever be Architect, Contractor, Engineer,
Plumber and Mechanical. We actually don't track electrical, lighting,
etc.
I am not sure what information is in the tblPermitPlumbing and
tblPermitMechanical tables, but I suspect they are again identical tables with just a lookup to determine whether it is a Plumbing or mechanical permit. Actually, none of the fields are identical. For the plumbing table
there are a lot of yes/no fields and a number field for the total of
each fixture. For example, in the plumbing table I have many plumbing
type fixtures (water heaters, sinks, bathtubs, fauctes, etc) and the
number of each fixture. The mechanical table contains similar type
data, the yes/no fields and number of fixtures, but different types of
fixtures (boilers, heaters, air conditioners, etc., basically HVAC type
equipment)
Again I am guessing, but isn't there a 1 to 1 relationship between that tblPermitmain and the tblProperty, in which case they should be just one table.
I repeat my original message, Keep the number of tables down to a reasonable level and join the main Permit to whichever of the contractors and sub permits you need. You would probably need a main form and 2 subforms
I've tried the 1 to 1 relationship but end up having problems when I
try to edit existing records. I have tried all sorts of different
queries and relationships, to no avail. As I understand it, you can
only edit data in queries if you have 2 or less tables. In my
database, I have 8(?) tables, which I could get down to about 4 if I
combine some of them. This still means I have 4 tables in a query,
which feeds my PermitMain form. Even if I use subforms and set up 1-1
relationships between tblPermitMain and tblProperty, tblPlumbingPermit,
tblMechanical, etc etc I still get errors when trying to edit existing
records.
My form, whether I use a single large query, multiple small queries or
subforms on the PermitMain form (I've tried multiple ways of doing
this)I can create a new record and input data just fine. If I close
the form, open it again and try to edit an existing record, I receive
the following error:
"Cannot enter value into blank field on 'one' side of outer join."
All of my tables are related to various ID fields in the PermitMain
table.
Phil
<ds***********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks for the help and the response!

Adms: First I'll list what I have for tables, and then I'll explain the method to this madness.

tblPermitMain
tblPermitPlumbing
tblPermitMechanical
The above tables contain permit information. tblPermitMain is the
building permit table which contains the permit info. This table has various field such as the permit number, permit type, square feet of structure, etc and a whole host of yes/no fields. The PlumbingPermit and MechanicalPermit tables contain plumbing permit and mechanical
permit info.

tblProperty (contains property info)
propertyID - pk (autonumber)
parcelnumber - text
address - text
subdivision - text
lot - number
block - number

tblApplicant
tblConstContractor
tblConstArchitect
tblConstEngineer
tblConstPlumbing
tblConstMechanical
The above tables all contain the same info, Fname, Lname, Address,
City, State, Zip, Phone, etc. etc.

Now for the logic (madness). I've talked with our permit person and they would like to have a single form to input all of this information so that he does not have to switch between various forms, all of the information is visible (eas of use for the user). I could create one or two tables to keep all of this info (approximately 75 fields) but I don't want to, based on what I've read about normalizations, and here's why.

Not every permit will have an Architect, Engineer, Contractor, Plumber, or Plumbing or Mechanical Permit. If I had one or two tables I would end up with a lot of blank fields in my tables and I'm trying to
minimize that, so I thought I'd create separate tables for these
entities.

Thanks a lot for the help on this!!

dskillingstad

ad*****@yahoo.com wrote:

it sounds like sub-forms are the way to go here, but your bigger

issue

is the design of your tables and the relationships between them.

i don't know the details of your project, but i would probably go

with

something like:

tblContractors (for name, address, phone, etc)
tblPermits
tbllnk_Permits_and_Contractors
tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various contractor

types

(tblPlumbers, tblElectricians, etc.) that contain ONLY the details

that

distinguish one type from another. i don't really know enough about

the

industry to say what, if any, these differences might be, and

which of

them you need to keep track of.

as far as the subforms go, there are a bunch of different ways you
could implement them in this case, but one way would be to have

your main permit form have an empty subform control on it. also on the

main

form, put a combo-box that has a list of all the contractor types in it.

in the "After Update" property of the combo box, add a function that changes the subform based on the value in the combo box. this is very rough, but it would look something like:

function fnChangeSubform()
Select Case me.cboContractorType
Case "Plumber"
me.subContractor.sourceobject = "frmContractors_subPlumbers"
Case "Electrician"
me.subContractor.sourceobject = "frmContractors_subElectricians"
End Select
end function

Link the childfield and masterfield properties on the subform control (the shell on the main form that holds the subform) to "PermitID" or whatever.

In my opinion, the subforms should probably only be used for

capturing

details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about the
contractor in general (such as phone and address info). That info
should be stored in separate tables, linked to permits via a
ContractorID and be edited on separate forms connected to each table.
To be more clear, from a data architecture point of view, it sounds like you want a "linking table" that connects contractors to permits. This table would have at least two columns -- PermitID and

ContractorID

-- each of which is a "foreign key" from tblPermits and

tblContractors,

respectively, and then possibly a bunch of other columns to track

info

related to this particular connection between the contractor and the permit. Name this table something link tbllnkContractors_and_Permits.
Again, there are many ways to skin this cat, but the above is at

least

food for thought.

If you reply to this thread again, describe in more detail the kinds of

things about the permits and contractors you want to capture and

then it'll be easier to suggest some directions to consider for your

tables

and the relationships b/t them.

Good luck!

adm

[Jan]

Hi:

I want to respond only to a particular part of your question, and it's
interjected below (after your description of the plumbing table). It's
something that others have suggested but you seem not to gotten the
point. Maybe a different statement will help.

ds***********@gmail.com wrote:

Thanks for the response... Here's a few more tid bits Phil Stanton
wrote:

The problem with your approach is that every time you decide to
have
a new

type of contractor you need a new table and have to redesign your

form.. For

example where is your table of heating contractors, lighting

contractors

etc. As you said, the all have the same information, but the
obvious

one

that you missed out is what sort of contractor they are. That is

simply a

lookup.

Actually, there will only ever be Architect, Contractor, Engineer,
Plumber and Mechanical. We actually don't track electrical,
lighting, etc.

I am not sure what information is in the tblPermitPlumbing and
tblPermitMechanical tables, but I suspect they are again identical

tables

with just a lookup to determine whether it is a Plumbing or

mechanical

permit.

Actually, none of the fields are identical. For the plumbing table
there are a lot of yes/no fields and a number field for the total of
each fixture. For example, in the plumbing table I have many
plumbing type fixtures (water heaters, sinks, bathtubs, fauctes, etc)
and the number of each fixture. The mechanical table contains
similar type data, the yes/no fields and number of fixtures, but
different types of fixtures (boilers, heaters, air conditioners,
etc., basically HVAC type equipment)

Instead of having a big wide table with lots of yes/no fields (and every
time they come up with a different type of plumbing fixture you'll have
to add another yes/no field), consider this:
Try a narrower table, with these fields:
- identifier (probably the permitID or something like that)
- fixture type (text field, where you'll put in water heater, sink, etc.)
- fixture quantity (how many of that fixture)

This will allow you to be infinitely flexible with the number and type
of fixture, and not have all those check boxes. You can do the same
thing with mechanicals; in fact, you could have them all in one table,
with the addition of a field that tells you whether you're looking at
mechanicals or plumbing fixtures.

This general approach could apply to many other parts of your database;
once you start doing things this way you may never go back to check
boxes again!

Oh...in the forms, you'll have the main form for the permit and then a
subform, continuous, with all your fixtures and mechanicals.

HTH

Again I am guessing, but isn't there a 1 to 1 relationship between

that

tblPermitmain and the tblProperty, in which case they should be
just

one

table. I repeat my original message, Keep the number of tables down
to a

reasonable

level and join the main Permit to whichever of the contractors and

sub

permits you need. You would probably need a main form and 2
subforms

I've tried the 1 to 1 relationship but end up having problems when I
try to edit existing records. I have tried all sorts of different
queries and relationships, to no avail. As I understand it, you can
only edit data in queries if you have 2 or less tables. In my
database, I have 8(?) tables, which I could get down to about 4 if I
combine some of them. This still means I have 4 tables in a query,
which feeds my PermitMain form. Even if I use subforms and set up
1-1 relationships between tblPermitMain and tblProperty,
tblPlumbingPermit, tblMechanical, etc etc I still get errors when
trying to edit existing records. My form, whether I use a single
large query, multiple small queries or subforms on the PermitMain
form (I've tried multiple ways of doing this)I can create a new
record and input data just fine. If I close the form, open it again
and try to edit an existing record, I receive the following error:
"Cannot enter value into blank field on 'one' side of outer join."
All of my tables are related to various ID fields in the PermitMain
table.

Phil <ds***********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks for the help and the response!

Adms: First I'll list what I have for tables, and then I'll
explain
the
method to this madness.

tblPermitMain tblPermitPlumbing tblPermitMechanical The above
tables contain permit information. tblPermitMain is the building
permit table which contains the permit info. This table
has
various field such as the permit number, permit type, square feet

of
structure, etc and a whole host of yes/no fields. The
PlumbingPermit
and MechanicalPermit tables contain plumbing permit and
mechanical permit info.

tblProperty (contains property info) propertyID - pk (autonumber)
parcelnumber - text address - text subdivision - text lot -
number block - number

tblApplicant tblConstContractor tblConstArchitect
tblConstEngineer tblConstPlumbing tblConstMechanical The above
tables all contain the same info, Fname, Lname, Address, City,
State, Zip, Phone, etc. etc.

Now for the logic (madness). I've talked with our permit person
and
they would like to have a single form to input all of this
information
so that he does not have to switch between various forms, all of
the
information is visible (eas of use for the user). I could create

one
or two tables to keep all of this info (approximately 75 fields)
but I
don't want to, based on what I've read about normalizations, and
here's
why.

Not every permit will have an Architect, Engineer, Contractor,
Plumber,
or Plumbing or Mechanical Permit. If I had one or two tables I
would
end up with a lot of blank fields in my tables and I'm trying to
minimize that, so I thought I'd create separate tables for these
entities.

Thanks a lot for the help on this!!

dskillingstad

ad*****@yahoo.com wrote:

it sounds like sub-forms are the way to go here, but your
bigger

issue

is the design of your tables and the relationships between
them.

i don't know the details of your project, but i would probably
go

with

something like:

tblContractors (for name, address, phone, etc) tblPermits
tbllnk_Permits_and_Contractors tlkpPermitTypes (lookup table)
tlkpContractorTypes (lookuptable)

and then have separate tables for each of the various
contractor

types

(tblPlumbers, tblElectricians, etc.) that contain ONLY the
details

that

distinguish one type from another. i don't really know enough
about
the

industry to say what, if any, these differences might be, and
which
of

them you need to keep track of.

as far as the subforms go, there are a bunch of different ways
you could implement them in this case, but one way would be to
have
your
main permit form have an empty subform control on it. also on
the

main

form, put a combo-box that has a list of all the contractor
types
in
it.

in the "After Update" property of the combo box, add a function

that
changes the subform based on the value in the combo box. this
is
very
rough, but it would look something like:

function fnChangeSubform() Select Case me.cboContractorType
Case "Plumber" me.subContractor.sourceobject =
"frmContractors_subPlumbers" Case "Electrician"
me.subContractor.sourceobject =
"frmContractors_subElectricians" End Select end function

Link the childfield and masterfield properties on the subform
control
(the shell on the main form that holds the subform) to
"PermitID"
or
whatever.

In my opinion, the subforms should probably only be used for

capturing

details about the relationship between the contractor and this
particular permit, NOT for capturing/editing information about
the contractor in general (such as phone and address info).
That info should be stored in separate tables, linked to
permits via a ContractorID and be edited on separate forms
connected to each
table.
To be more clear, from a data architecture point of view, it
sounds
like you want a "linking table" that connects contractors to
permits.
This table would have at least two columns -- PermitID and

ContractorID

-- each of which is a "foreign key" from tblPermits and

tblContractors,

respectively, and then possibly a bunch of other columns to
track

info

related to this particular connection between the contractor
and
the
permit. Name this table something link
tbllnkContractors_and_Permits.
Again, there are many ways to skin this cat, but the above is
at

least

food for thought.

If you reply to this thread again, describe in more detail the
kinds
of

things about the permits and contractors you want to capture
and
then
it'll be easier to suggest some directions to consider for your


tables

and the relationships b/t them.

Good luck!

adm

[adm]

Sorry...I was definitely looking at this as a "start over" solution
rather than "how do i fix this" solution. You are just not going to be
able to edit data in a recordset that is based on a query with joins.
It logically doesn't make sense, because your db won't know where to
write the data to.

The methods I describe above are aimed at splitting out the data you
currently have joined via your queries into separate recordsets so you
can read AND write them....the current joined thing you have just isn't
going to work, because the data your query returns looks something
like:

fname | lname | bus. address | permitid | permittype
john | smith | 10 main st | 111 | plumbing
john | smith | 10 main st | 222 | plumbing

If you try to change smith's address from "10 main st" to something
else, Access (or, really, any db) isn't going to know where to write
that data, because assuming you've only got one field for bus. address,
it won't be able to know, logically, whether you want to change 10 main
st for john smith globally or just for this one permit, etc.

To repeat a design consideration from an earlier message, all the
fields that contractors have in common -- fname, lname, street address,
phone number, etc., -- should be stored in one table, tblContractors.
Only those areas where they differ should be stored in separate tables
-- tblContractors_Plumbers and tblContractors_Electricians, etc, --
and linked back to the parent tblContractors via a ContractorID.

[Phil Stanton]

My last thought is that as far as I know there is no limit on the number of
tables in an editable query, it depends on the relationships between them,
but see the help article on updateable queries.

Phil

<ds***********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks for the response... Here's a few more tid bits
Phil Stanton wrote:

The problem with your approach is that every time you decide to have

a new

type of contractor you need a new table and have to redesign your

form.. For

example where is your table of heating contractors, lighting

contractors

etc. As you said, the all have the same information, but the obvious

one

that you missed out is what sort of contractor they are. That is

simply a

lookup.

Actually, there will only ever be Architect, Contractor, Engineer,
Plumber and Mechanical. We actually don't track electrical, lighting,
etc.

I am not sure what information is in the tblPermitPlumbing and
tblPermitMechanical tables, but I suspect they are again identical

tables

with just a lookup to determine whether it is a Plumbing or

mechanical

permit.

Actually, none of the fields are identical. For the plumbing table
there are a lot of yes/no fields and a number field for the total of
each fixture. For example, in the plumbing table I have many plumbing
type fixtures (water heaters, sinks, bathtubs, fauctes, etc) and the
number of each fixture. The mechanical table contains similar type
data, the yes/no fields and number of fixtures, but different types of
fixtures (boilers, heaters, air conditioners, etc., basically HVAC type
equipment)

Again I am guessing, but isn't there a 1 to 1 relationship between

that

tblPermitmain and the tblProperty, in which case they should be just

one

table.
I repeat my original message, Keep the number of tables down to a

reasonable

level and join the main Permit to whichever of the contractors and

sub

permits you need. You would probably need a main form and 2 subforms

I've tried the 1 to 1 relationship but end up having problems when I
try to edit existing records. I have tried all sorts of different
queries and relationships, to no avail. As I understand it, you can
only edit data in queries if you have 2 or less tables. In my
database, I have 8(?) tables, which I could get down to about 4 if I
combine some of them. This still means I have 4 tables in a query,
which feeds my PermitMain form. Even if I use subforms and set up 1-1
relationships between tblPermitMain and tblProperty, tblPlumbingPermit,
tblMechanical, etc etc I still get errors when trying to edit existing
records.
My form, whether I use a single large query, multiple small queries or
subforms on the PermitMain form (I've tried multiple ways of doing
this)I can create a new record and input data just fine. If I close
the form, open it again and try to edit an existing record, I receive
the following error:
"Cannot enter value into blank field on 'one' side of outer join."
All of my tables are related to various ID fields in the PermitMain
table.

Phil
<ds***********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

> Thanks for the help and the response!
>
> Adms: First I'll list what I have for tables, and then I'll explain the > method to this madness.
>
> tblPermitMain
> tblPermitPlumbing
> tblPermitMechanical
> The above tables contain permit information. tblPermitMain is the
> building permit table which contains the permit info. This table has > various field such as the permit number, permit type, square feet of > structure, etc and a whole host of yes/no fields. The PlumbingPermit > and MechanicalPermit tables contain plumbing permit and mechanical
> permit info.
>
> tblProperty (contains property info)
> propertyID - pk (autonumber)
> parcelnumber - text
> address - text
> subdivision - text
> lot - number
> block - number
>
> tblApplicant
> tblConstContractor
> tblConstArchitect
> tblConstEngineer
> tblConstPlumbing
> tblConstMechanical
> The above tables all contain the same info, Fname, Lname, Address,
> City, State, Zip, Phone, etc. etc.
>
> Now for the logic (madness). I've talked with our permit person and > they would like to have a single form to input all of this information > so that he does not have to switch between various forms, all of the > information is visible (eas of use for the user). I could create one > or two tables to keep all of this info (approximately 75 fields) but I > don't want to, based on what I've read about normalizations, and here's > why.
>
> Not every permit will have an Architect, Engineer, Contractor, Plumber, > or Plumbing or Mechanical Permit. If I had one or two tables I would > end up with a lot of blank fields in my tables and I'm trying to
> minimize that, so I thought I'd create separate tables for these
> entities.
>
> Thanks a lot for the help on this!!
>
> dskillingstad
>
>
>
> ad*****@yahoo.com wrote:
>> it sounds like sub-forms are the way to go here, but your bigger
> issue
>> is the design of your tables and the relationships between them.
>>
>> i don't know the details of your project, but i would probably go
> with
>> something like:
>>
>> tblContractors (for name, address, phone, etc)
>> tblPermits
>> tbllnk_Permits_and_Contractors
>> tlkpPermitTypes (lookup table)
>> tlkpContractorTypes (lookuptable)
>>
>> and then have separate tables for each of the various contractor
> types
>> (tblPlumbers, tblElectricians, etc.) that contain ONLY the details
> that
>> distinguish one type from another. i don't really know enough about > the
>> industry to say what, if any, these differences might be, and which > of
>> them you need to keep track of.
>>
>> as far as the subforms go, there are a bunch of different ways you
>> could implement them in this case, but one way would be to have your >> main permit form have an empty subform control on it. also on the
> main
>> form, put a combo-box that has a list of all the contractor types in >> it.
>>
>> in the "After Update" property of the combo box, add a function that >> changes the subform based on the value in the combo box. this is very >> rough, but it would look something like:
>>
>> function fnChangeSubform()
>> Select Case me.cboContractorType
>> Case "Plumber"
>> me.subContractor.sourceobject = "frmContractors_subPlumbers"
>> Case "Electrician"
>> me.subContractor.sourceobject = "frmContractors_subElectricians"
>> End Select
>> end function
>>
>> Link the childfield and masterfield properties on the subform control >> (the shell on the main form that holds the subform) to "PermitID" or >> whatever.
>>
>> In my opinion, the subforms should probably only be used for
> capturing
>> details about the relationship between the contractor and this
>> particular permit, NOT for capturing/editing information about the
>> contractor in general (such as phone and address info). That info
>> should be stored in separate tables, linked to permits via a
>> ContractorID and be edited on separate forms connected to each table. >>
>> To be more clear, from a data architecture point of view, it sounds >> like you want a "linking table" that connects contractors to permits. >> This table would have at least two columns -- PermitID and
> ContractorID
>> -- each of which is a "foreign key" from tblPermits and
> tblContractors,
>> respectively, and then possibly a bunch of other columns to track
> info
>> related to this particular connection between the contractor and the >> permit. Name this table something link tbllnkContractors_and_Permits. >>
>> Again, there are many ways to skin this cat, but the above is at
> least
>> food for thought.
>>
>> If you reply to this thread again, describe in more detail the kinds > of
>> things about the permits and contractors you want to capture and then >> it'll be easier to suggest some directions to consider for your
> tables
>> and the relationships b/t them.
>>
>> Good luck!
>>
>> adm
>