Linking multiple tables alternatives

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

re: Linking multiple tables alternatives

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


<dskillingstad@gmail.com> wrote in message
news:1112803362.456839.146970@g14g2000cwa.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Linking multiple tables alternatives

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

<dskillingstad@gmail.com> wrote in message
news:1112803362.456839.146970@g14g2000cwa.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Linking multiple tables alternatives

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

re: Linking multiple tables alternatives

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



admspam@yahoo.com wrote:[color=blue]
> it sounds like sub-forms are the way to go here, but your bigger[/color]
issue[color=blue]
> 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[/color]
with[color=blue]
> 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[/color]
types[color=blue]
> (tblPlumbers, tblElectricians, etc.) that contain ONLY the details[/color]
that[color=blue]
> distinguish one type from another. i don't really know enough about[/color]
the[color=blue]
> industry to say what, if any, these differences might be, and which[/color]
of[color=blue]
> 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[/color]
main[color=blue]
> 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[/color]
capturing[color=blue]
> 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[/color]
ContractorID[color=blue]
> -- each of which is a "foreign key" from tblPermits and[/color]
tblContractors,[color=blue]
> respectively, and then possibly a bunch of other columns to track[/color]
info[color=blue]
> 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[/color]
least[color=blue]
> food for thought.
>
> If you reply to this thread again, describe in more detail the kinds[/color]
of[color=blue]
> things about the permits and contractors you want to capture and then
> it'll be easier to suggest some directions to consider for your[/color]
tables[color=blue]
> and the relationships b/t them.
>
> Good luck!
>
> adm[/color]

re: Linking multiple tables alternatives

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
<dskillingstad@gmail.com> wrote in message
news:1112883409.544571.128210@l41g2000cwc.googlegr oups.com...[color=blue]
> 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
>
>
>
> admspam@yahoo.com wrote:[color=green]
>> it sounds like sub-forms are the way to go here, but your bigger[/color]
> issue[color=green]
>> 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[/color]
> with[color=green]
>> 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[/color]
> types[color=green]
>> (tblPlumbers, tblElectricians, etc.) that contain ONLY the details[/color]
> that[color=green]
>> distinguish one type from another. i don't really know enough about[/color]
> the[color=green]
>> industry to say what, if any, these differences might be, and which[/color]
> of[color=green]
>> 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[/color]
> main[color=green]
>> 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[/color]
> capturing[color=green]
>> 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[/color]
> ContractorID[color=green]
>> -- each of which is a "foreign key" from tblPermits and[/color]
> tblContractors,[color=green]
>> respectively, and then possibly a bunch of other columns to track[/color]
> info[color=green]
>> 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[/color]
> least[color=green]
>> food for thought.
>>
>> If you reply to this thread again, describe in more detail the kinds[/color]
> of[color=green]
>> things about the permits and contractors you want to capture and then
>> it'll be easier to suggest some directions to consider for your[/color]
> tables[color=green]
>> and the relationships b/t them.
>>
>> Good luck!
>>
>> adm[/color]
>[/color]

re: Linking multiple tables alternatives

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

re: Linking multiple tables alternatives

admspam@yahoo.com wrote:[color=blue]
> 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.
>[/color]
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).[color=blue]
> 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.
>[/color]
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)
[color=blue]
> Likewise with the contractors, as I suggested in my original[/color]
response.[color=blue]
> 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[/color]
a[color=blue]
> 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[/color]
response.[color=blue]
> Maybe other people can suggest how they would handle it, if they have
> different ideas.
>[/color]
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.
[color=blue]
> 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[/color]

Thanks for all your help!!

re: Linking multiple tables alternatives

Thanks for the response... Here's a few more tid bits
Phil Stanton wrote:[color=blue]
> The problem with your approach is that every time you decide to have[/color]
a new[color=blue]
> type of contractor you need a new table and have to redesign your[/color]
form.. For[color=blue]
> example where is your table of heating contractors, lighting[/color]
contractors[color=blue]
> etc. As you said, the all have the same information, but the obvious[/color]
one[color=blue]
> that you missed out is what sort of contractor they are. That is[/color]
simply a[color=blue]
> lookup.[/color]
Actually, there will only ever be Architect, Contractor, Engineer,
Plumber and Mechanical. We actually don't track electrical, lighting,
etc.
[color=blue]
> I am not sure what information is in the tblPermitPlumbing and
> tblPermitMechanical tables, but I suspect they are again identical[/color]
tables[color=blue]
> with just a lookup to determine whether it is a Plumbing or[/color]
mechanical[color=blue]
> permit.[/color]
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)
[color=blue]
> Again I am guessing, but isn't there a 1 to 1 relationship between[/color]
that[color=blue]
> tblPermitmain and the tblProperty, in which case they should be just[/color]
one[color=blue]
> table.
> I repeat my original message, Keep the number of tables down to a[/color]
reasonable[color=blue]
> level and join the main Permit to whichever of the contractors and[/color]
sub[color=blue]
> permits you need. You would probably need a main form and 2 subforms[/color]

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.
[color=blue]
> Phil
> <dskillingstad@gmail.com> wrote in message
> news:1112883409.544571.128210@l41g2000cwc.googlegr oups.com...[color=green]
> > Thanks for the help and the response!
> >
> > Adms: First I'll list what I have for tables, and then I'll explain[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
has[color=blue][color=green]
> > various field such as the permit number, permit type, square feet[/color][/color]
of[color=blue][color=green]
> > structure, etc and a whole host of yes/no fields. The[/color][/color]
PlumbingPermit[color=blue][color=green]
> > 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[/color][/color]
and[color=blue][color=green]
> > they would like to have a single form to input all of this[/color][/color]
information[color=blue][color=green]
> > so that he does not have to switch between various forms, all of[/color][/color]
the[color=blue][color=green]
> > information is visible (eas of use for the user). I could create[/color][/color]
one[color=blue][color=green]
> > or two tables to keep all of this info (approximately 75 fields)[/color][/color]
but I[color=blue][color=green]
> > don't want to, based on what I've read about normalizations, and[/color][/color]
here's[color=blue][color=green]
> > why.
> >
> > Not every permit will have an Architect, Engineer, Contractor,[/color][/color]
Plumber,[color=blue][color=green]
> > or Plumbing or Mechanical Permit. If I had one or two tables I[/color][/color]
would[color=blue][color=green]
> > 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
> >
> >
> >
> > admspam@yahoo.com wrote:[color=darkred]
> >> it sounds like sub-forms are the way to go here, but your bigger[/color]
> > issue[color=darkred]
> >> 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[/color]
> > with[color=darkred]
> >> 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[/color]
> > types[color=darkred]
> >> (tblPlumbers, tblElectricians, etc.) that contain ONLY the details[/color]
> > that[color=darkred]
> >> distinguish one type from another. i don't really know enough[/color][/color][/color]
about[color=blue][color=green]
> > the[color=darkred]
> >> industry to say what, if any, these differences might be, and[/color][/color][/color]
which[color=blue][color=green]
> > of[color=darkred]
> >> 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[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> >> main permit form have an empty subform control on it. also on the[/color]
> > main[color=darkred]
> >> form, put a combo-box that has a list of all the contractor types[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> >> it.
> >>
> >> in the "After Update" property of the combo box, add a function[/color][/color][/color]
that[color=blue][color=green][color=darkred]
> >> changes the subform based on the value in the combo box. this is[/color][/color][/color]
very[color=blue][color=green][color=darkred]
> >> 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[/color][/color][/color]
control[color=blue][color=green][color=darkred]
> >> (the shell on the main form that holds the subform) to "PermitID"[/color][/color][/color]
or[color=blue][color=green][color=darkred]
> >> whatever.
> >>
> >> In my opinion, the subforms should probably only be used for[/color]
> > capturing[color=darkred]
> >> 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[/color][/color][/color]
table.[color=blue][color=green][color=darkred]
> >>
> >> To be more clear, from a data architecture point of view, it[/color][/color][/color]
sounds[color=blue][color=green][color=darkred]
> >> like you want a "linking table" that connects contractors to[/color][/color][/color]
permits.[color=blue][color=green][color=darkred]
> >> This table would have at least two columns -- PermitID and[/color]
> > ContractorID[color=darkred]
> >> -- each of which is a "foreign key" from tblPermits and[/color]
> > tblContractors,[color=darkred]
> >> respectively, and then possibly a bunch of other columns to track[/color]
> > info[color=darkred]
> >> related to this particular connection between the contractor and[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> permit. Name this table something link[/color][/color][/color]
tbllnkContractors_and_Permits.[color=blue][color=green][color=darkred]
> >>
> >> Again, there are many ways to skin this cat, but the above is at[/color]
> > least[color=darkred]
> >> food for thought.
> >>
> >> If you reply to this thread again, describe in more detail the[/color][/color][/color]
kinds[color=blue][color=green]
> > of[color=darkred]
> >> things about the permits and contractors you want to capture and[/color][/color][/color]
then[color=blue][color=green][color=darkred]
> >> it'll be easier to suggest some directions to consider for your[/color]
> > tables[color=darkred]
> >> and the relationships b/t them.
> >>
> >> Good luck!
> >>
> >> adm[/color]
> >[/color][/color]

re: Linking multiple tables alternatives

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.

dskillingstad@gmail.com wrote:[color=blue]
> Thanks for the response... Here's a few more tid bits Phil Stanton
> wrote:
>[color=green]
>> The problem with your approach is that every time you decide to
>> have[/color]
>
> a new
>[color=green]
>> type of contractor you need a new table and have to redesign your[/color]
>
> form.. For
>[color=green]
>> example where is your table of heating contractors, lighting[/color]
>
> contractors
>[color=green]
>> etc. As you said, the all have the same information, but the
>> obvious[/color]
>
> one
>[color=green]
>> that you missed out is what sort of contractor they are. That is[/color]
>
> simply a
>[color=green]
>> lookup.[/color]
>
> Actually, there will only ever be Architect, Contractor, Engineer,
> Plumber and Mechanical. We actually don't track electrical,
> lighting, etc.
>
>[color=green]
>> I am not sure what information is in the tblPermitPlumbing and
>> tblPermitMechanical tables, but I suspect they are again identical[/color]
>
> tables
>[color=green]
>> with just a lookup to determine whether it is a Plumbing or[/color]
>
> mechanical
>[color=green]
>> permit.[/color]
>
> 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)[/color]

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
[color=blue]
>
>[color=green]
>> Again I am guessing, but isn't there a 1 to 1 relationship between[/color]
>
> that
>[color=green]
>> tblPermitmain and the tblProperty, in which case they should be
>> just[/color]
>
> one
>[color=green]
>> table. I repeat my original message, Keep the number of tables down
>> to a[/color]
>
> reasonable
>[color=green]
>> level and join the main Permit to whichever of the contractors and[/color]
>
> sub
>[color=green]
>> permits you need. You would probably need a main form and 2
>> subforms[/color]
>
>
> 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.
>
>[color=green]
>> Phil <dskillingstad@gmail.com> wrote in message
>> news:1112883409.544571.128210@l41g2000cwc.googlegr oups.com...
>>[color=darkred]
>>> Thanks for the help and the response!
>>>
>>> Adms: First I'll list what I have for tables, and then I'll
>>> explain[/color][/color]
>
> the
>[color=green][color=darkred]
>>> 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[/color][/color]
>
> has
>[color=green][color=darkred]
>>> various field such as the permit number, permit type, square feet
>>>
>>>
>>>
>>>
>>>[/color][/color]
>
> of
>[color=green][color=darkred]
>>> structure, etc and a whole host of yes/no fields. The[/color][/color]
>
> PlumbingPermit
>[color=green][color=darkred]
>>> 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[/color][/color]
>
> and
>[color=green][color=darkred]
>>> they would like to have a single form to input all of this[/color][/color]
>
> information
>[color=green][color=darkred]
>>> so that he does not have to switch between various forms, all of[/color][/color]
>
> the
>[color=green][color=darkred]
>>> information is visible (eas of use for the user). I could create
>>>
>>>
>>>
>>>
>>>[/color][/color]
>
> one
>[color=green][color=darkred]
>>> or two tables to keep all of this info (approximately 75 fields)[/color][/color]
>
> but I
>[color=green][color=darkred]
>>> don't want to, based on what I've read about normalizations, and[/color][/color]
>
> here's
>[color=green][color=darkred]
>>> why.
>>>
>>> Not every permit will have an Architect, Engineer, Contractor,[/color][/color]
>
> Plumber,
>[color=green][color=darkred]
>>> or Plumbing or Mechanical Permit. If I had one or two tables I[/color][/color]
>
> would
>[color=green][color=darkred]
>>> 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
>>>
>>>
>>>
>>> admspam@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[/color][/color]
>
> about
>[color=green][color=darkred]
>>> the
>>>
>>>> industry to say what, if any, these differences might be, and[/color][/color]
>
> which
>[color=green][color=darkred]
>>> 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[/color][/color]
>
> your
>[color=green][color=darkred]
>>>> 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[/color][/color]
>
> in
>[color=green][color=darkred]
>>>> it.
>>>>
>>>> in the "After Update" property of the combo box, add a function
>>>>
>>>>
>>>>
>>>>
>>>>[/color][/color]
>
> that
>[color=green][color=darkred]
>>>> changes the subform based on the value in the combo box. this
>>>> is[/color][/color]
>
> very
>[color=green][color=darkred]
>>>> 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[/color][/color]
>
> control
>[color=green][color=darkred]
>>>> (the shell on the main form that holds the subform) to
>>>> "PermitID"[/color][/color]
>
> or
>[color=green][color=darkred]
>>>> 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[/color][/color]
>
> table.
>[color=green][color=darkred]
>>>> To be more clear, from a data architecture point of view, it[/color][/color]
>
> sounds
>[color=green][color=darkred]
>>>> like you want a "linking table" that connects contractors to[/color][/color]
>
> permits.
>[color=green][color=darkred]
>>>> 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[/color][/color]
>
> the
>[color=green][color=darkred]
>>>> permit. Name this table something link[/color][/color]
>
> tbllnkContractors_and_Permits.
>[color=green][color=darkred]
>>>> 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[/color][/color]
>
> kinds
>[color=green][color=darkred]
>>> of
>>>
>>>> things about the permits and contractors you want to capture
>>>> and[/color][/color]
>
> then
>[color=green][color=darkred]
>>>> it'll be easier to suggest some directions to consider for your
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> tables
>>>
>>>> and the relationships b/t them.
>>>>
>>>> Good luck!
>>>>
>>>> adm
>>>[/color][/color]
>[/color]

re: Linking multiple tables alternatives

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.

re: Linking multiple tables alternatives

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

<dskillingstad@gmail.com> wrote in message
news:1112931896.494771.182630@z14g2000cwz.googlegr oups.com...[color=blue]
> Thanks for the response... Here's a few more tid bits
> Phil Stanton wrote:[color=green]
>> The problem with your approach is that every time you decide to have[/color]
> a new[color=green]
>> type of contractor you need a new table and have to redesign your[/color]
> form.. For[color=green]
>> example where is your table of heating contractors, lighting[/color]
> contractors[color=green]
>> etc. As you said, the all have the same information, but the obvious[/color]
> one[color=green]
>> that you missed out is what sort of contractor they are. That is[/color]
> simply a[color=green]
>> lookup.[/color]
> Actually, there will only ever be Architect, Contractor, Engineer,
> Plumber and Mechanical. We actually don't track electrical, lighting,
> etc.
>[color=green]
>> I am not sure what information is in the tblPermitPlumbing and
>> tblPermitMechanical tables, but I suspect they are again identical[/color]
> tables[color=green]
>> with just a lookup to determine whether it is a Plumbing or[/color]
> mechanical[color=green]
>> permit.[/color]
> 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)
>[color=green]
>> Again I am guessing, but isn't there a 1 to 1 relationship between[/color]
> that[color=green]
>> tblPermitmain and the tblProperty, in which case they should be just[/color]
> one[color=green]
>> table.
>> I repeat my original message, Keep the number of tables down to a[/color]
> reasonable[color=green]
>> level and join the main Permit to whichever of the contractors and[/color]
> sub[color=green]
>> permits you need. You would probably need a main form and 2 subforms[/color]
>
> 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.
>[color=green]
>> Phil
>> <dskillingstad@gmail.com> wrote in message
>> news:1112883409.544571.128210@l41g2000cwc.googlegr oups.com...[color=darkred]
>> > Thanks for the help and the response!
>> >
>> > Adms: First I'll list what I have for tables, and then I'll explain[/color][/color]
> the[color=green][color=darkred]
>> > 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[/color][/color]
> has[color=green][color=darkred]
>> > various field such as the permit number, permit type, square feet[/color][/color]
> of[color=green][color=darkred]
>> > structure, etc and a whole host of yes/no fields. The[/color][/color]
> PlumbingPermit[color=green][color=darkred]
>> > 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[/color][/color]
> and[color=green][color=darkred]
>> > they would like to have a single form to input all of this[/color][/color]
> information[color=green][color=darkred]
>> > so that he does not have to switch between various forms, all of[/color][/color]
> the[color=green][color=darkred]
>> > information is visible (eas of use for the user). I could create[/color][/color]
> one[color=green][color=darkred]
>> > or two tables to keep all of this info (approximately 75 fields)[/color][/color]
> but I[color=green][color=darkred]
>> > don't want to, based on what I've read about normalizations, and[/color][/color]
> here's[color=green][color=darkred]
>> > why.
>> >
>> > Not every permit will have an Architect, Engineer, Contractor,[/color][/color]
> Plumber,[color=green][color=darkred]
>> > or Plumbing or Mechanical Permit. If I had one or two tables I[/color][/color]
> would[color=green][color=darkred]
>> > 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
>> >
>> >
>> >
>> > admspam@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[/color][/color]
> about[color=green][color=darkred]
>> > the
>> >> industry to say what, if any, these differences might be, and[/color][/color]
> which[color=green][color=darkred]
>> > 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[/color][/color]
> your[color=green][color=darkred]
>> >> 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[/color][/color]
> in[color=green][color=darkred]
>> >> it.
>> >>
>> >> in the "After Update" property of the combo box, add a function[/color][/color]
> that[color=green][color=darkred]
>> >> changes the subform based on the value in the combo box. this is[/color][/color]
> very[color=green][color=darkred]
>> >> 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[/color][/color]
> control[color=green][color=darkred]
>> >> (the shell on the main form that holds the subform) to "PermitID"[/color][/color]
> or[color=green][color=darkred]
>> >> 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[/color][/color]
> table.[color=green][color=darkred]
>> >>
>> >> To be more clear, from a data architecture point of view, it[/color][/color]
> sounds[color=green][color=darkred]
>> >> like you want a "linking table" that connects contractors to[/color][/color]
> permits.[color=green][color=darkred]
>> >> 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[/color][/color]
> the[color=green][color=darkred]
>> >> permit. Name this table something link[/color][/color]
> tbllnkContractors_and_Permits.[color=green][color=darkred]
>> >>
>> >> 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[/color][/color]
> kinds[color=green][color=darkred]
>> > of
>> >> things about the permits and contractors you want to capture and[/color][/color]
> then[color=green][color=darkred]
>> >> it'll be easier to suggest some directions to consider for your
>> > tables
>> >> and the relationships b/t them.
>> >>
>> >> Good luck!
>> >>
>> >> adm
>> >[/color][/color]
>[/color]