Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Is this database normalised?

Discussion in 'Information Technology' started by Tony Williams, Jul 28, 2006.

  1. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    I have a database with 4 tables. Table 1 holds the details of contracts.
    Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
    can have 1 2 or 3 parties apart from ourselves. I have created separate
    tables for the parties to hold information like contact names address etc.
    Is this the right formation for this database?
    Thanks
    Tony
     
  2. Loading...

    Similar Threads Forum Date
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Are you saying that table 2 is the details for contact 1, table 3 is the
    details for contact 2 and table 4 is the details for contact 3?

    Odds are it isn't normalized, then. Having multiple tables for the same
    entity isn't usually correct.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Tony Williams" <tw@invalid.com> wrote in message
    news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > I have a database with 4 tables. Table 1 holds the details of contracts.
    > Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
    > can have 1 2 or 3 parties apart from ourselves. I have created separate
    > tables for the parties to hold information like contact names address etc.
    > Is this the right formation for this database?
    > Thanks
    > Tony
    >
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    nope

    Contract
    ContractID
    .....

    Party
    PartyID
    .....

    ContractParty
    ContractID
    PartyID

    Pieter


    "Tony Williams" <tw@invalid.com> wrote in message
    news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >I have a database with 4 tables. Table 1 holds the details of contracts.
    >Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
    >can have 1 2 or 3 parties apart from ourselves. I have created separate
    >tables for the parties to hold information like contact names address etc.
    > Is this the right formation for this database?
    > Thanks
    > Tony
    >
     
  5. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Probably not. I would expect 3 tables

    contracts
    parties
    contractsparties

    Where
    contracts - holds the details about a contract
    parties - holds details about parties to contracts
    contractsparties - links the two together

    In this way you can have as many parties to a contract as you like and have
    as many contracts for a party as you like.


    --

    Terry Kreft


    "Tony Williams" <tw@invalid.com> wrote in message
    news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > I have a database with 4 tables. Table 1 holds the details of contracts.
    > Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
    > can have 1 2 or 3 parties apart from ourselves. I have created separate
    > tables for the parties to hold information like contact names address etc.
    > Is this the right formation for this database?
    > Thanks
    > Tony
    >
    >
     
  6. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    Would the parties table then have to have fields like company1name,
    company2name, company3name and then contact1name,contact2name,contact3name
    etc
    I htought that this was not a good way to set up a database ie having
    repeated fields. What would be the structure of contractparties, how would
    it link the parties to the contacts?
    Thanks for your help
    Tony
    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > Probably not. I would expect 3 tables
    >
    > contracts
    > parties
    > contractsparties
    >
    > Where
    > contracts - holds the details about a contract
    > parties - holds details about parties to contracts
    > contractsparties - links the two together
    >
    > In this way you can have as many parties to a contract as you like and
    > have
    > as many contracts for a party as you like.
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> I have a database with 4 tables. Table 1 holds the details of contracts.
    >> Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
    >> can have 1 2 or 3 parties apart from ourselves. I have created separate
    >> tables for the parties to hold information like contact names address
    >> etc.
    >> Is this the right formation for this database?
    >> Thanks
    >> Tony
    >>
    >>

    >
    >
     
  7. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Good grief no.

    contracts would be something like
    contractID
    contractTitle
    contractDetails
    ... (other stuff to do with a contract)

    parties would be something like
    partyID
    companyName
    contactName
    ... (other stuff to do with a party to a contract)

    contractsparties would be something like
    contractID
    partyID
    ... (other stuff to do with a contract and party combination)

    Data might look like this

    contracts
    -----------
    contractID contractTitle contractDetails
    1 Tiitle1 Do something
    2 Tiitle2 Do something else
    3 Tiitle3 Something else again

    parties
    --------
    partyID companyName contactName
    A Acme Ltd Fred Bloggs
    B Steptoe & Son HH Corbett
    C Specs & Co S Smith

    contractsparties
    -----------------
    contractID partyID
    1 A
    1 B
    2 A
    2 C
    3 A
    3 B
    3 C


    You could then do something like

    SELECT
    contracts.contractTitle,
    parties.companyName
    FROM
    contracts
    INNER JOIN
    contractsparties
    ON
    contracts.contractID = contractsparties.contractID
    INNER JOIN
    parties
    ON
    parties.partyID = contractsparties.partyID
    WHERE
    contracts.contractID = 2

    And get back
    contractTitle companyName
    ------------- ----------------
    Tiitle2 Acme Ltd
    Tiitle2 Specs & Co





    --

    Terry Kreft


    "Tony Williams" <tw@invalid.com> wrote in message
    news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    > Would the parties table then have to have fields like company1name,
    > company2name, company3name and then contact1name,contact2name,contact3name
    > etc
    > I htought that this was not a good way to set up a database ie having
    > repeated fields. What would be the structure of contractparties, how would
    > it link the parties to the contacts?
    > Thanks for your help
    > Tony
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > > Probably not. I would expect 3 tables
    > >
    > > contracts
    > > parties
    > > contractsparties
    > >
    > > Where
    > > contracts - holds the details about a contract
    > > parties - holds details about parties to contracts
    > > contractsparties - links the two together
    > >
    > > In this way you can have as many parties to a contract as you like and
    > > have
    > > as many contracts for a party as you like.
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Tony Williams" <tw@invalid.com> wrote in message
    > > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > >> I have a database with 4 tables. Table 1 holds the details of

    contracts.
    > >> Tables 2, 3 and 4 holds details of the parties to the contract.

    Contracts
    > >> can have 1 2 or 3 parties apart from ourselves. I have created separate
    > >> tables for the parties to hold information like contact names address
    > >> etc.
    > >> Is this the right formation for this database?
    > >> Thanks
    > >> Tony
    > >>
    > >>

    > >
    > >

    >
    >
     
  8. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    Thanks Terry I sit and work through your reply over the next couple of days
    and perhaps post any specific questions in the relevant newsgroup.
    Thanks again
    Tony
    PS I sent you an email recently asking if you had produced a copy of
    wzForms.zip for Access 2002. I tried installing the copy from the website
    and it says it was designed for a previous version? But the email kept
    bouncing back as undeliverable?

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > Good grief no.
    >
    > contracts would be something like
    > contractID
    > contractTitle
    > contractDetails
    > ... (other stuff to do with a contract)
    >
    > parties would be something like
    > partyID
    > companyName
    > contactName
    > ... (other stuff to do with a party to a contract)
    >
    > contractsparties would be something like
    > contractID
    > partyID
    > ... (other stuff to do with a contract and party combination)
    >
    > Data might look like this
    >
    > contracts
    > -----------
    > contractID contractTitle contractDetails
    > 1 Tiitle1 Do something
    > 2 Tiitle2 Do something else
    > 3 Tiitle3 Something else again
    >
    > parties
    > --------
    > partyID companyName contactName
    > A Acme Ltd Fred Bloggs
    > B Steptoe & Son HH Corbett
    > C Specs & Co S Smith
    >
    > contractsparties
    > -----------------
    > contractID partyID
    > 1 A
    > 1 B
    > 2 A
    > 2 C
    > 3 A
    > 3 B
    > 3 C
    >
    >
    > You could then do something like
    >
    > SELECT
    > contracts.contractTitle,
    > parties.companyName
    > FROM
    > contracts
    > INNER JOIN
    > contractsparties
    > ON
    > contracts.contractID = contractsparties.contractID
    > INNER JOIN
    > parties
    > ON
    > parties.partyID = contractsparties.partyID
    > WHERE
    > contracts.contractID = 2
    >
    > And get back
    > contractTitle companyName
    > ------------- ----------------
    > Tiitle2 Acme Ltd
    > Tiitle2 Specs & Co
    >
    >
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> Would the parties table then have to have fields like company1name,
    >> company2name, company3name and then
    >> contact1name,contact2name,contact3name
    >> etc
    >> I htought that this was not a good way to set up a database ie having
    >> repeated fields. What would be the structure of contractparties, how
    >> would
    >> it link the parties to the contacts?
    >> Thanks for your help
    >> Tony
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> > Probably not. I would expect 3 tables
    >> >
    >> > contracts
    >> > parties
    >> > contractsparties
    >> >
    >> > Where
    >> > contracts - holds the details about a contract
    >> > parties - holds details about parties to contracts
    >> > contractsparties - links the two together
    >> >
    >> > In this way you can have as many parties to a contract as you like and
    >> > have
    >> > as many contracts for a party as you like.
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> I have a database with 4 tables. Table 1 holds the details of

    > contracts.
    >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > Contracts
    >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> separate
    >> >> tables for the parties to hold information like contact names address
    >> >> etc.
    >> >> Is this the right formation for this database?
    >> >> Thanks
    >> >> Tony
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  9. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    Just one point,though I'm not sure I follow how I would create the
    contractparties table? Would the three tables be linked and therefore the
    contractparties table would be created automatically from the other two. Eg
    a new record in contracts would create the ID in contracts and in
    contractparties and a new record in parties would create an ID record in
    contractparties as well?
    Sorry to be so dim!!!
    Thanks
    Tony
    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > Good grief no.
    >
    > contracts would be something like
    > contractID
    > contractTitle
    > contractDetails
    > ... (other stuff to do with a contract)
    >
    > parties would be something like
    > partyID
    > companyName
    > contactName
    > ... (other stuff to do with a party to a contract)
    >
    > contractsparties would be something like
    > contractID
    > partyID
    > ... (other stuff to do with a contract and party combination)
    >
    > Data might look like this
    >
    > contracts
    > -----------
    > contractID contractTitle contractDetails
    > 1 Tiitle1 Do something
    > 2 Tiitle2 Do something else
    > 3 Tiitle3 Something else again
    >
    > parties
    > --------
    > partyID companyName contactName
    > A Acme Ltd Fred Bloggs
    > B Steptoe & Son HH Corbett
    > C Specs & Co S Smith
    >
    > contractsparties
    > -----------------
    > contractID partyID
    > 1 A
    > 1 B
    > 2 A
    > 2 C
    > 3 A
    > 3 B
    > 3 C
    >
    >
    > You could then do something like
    >
    > SELECT
    > contracts.contractTitle,
    > parties.companyName
    > FROM
    > contracts
    > INNER JOIN
    > contractsparties
    > ON
    > contracts.contractID = contractsparties.contractID
    > INNER JOIN
    > parties
    > ON
    > parties.partyID = contractsparties.partyID
    > WHERE
    > contracts.contractID = 2
    >
    > And get back
    > contractTitle companyName
    > ------------- ----------------
    > Tiitle2 Acme Ltd
    > Tiitle2 Specs & Co
    >
    >
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> Would the parties table then have to have fields like company1name,
    >> company2name, company3name and then
    >> contact1name,contact2name,contact3name
    >> etc
    >> I htought that this was not a good way to set up a database ie having
    >> repeated fields. What would be the structure of contractparties, how
    >> would
    >> it link the parties to the contacts?
    >> Thanks for your help
    >> Tony
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> > Probably not. I would expect 3 tables
    >> >
    >> > contracts
    >> > parties
    >> > contractsparties
    >> >
    >> > Where
    >> > contracts - holds the details about a contract
    >> > parties - holds details about parties to contracts
    >> > contractsparties - links the two together
    >> >
    >> > In this way you can have as many parties to a contract as you like and
    >> > have
    >> > as many contracts for a party as you like.
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> I have a database with 4 tables. Table 1 holds the details of

    > contracts.
    >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > Contracts
    >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> separate
    >> >> tables for the parties to hold information like contact names address
    >> >> etc.
    >> >> Is this the right formation for this database?
    >> >> Thanks
    >> >> Tony
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  10. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    Thinking more about this. Would this work if I just had two tables,
    contracts and parties and they were linked by say a field called txtrefnbr
    which was a key field in contracts and was a field in parties which could
    any number of parties to one contract which is what we want?
    Thanks
    Terry

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > Good grief no.
    >
    > contracts would be something like
    > contractID
    > contractTitle
    > contractDetails
    > ... (other stuff to do with a contract)
    >
    > parties would be something like
    > partyID
    > companyName
    > contactName
    > ... (other stuff to do with a party to a contract)
    >
    > contractsparties would be something like
    > contractID
    > partyID
    > ... (other stuff to do with a contract and party combination)
    >
    > Data might look like this
    >
    > contracts
    > -----------
    > contractID contractTitle contractDetails
    > 1 Tiitle1 Do something
    > 2 Tiitle2 Do something else
    > 3 Tiitle3 Something else again
    >
    > parties
    > --------
    > partyID companyName contactName
    > A Acme Ltd Fred Bloggs
    > B Steptoe & Son HH Corbett
    > C Specs & Co S Smith
    >
    > contractsparties
    > -----------------
    > contractID partyID
    > 1 A
    > 1 B
    > 2 A
    > 2 C
    > 3 A
    > 3 B
    > 3 C
    >
    >
    > You could then do something like
    >
    > SELECT
    > contracts.contractTitle,
    > parties.companyName
    > FROM
    > contracts
    > INNER JOIN
    > contractsparties
    > ON
    > contracts.contractID = contractsparties.contractID
    > INNER JOIN
    > parties
    > ON
    > parties.partyID = contractsparties.partyID
    > WHERE
    > contracts.contractID = 2
    >
    > And get back
    > contractTitle companyName
    > ------------- ----------------
    > Tiitle2 Acme Ltd
    > Tiitle2 Specs & Co
    >
    >
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> Would the parties table then have to have fields like company1name,
    >> company2name, company3name and then
    >> contact1name,contact2name,contact3name
    >> etc
    >> I htought that this was not a good way to set up a database ie having
    >> repeated fields. What would be the structure of contractparties, how
    >> would
    >> it link the parties to the contacts?
    >> Thanks for your help
    >> Tony
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> > Probably not. I would expect 3 tables
    >> >
    >> > contracts
    >> > parties
    >> > contractsparties
    >> >
    >> > Where
    >> > contracts - holds the details about a contract
    >> > parties - holds details about parties to contracts
    >> > contractsparties - links the two together
    >> >
    >> > In this way you can have as many parties to a contract as you like and
    >> > have
    >> > as many contracts for a party as you like.
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> I have a database with 4 tables. Table 1 holds the details of

    > contracts.
    >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > Contracts
    >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> separate
    >> >> tables for the parties to hold information like contact names address
    >> >> etc.
    >> >> Is this the right formation for this database?
    >> >> Thanks
    >> >> Tony
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  11. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    No.

    If you want to have multiple contacts on any givent contract, and you need
    any contact to be able to participate in multiple contracts, you need the 3
    tables already described by others in great detail.

    They are consistent in their answers for a reason. Heed them.

    "Tony Williams" <tw@invalid.com> wrote in message
    news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    > Thinking more about this. Would this work if I just had two tables,
    > contracts and parties and they were linked by say a field called txtrefnbr
    > which was a key field in contracts and was a field in parties which could
    > any number of parties to one contract which is what we want?
    > Thanks
    > Terry
    >
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    >> Good grief no.
    >>
    >> contracts would be something like
    >> contractID
    >> contractTitle
    >> contractDetails
    >> ... (other stuff to do with a contract)
    >>
    >> parties would be something like
    >> partyID
    >> companyName
    >> contactName
    >> ... (other stuff to do with a party to a contract)
    >>
    >> contractsparties would be something like
    >> contractID
    >> partyID
    >> ... (other stuff to do with a contract and party combination)
    >>
    >> Data might look like this
    >>
    >> contracts
    >> -----------
    >> contractID contractTitle contractDetails
    >> 1 Tiitle1 Do something
    >> 2 Tiitle2 Do something else
    >> 3 Tiitle3 Something else again
    >>
    >> parties
    >> --------
    >> partyID companyName contactName
    >> A Acme Ltd Fred Bloggs
    >> B Steptoe & Son HH Corbett
    >> C Specs & Co S Smith
    >>
    >> contractsparties
    >> -----------------
    >> contractID partyID
    >> 1 A
    >> 1 B
    >> 2 A
    >> 2 C
    >> 3 A
    >> 3 B
    >> 3 C
    >>
    >>
    >> You could then do something like
    >>
    >> SELECT
    >> contracts.contractTitle,
    >> parties.companyName
    >> FROM
    >> contracts
    >> INNER JOIN
    >> contractsparties
    >> ON
    >> contracts.contractID = contractsparties.contractID
    >> INNER JOIN
    >> parties
    >> ON
    >> parties.partyID = contractsparties.partyID
    >> WHERE
    >> contracts.contractID = 2
    >>
    >> And get back
    >> contractTitle companyName
    >> ------------- ----------------
    >> Tiitle2 Acme Ltd
    >> Tiitle2 Specs & Co
    >>
    >>
    >>
    >>
    >>
    >> --
    >>
    >> Terry Kreft
    >>
    >>
    >> "Tony Williams" <tw@invalid.com> wrote in message
    >> news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >>> Would the parties table then have to have fields like company1name,
    >>> company2name, company3name and then
    >>> contact1name,contact2name,contact3name
    >>> etc
    >>> I htought that this was not a good way to set up a database ie having
    >>> repeated fields. What would be the structure of contractparties, how
    >>> would
    >>> it link the parties to the contacts?
    >>> Thanks for your help
    >>> Tony
    >>> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >>> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >>> > Probably not. I would expect 3 tables
    >>> >
    >>> > contracts
    >>> > parties
    >>> > contractsparties
    >>> >
    >>> > Where
    >>> > contracts - holds the details about a contract
    >>> > parties - holds details about parties to contracts
    >>> > contractsparties - links the two together
    >>> >
    >>> > In this way you can have as many parties to a contract as you like and
    >>> > have
    >>> > as many contracts for a party as you like.
    >>> >
    >>> >
    >>> > --
    >>> >
    >>> > Terry Kreft
    >>> >
    >>> >
    >>> > "Tony Williams" <tw@invalid.com> wrote in message
    >>> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >>> >> I have a database with 4 tables. Table 1 holds the details of

    >> contracts.
    >>> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    >> Contracts
    >>> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >>> >> separate
    >>> >> tables for the parties to hold information like contact names address
    >>> >> etc.
    >>> >> Is this the right formation for this database?
    >>> >> Thanks
    >>> >> Tony
    >>> >>
    >>> >>
    >>> >
    >>> >
    >>>
    >>>

    >>
    >>

    >
    >
     
  12. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Yes.

    The schema I showed would allow any number of parties to participate in any
    number of contracts. This is called a many to many or M:M relationship.

    What you describe below would allow any number of parties to participate ina
    a single contract. This is called a one to many or 1:M relationship.



    --

    Terry Kreft


    "Tony Williams" <tw@invalid.com> wrote in message
    news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    > Thinking more about this. Would this work if I just had two tables,
    > contracts and parties and they were linked by say a field called txtrefnbr
    > which was a key field in contracts and was a field in parties which could
    > any number of parties to one contract which is what we want?
    > Thanks
    > Terry
    >
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > > Good grief no.
    > >
    > > contracts would be something like
    > > contractID
    > > contractTitle
    > > contractDetails
    > > ... (other stuff to do with a contract)
    > >
    > > parties would be something like
    > > partyID
    > > companyName
    > > contactName
    > > ... (other stuff to do with a party to a contract)
    > >
    > > contractsparties would be something like
    > > contractID
    > > partyID
    > > ... (other stuff to do with a contract and party combination)
    > >
    > > Data might look like this
    > >
    > > contracts
    > > -----------
    > > contractID contractTitle contractDetails
    > > 1 Tiitle1 Do something
    > > 2 Tiitle2 Do something else
    > > 3 Tiitle3 Something else again
    > >
    > > parties
    > > --------
    > > partyID companyName contactName
    > > A Acme Ltd Fred Bloggs
    > > B Steptoe & Son HH Corbett
    > > C Specs & Co S Smith
    > >
    > > contractsparties
    > > -----------------
    > > contractID partyID
    > > 1 A
    > > 1 B
    > > 2 A
    > > 2 C
    > > 3 A
    > > 3 B
    > > 3 C
    > >
    > >
    > > You could then do something like
    > >
    > > SELECT
    > > contracts.contractTitle,
    > > parties.companyName
    > > FROM
    > > contracts
    > > INNER JOIN
    > > contractsparties
    > > ON
    > > contracts.contractID = contractsparties.contractID
    > > INNER JOIN
    > > parties
    > > ON
    > > parties.partyID = contractsparties.partyID
    > > WHERE
    > > contracts.contractID = 2
    > >
    > > And get back
    > > contractTitle companyName
    > > ------------- ----------------
    > > Tiitle2 Acme Ltd
    > > Tiitle2 Specs & Co
    > >
    > >
    > >
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Tony Williams" <tw@invalid.com> wrote in message
    > > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    > >> Would the parties table then have to have fields like company1name,
    > >> company2name, company3name and then
    > >> contact1name,contact2name,contact3name
    > >> etc
    > >> I htought that this was not a good way to set up a database ie having
    > >> repeated fields. What would be the structure of contractparties, how
    > >> would
    > >> it link the parties to the contacts?
    > >> Thanks for your help
    > >> Tony
    > >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > >> > Probably not. I would expect 3 tables
    > >> >
    > >> > contracts
    > >> > parties
    > >> > contractsparties
    > >> >
    > >> > Where
    > >> > contracts - holds the details about a contract
    > >> > parties - holds details about parties to contracts
    > >> > contractsparties - links the two together
    > >> >
    > >> > In this way you can have as many parties to a contract as you like

    and
    > >> > have
    > >> > as many contracts for a party as you like.
    > >> >
    > >> >
    > >> > --
    > >> >
    > >> > Terry Kreft
    > >> >
    > >> >
    > >> > "Tony Williams" <tw@invalid.com> wrote in message
    > >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > >> >> I have a database with 4 tables. Table 1 holds the details of

    > > contracts.
    > >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > > Contracts
    > >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    > >> >> separate
    > >> >> tables for the parties to hold information like contact names

    address
    > >> >> etc.
    > >> >> Is this the right formation for this database?
    > >> >> Thanks
    > >> >> Tony
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  13. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    wzForms
    -----------
    You can do this yourself
    1) Create a new mda file call it wzForms.mda
    (See note below)
    2) Go to the Tools menu and select Options
    In the dialog
    a) select the View tab
    b) Tick "Hidden objects" and "System objects"
    c) Click OK
    3) Go to the File Menu and select "Get External Data/Import"
    a) Navigate to and select the A97 version of wzForms.mda
    b) On the Tables tab select all the tables except the ones that
    start MSys
    c) On the Queries, Forms, Reports, Macros and Modules tabs select
    all
    e) Click OK
    4) Press Ctrl-G to go to the debug window
    5) Enter the following in the debug window and hit the enter key
    CodeDb.Properties.Append Codedb.CreateProperty("RevNo", dbText,
    "1.0")
    6) Choose the "Debug/Compile wzForms" menu option
    7) Close the code window
    8) In the database window choose "Tools/Database Utilities/Compact and
    Repair" menu option
    9) Close the add-in
    10) Install the Add-In

    Note: If you do not keep the name the same you will have to edit the
    USysRegInfo table to get the Ad-In to register.

    --

    Terry Kreft


    "Tony Williams" <tw@invalid.com> wrote in message
    news:OhJnDu1fGHA.4568@TK2MSFTNGP03.phx.gbl...
    > Thanks Terry I sit and work through your reply over the next couple of

    days
    > and perhaps post any specific questions in the relevant newsgroup.
    > Thanks again
    > Tony
    > PS I sent you an email recently asking if you had produced a copy of
    > wzForms.zip for Access 2002. I tried installing the copy from the website
    > and it says it was designed for a previous version? But the email kept
    > bouncing back as undeliverable?
    >
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > > Good grief no.
    > >
    > > contracts would be something like
    > > contractID
    > > contractTitle
    > > contractDetails
    > > ... (other stuff to do with a contract)
    > >
    > > parties would be something like
    > > partyID
    > > companyName
    > > contactName
    > > ... (other stuff to do with a party to a contract)
    > >
    > > contractsparties would be something like
    > > contractID
    > > partyID
    > > ... (other stuff to do with a contract and party combination)
    > >
    > > Data might look like this
    > >
    > > contracts
    > > -----------
    > > contractID contractTitle contractDetails
    > > 1 Tiitle1 Do something
    > > 2 Tiitle2 Do something else
    > > 3 Tiitle3 Something else again
    > >
    > > parties
    > > --------
    > > partyID companyName contactName
    > > A Acme Ltd Fred Bloggs
    > > B Steptoe & Son HH Corbett
    > > C Specs & Co S Smith
    > >
    > > contractsparties
    > > -----------------
    > > contractID partyID
    > > 1 A
    > > 1 B
    > > 2 A
    > > 2 C
    > > 3 A
    > > 3 B
    > > 3 C
    > >
    > >
    > > You could then do something like
    > >
    > > SELECT
    > > contracts.contractTitle,
    > > parties.companyName
    > > FROM
    > > contracts
    > > INNER JOIN
    > > contractsparties
    > > ON
    > > contracts.contractID = contractsparties.contractID
    > > INNER JOIN
    > > parties
    > > ON
    > > parties.partyID = contractsparties.partyID
    > > WHERE
    > > contracts.contractID = 2
    > >
    > > And get back
    > > contractTitle companyName
    > > ------------- ----------------
    > > Tiitle2 Acme Ltd
    > > Tiitle2 Specs & Co
    > >
    > >
    > >
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Tony Williams" <tw@invalid.com> wrote in message
    > > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    > >> Would the parties table then have to have fields like company1name,
    > >> company2name, company3name and then
    > >> contact1name,contact2name,contact3name
    > >> etc
    > >> I htought that this was not a good way to set up a database ie having
    > >> repeated fields. What would be the structure of contractparties, how
    > >> would
    > >> it link the parties to the contacts?
    > >> Thanks for your help
    > >> Tony
    > >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > >> > Probably not. I would expect 3 tables
    > >> >
    > >> > contracts
    > >> > parties
    > >> > contractsparties
    > >> >
    > >> > Where
    > >> > contracts - holds the details about a contract
    > >> > parties - holds details about parties to contracts
    > >> > contractsparties - links the two together
    > >> >
    > >> > In this way you can have as many parties to a contract as you like

    and
    > >> > have
    > >> > as many contracts for a party as you like.
    > >> >
    > >> >
    > >> > --
    > >> >
    > >> > Terry Kreft
    > >> >
    > >> >
    > >> > "Tony Williams" <tw@invalid.com> wrote in message
    > >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > >> >> I have a database with 4 tables. Table 1 holds the details of

    > > contracts.
    > >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > > Contracts
    > >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    > >> >> separate
    > >> >> tables for the parties to hold information like contact names

    address
    > >> >> etc.
    > >> >> Is this the right formation for this database?
    > >> >> Thanks
    > >> >> Tony
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  14. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Close, you would create records in contracts and parties and then create
    records in contractsparties to associate the contracts and the parties with
    each other.

    --

    Terry Kreft


    "Tony Williams" <tw@invalid.com> wrote in message
    news:O3CHcx1fGHA.5092@TK2MSFTNGP04.phx.gbl...
    > Just one point,though I'm not sure I follow how I would create the
    > contractparties table? Would the three tables be linked and therefore the
    > contractparties table would be created automatically from the other two.

    Eg
    > a new record in contracts would create the ID in contracts and in
    > contractparties and a new record in parties would create an ID record in
    > contractparties as well?
    > Sorry to be so dim!!!
    > Thanks
    > Tony
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > > Good grief no.
    > >
    > > contracts would be something like
    > > contractID
    > > contractTitle
    > > contractDetails
    > > ... (other stuff to do with a contract)
    > >
    > > parties would be something like
    > > partyID
    > > companyName
    > > contactName
    > > ... (other stuff to do with a party to a contract)
    > >
    > > contractsparties would be something like
    > > contractID
    > > partyID
    > > ... (other stuff to do with a contract and party combination)
    > >
    > > Data might look like this
    > >
    > > contracts
    > > -----------
    > > contractID contractTitle contractDetails
    > > 1 Tiitle1 Do something
    > > 2 Tiitle2 Do something else
    > > 3 Tiitle3 Something else again
    > >
    > > parties
    > > --------
    > > partyID companyName contactName
    > > A Acme Ltd Fred Bloggs
    > > B Steptoe & Son HH Corbett
    > > C Specs & Co S Smith
    > >
    > > contractsparties
    > > -----------------
    > > contractID partyID
    > > 1 A
    > > 1 B
    > > 2 A
    > > 2 C
    > > 3 A
    > > 3 B
    > > 3 C
    > >
    > >
    > > You could then do something like
    > >
    > > SELECT
    > > contracts.contractTitle,
    > > parties.companyName
    > > FROM
    > > contracts
    > > INNER JOIN
    > > contractsparties
    > > ON
    > > contracts.contractID = contractsparties.contractID
    > > INNER JOIN
    > > parties
    > > ON
    > > parties.partyID = contractsparties.partyID
    > > WHERE
    > > contracts.contractID = 2
    > >
    > > And get back
    > > contractTitle companyName
    > > ------------- ----------------
    > > Tiitle2 Acme Ltd
    > > Tiitle2 Specs & Co
    > >
    > >
    > >
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Tony Williams" <tw@invalid.com> wrote in message
    > > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    > >> Would the parties table then have to have fields like company1name,
    > >> company2name, company3name and then
    > >> contact1name,contact2name,contact3name
    > >> etc
    > >> I htought that this was not a good way to set up a database ie having
    > >> repeated fields. What would be the structure of contractparties, how
    > >> would
    > >> it link the parties to the contacts?
    > >> Thanks for your help
    > >> Tony
    > >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > >> > Probably not. I would expect 3 tables
    > >> >
    > >> > contracts
    > >> > parties
    > >> > contractsparties
    > >> >
    > >> > Where
    > >> > contracts - holds the details about a contract
    > >> > parties - holds details about parties to contracts
    > >> > contractsparties - links the two together
    > >> >
    > >> > In this way you can have as many parties to a contract as you like

    and
    > >> > have
    > >> > as many contracts for a party as you like.
    > >> >
    > >> >
    > >> > --
    > >> >
    > >> > Terry Kreft
    > >> >
    > >> >
    > >> > "Tony Williams" <tw@invalid.com> wrote in message
    > >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > >> >> I have a database with 4 tables. Table 1 holds the details of

    > > contracts.
    > >> >> Tables 2, 3 and 4 holds details of the parties to the contract.

    > > Contracts
    > >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    > >> >> separate
    > >> >> tables for the parties to hold information like contact names

    address
    > >> >> etc.
    > >> >> Is this the right formation for this database?
    > >> >> Thanks
    > >> >> Tony
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  15. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    TK: I'm confused. You are one of the presenters of the 3-table solution,
    but you answer "Yes" to the poster's question about whether he can do what
    he wants with 2 tables. ???

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:erFGlV2fGHA.764@TK2MSFTNGP05.phx.gbl...
    > Yes.
    >
    > The schema I showed would allow any number of parties to participate in
    > any
    > number of contracts. This is called a many to many or M:M relationship.
    >
    > What you describe below would allow any number of parties to participate
    > ina
    > a single contract. This is called a one to many or 1:M relationship.
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    >> Thinking more about this. Would this work if I just had two tables,
    >> contracts and parties and they were linked by say a field called
    >> txtrefnbr
    >> which was a key field in contracts and was a field in parties which could
    >> any number of parties to one contract which is what we want?
    >> Thanks
    >> Terry
    >>
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    >> > Good grief no.
    >> >
    >> > contracts would be something like
    >> > contractID
    >> > contractTitle
    >> > contractDetails
    >> > ... (other stuff to do with a contract)
    >> >
    >> > parties would be something like
    >> > partyID
    >> > companyName
    >> > contactName
    >> > ... (other stuff to do with a party to a contract)
    >> >
    >> > contractsparties would be something like
    >> > contractID
    >> > partyID
    >> > ... (other stuff to do with a contract and party combination)
    >> >
    >> > Data might look like this
    >> >
    >> > contracts
    >> > -----------
    >> > contractID contractTitle contractDetails
    >> > 1 Tiitle1 Do something
    >> > 2 Tiitle2 Do something else
    >> > 3 Tiitle3 Something else again
    >> >
    >> > parties
    >> > --------
    >> > partyID companyName contactName
    >> > A Acme Ltd Fred Bloggs
    >> > B Steptoe & Son HH Corbett
    >> > C Specs & Co S Smith
    >> >
    >> > contractsparties
    >> > -----------------
    >> > contractID partyID
    >> > 1 A
    >> > 1 B
    >> > 2 A
    >> > 2 C
    >> > 3 A
    >> > 3 B
    >> > 3 C
    >> >
    >> >
    >> > You could then do something like
    >> >
    >> > SELECT
    >> > contracts.contractTitle,
    >> > parties.companyName
    >> > FROM
    >> > contracts
    >> > INNER JOIN
    >> > contractsparties
    >> > ON
    >> > contracts.contractID = contractsparties.contractID
    >> > INNER JOIN
    >> > parties
    >> > ON
    >> > parties.partyID = contractsparties.partyID
    >> > WHERE
    >> > contracts.contractID = 2
    >> >
    >> > And get back
    >> > contractTitle companyName
    >> > ------------- ----------------
    >> > Tiitle2 Acme Ltd
    >> > Tiitle2 Specs & Co
    >> >
    >> >
    >> >
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> >> Would the parties table then have to have fields like company1name,
    >> >> company2name, company3name and then
    >> >> contact1name,contact2name,contact3name
    >> >> etc
    >> >> I htought that this was not a good way to set up a database ie having
    >> >> repeated fields. What would be the structure of contractparties, how
    >> >> would
    >> >> it link the parties to the contacts?
    >> >> Thanks for your help
    >> >> Tony
    >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> >> > Probably not. I would expect 3 tables
    >> >> >
    >> >> > contracts
    >> >> > parties
    >> >> > contractsparties
    >> >> >
    >> >> > Where
    >> >> > contracts - holds the details about a contract
    >> >> > parties - holds details about parties to contracts
    >> >> > contractsparties - links the two together
    >> >> >
    >> >> > In this way you can have as many parties to a contract as you like

    > and
    >> >> > have
    >> >> > as many contracts for a party as you like.
    >> >> >
    >> >> >
    >> >> > --
    >> >> >
    >> >> > Terry Kreft
    >> >> >
    >> >> >
    >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> >> I have a database with 4 tables. Table 1 holds the details of
    >> > contracts.
    >> >> >> Tables 2, 3 and 4 holds details of the parties to the contract.
    >> > Contracts
    >> >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> >> separate
    >> >> >> tables for the parties to hold information like contact names

    > address
    >> >> >> etc.
    >> >> >> Is this the right formation for this database?
    >> >> >> Thanks
    >> >> >> Tony
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  16. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Rick,
    If you re-read the OPs post where you answered No and I answered Yes <g>,
    you'll see he's describing a 1:M relationship as the desired one.

    I'm as surprised at this as you, I suspect, I would have expected a M:M, but
    the OP should be better at assessing his needs than I am, if he says he
    wants 1:M then I'm not going to argue.



    --

    Terry Kreft


    "Rick Wannall" <cwannall@yahoo.com> wrote in message
    news:ZF6dg.97952$dW3.58254@newssvr21.news.prodigy.com...
    > TK: I'm confused. You are one of the presenters of the 3-table solution,
    > but you answer "Yes" to the poster's question about whether he can do what
    > he wants with 2 tables. ???
    >
    > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > news:erFGlV2fGHA.764@TK2MSFTNGP05.phx.gbl...
    > > Yes.
    > >
    > > The schema I showed would allow any number of parties to participate in
    > > any
    > > number of contracts. This is called a many to many or M:M relationship.
    > >
    > > What you describe below would allow any number of parties to participate
    > > ina
    > > a single contract. This is called a one to many or 1:M relationship.
    > >
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Tony Williams" <tw@invalid.com> wrote in message
    > > news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    > >> Thinking more about this. Would this work if I just had two tables,
    > >> contracts and parties and they were linked by say a field called
    > >> txtrefnbr
    > >> which was a key field in contracts and was a field in parties which

    could
    > >> any number of parties to one contract which is what we want?
    > >> Thanks
    > >> Terry
    > >>
    > >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > >> news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    > >> > Good grief no.
    > >> >
    > >> > contracts would be something like
    > >> > contractID
    > >> > contractTitle
    > >> > contractDetails
    > >> > ... (other stuff to do with a contract)
    > >> >
    > >> > parties would be something like
    > >> > partyID
    > >> > companyName
    > >> > contactName
    > >> > ... (other stuff to do with a party to a contract)
    > >> >
    > >> > contractsparties would be something like
    > >> > contractID
    > >> > partyID
    > >> > ... (other stuff to do with a contract and party combination)
    > >> >
    > >> > Data might look like this
    > >> >
    > >> > contracts
    > >> > -----------
    > >> > contractID contractTitle contractDetails
    > >> > 1 Tiitle1 Do something
    > >> > 2 Tiitle2 Do something else
    > >> > 3 Tiitle3 Something else again
    > >> >
    > >> > parties
    > >> > --------
    > >> > partyID companyName contactName
    > >> > A Acme Ltd Fred Bloggs
    > >> > B Steptoe & Son HH Corbett
    > >> > C Specs & Co S Smith
    > >> >
    > >> > contractsparties
    > >> > -----------------
    > >> > contractID partyID
    > >> > 1 A
    > >> > 1 B
    > >> > 2 A
    > >> > 2 C
    > >> > 3 A
    > >> > 3 B
    > >> > 3 C
    > >> >
    > >> >
    > >> > You could then do something like
    > >> >
    > >> > SELECT
    > >> > contracts.contractTitle,
    > >> > parties.companyName
    > >> > FROM
    > >> > contracts
    > >> > INNER JOIN
    > >> > contractsparties
    > >> > ON
    > >> > contracts.contractID = contractsparties.contractID
    > >> > INNER JOIN
    > >> > parties
    > >> > ON
    > >> > parties.partyID = contractsparties.partyID
    > >> > WHERE
    > >> > contracts.contractID = 2
    > >> >
    > >> > And get back
    > >> > contractTitle companyName
    > >> > ------------- ----------------
    > >> > Tiitle2 Acme Ltd
    > >> > Tiitle2 Specs & Co
    > >> >
    > >> >
    > >> >
    > >> >
    > >> >
    > >> > --
    > >> >
    > >> > Terry Kreft
    > >> >
    > >> >
    > >> > "Tony Williams" <tw@invalid.com> wrote in message
    > >> > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    > >> >> Would the parties table then have to have fields like company1name,
    > >> >> company2name, company3name and then
    > >> >> contact1name,contact2name,contact3name
    > >> >> etc
    > >> >> I htought that this was not a good way to set up a database ie

    having
    > >> >> repeated fields. What would be the structure of contractparties, how
    > >> >> would
    > >> >> it link the parties to the contacts?
    > >> >> Thanks for your help
    > >> >> Tony
    > >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    > >> >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    > >> >> > Probably not. I would expect 3 tables
    > >> >> >
    > >> >> > contracts
    > >> >> > parties
    > >> >> > contractsparties
    > >> >> >
    > >> >> > Where
    > >> >> > contracts - holds the details about a contract
    > >> >> > parties - holds details about parties to contracts
    > >> >> > contractsparties - links the two together
    > >> >> >
    > >> >> > In this way you can have as many parties to a contract as you like

    > > and
    > >> >> > have
    > >> >> > as many contracts for a party as you like.
    > >> >> >
    > >> >> >
    > >> >> > --
    > >> >> >
    > >> >> > Terry Kreft
    > >> >> >
    > >> >> >
    > >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    > >> >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > >> >> >> I have a database with 4 tables. Table 1 holds the details of
    > >> > contracts.
    > >> >> >> Tables 2, 3 and 4 holds details of the parties to the contract.
    > >> > Contracts
    > >> >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    > >> >> >> separate
    > >> >> >> tables for the parties to hold information like contact names

    > > address
    > >> >> >> etc.
    > >> >> >> Is this the right formation for this database?
    > >> >> >> Thanks
    > >> >> >> Tony
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
  17. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    Thanks Terry
    Tony
    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:uGXNYv2fGHA.3572@TK2MSFTNGP04.phx.gbl...
    > wzForms
    > -----------
    > You can do this yourself
    > 1) Create a new mda file call it wzForms.mda
    > (See note below)
    > 2) Go to the Tools menu and select Options
    > In the dialog
    > a) select the View tab
    > b) Tick "Hidden objects" and "System objects"
    > c) Click OK
    > 3) Go to the File Menu and select "Get External Data/Import"
    > a) Navigate to and select the A97 version of wzForms.mda
    > b) On the Tables tab select all the tables except the ones that
    > start MSys
    > c) On the Queries, Forms, Reports, Macros and Modules tabs select
    > all
    > e) Click OK
    > 4) Press Ctrl-G to go to the debug window
    > 5) Enter the following in the debug window and hit the enter key
    > CodeDb.Properties.Append Codedb.CreateProperty("RevNo", dbText,
    > "1.0")
    > 6) Choose the "Debug/Compile wzForms" menu option
    > 7) Close the code window
    > 8) In the database window choose "Tools/Database Utilities/Compact and
    > Repair" menu option
    > 9) Close the add-in
    > 10) Install the Add-In
    >
    > Note: If you do not keep the name the same you will have to edit the
    > USysRegInfo table to get the Ad-In to register.
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Tony Williams" <tw@invalid.com> wrote in message
    > news:OhJnDu1fGHA.4568@TK2MSFTNGP03.phx.gbl...
    >> Thanks Terry I sit and work through your reply over the next couple of

    > days
    >> and perhaps post any specific questions in the relevant newsgroup.
    >> Thanks again
    >> Tony
    >> PS I sent you an email recently asking if you had produced a copy of
    >> wzForms.zip for Access 2002. I tried installing the copy from the website
    >> and it says it was designed for a previous version? But the email kept
    >> bouncing back as undeliverable?
    >>
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    >> > Good grief no.
    >> >
    >> > contracts would be something like
    >> > contractID
    >> > contractTitle
    >> > contractDetails
    >> > ... (other stuff to do with a contract)
    >> >
    >> > parties would be something like
    >> > partyID
    >> > companyName
    >> > contactName
    >> > ... (other stuff to do with a party to a contract)
    >> >
    >> > contractsparties would be something like
    >> > contractID
    >> > partyID
    >> > ... (other stuff to do with a contract and party combination)
    >> >
    >> > Data might look like this
    >> >
    >> > contracts
    >> > -----------
    >> > contractID contractTitle contractDetails
    >> > 1 Tiitle1 Do something
    >> > 2 Tiitle2 Do something else
    >> > 3 Tiitle3 Something else again
    >> >
    >> > parties
    >> > --------
    >> > partyID companyName contactName
    >> > A Acme Ltd Fred Bloggs
    >> > B Steptoe & Son HH Corbett
    >> > C Specs & Co S Smith
    >> >
    >> > contractsparties
    >> > -----------------
    >> > contractID partyID
    >> > 1 A
    >> > 1 B
    >> > 2 A
    >> > 2 C
    >> > 3 A
    >> > 3 B
    >> > 3 C
    >> >
    >> >
    >> > You could then do something like
    >> >
    >> > SELECT
    >> > contracts.contractTitle,
    >> > parties.companyName
    >> > FROM
    >> > contracts
    >> > INNER JOIN
    >> > contractsparties
    >> > ON
    >> > contracts.contractID = contractsparties.contractID
    >> > INNER JOIN
    >> > parties
    >> > ON
    >> > parties.partyID = contractsparties.partyID
    >> > WHERE
    >> > contracts.contractID = 2
    >> >
    >> > And get back
    >> > contractTitle companyName
    >> > ------------- ----------------
    >> > Tiitle2 Acme Ltd
    >> > Tiitle2 Specs & Co
    >> >
    >> >
    >> >
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> >> Would the parties table then have to have fields like company1name,
    >> >> company2name, company3name and then
    >> >> contact1name,contact2name,contact3name
    >> >> etc
    >> >> I htought that this was not a good way to set up a database ie having
    >> >> repeated fields. What would be the structure of contractparties, how
    >> >> would
    >> >> it link the parties to the contacts?
    >> >> Thanks for your help
    >> >> Tony
    >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> >> > Probably not. I would expect 3 tables
    >> >> >
    >> >> > contracts
    >> >> > parties
    >> >> > contractsparties
    >> >> >
    >> >> > Where
    >> >> > contracts - holds the details about a contract
    >> >> > parties - holds details about parties to contracts
    >> >> > contractsparties - links the two together
    >> >> >
    >> >> > In this way you can have as many parties to a contract as you like

    > and
    >> >> > have
    >> >> > as many contracts for a party as you like.
    >> >> >
    >> >> >
    >> >> > --
    >> >> >
    >> >> > Terry Kreft
    >> >> >
    >> >> >
    >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> >> I have a database with 4 tables. Table 1 holds the details of
    >> > contracts.
    >> >> >> Tables 2, 3 and 4 holds details of the parties to the contract.
    >> > Contracts
    >> >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> >> separate
    >> >> >> tables for the parties to hold information like contact names

    > address
    >> >> >> etc.
    >> >> >> Is this the right formation for this database?
    >> >> >> Thanks
    >> >> >> Tony
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  18. Tony Williams

    Tony Williams
    Expand Collapse
    Guest

    What we have is a set of contracts where there can be upto 3 parties to each
    contract but we wouldn't have a party to any number of contracts so I think
    yes I have a 1 to many relationship?
    Thanks for your guidance Terry
    Tony
    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:eLYfzl8fGHA.3468@TK2MSFTNGP03.phx.gbl...
    > Rick,
    > If you re-read the OPs post where you answered No and I answered Yes <g>,
    > you'll see he's describing a 1:M relationship as the desired one.
    >
    > I'm as surprised at this as you, I suspect, I would have expected a M:M,
    > but
    > the OP should be better at assessing his needs than I am, if he says he
    > wants 1:M then I'm not going to argue.
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Rick Wannall" <cwannall@yahoo.com> wrote in message
    > news:ZF6dg.97952$dW3.58254@newssvr21.news.prodigy.com...
    >> TK: I'm confused. You are one of the presenters of the 3-table solution,
    >> but you answer "Yes" to the poster's question about whether he can do
    >> what
    >> he wants with 2 tables. ???
    >>
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:erFGlV2fGHA.764@TK2MSFTNGP05.phx.gbl...
    >> > Yes.
    >> >
    >> > The schema I showed would allow any number of parties to participate in
    >> > any
    >> > number of contracts. This is called a many to many or M:M relationship.
    >> >
    >> > What you describe below would allow any number of parties to
    >> > participate
    >> > ina
    >> > a single contract. This is called a one to many or 1:M relationship.
    >> >
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    >> >> Thinking more about this. Would this work if I just had two tables,
    >> >> contracts and parties and they were linked by say a field called
    >> >> txtrefnbr
    >> >> which was a key field in contracts and was a field in parties which

    > could
    >> >> any number of parties to one contract which is what we want?
    >> >> Thanks
    >> >> Terry
    >> >>
    >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    >> >> > Good grief no.
    >> >> >
    >> >> > contracts would be something like
    >> >> > contractID
    >> >> > contractTitle
    >> >> > contractDetails
    >> >> > ... (other stuff to do with a contract)
    >> >> >
    >> >> > parties would be something like
    >> >> > partyID
    >> >> > companyName
    >> >> > contactName
    >> >> > ... (other stuff to do with a party to a contract)
    >> >> >
    >> >> > contractsparties would be something like
    >> >> > contractID
    >> >> > partyID
    >> >> > ... (other stuff to do with a contract and party combination)
    >> >> >
    >> >> > Data might look like this
    >> >> >
    >> >> > contracts
    >> >> > -----------
    >> >> > contractID contractTitle contractDetails
    >> >> > 1 Tiitle1 Do something
    >> >> > 2 Tiitle2 Do something else
    >> >> > 3 Tiitle3 Something else again
    >> >> >
    >> >> > parties
    >> >> > --------
    >> >> > partyID companyName contactName
    >> >> > A Acme Ltd Fred Bloggs
    >> >> > B Steptoe & Son HH Corbett
    >> >> > C Specs & Co S Smith
    >> >> >
    >> >> > contractsparties
    >> >> > -----------------
    >> >> > contractID partyID
    >> >> > 1 A
    >> >> > 1 B
    >> >> > 2 A
    >> >> > 2 C
    >> >> > 3 A
    >> >> > 3 B
    >> >> > 3 C
    >> >> >
    >> >> >
    >> >> > You could then do something like
    >> >> >
    >> >> > SELECT
    >> >> > contracts.contractTitle,
    >> >> > parties.companyName
    >> >> > FROM
    >> >> > contracts
    >> >> > INNER JOIN
    >> >> > contractsparties
    >> >> > ON
    >> >> > contracts.contractID = contractsparties.contractID
    >> >> > INNER JOIN
    >> >> > parties
    >> >> > ON
    >> >> > parties.partyID = contractsparties.partyID
    >> >> > WHERE
    >> >> > contracts.contractID = 2
    >> >> >
    >> >> > And get back
    >> >> > contractTitle companyName
    >> >> > ------------- ----------------
    >> >> > Tiitle2 Acme Ltd
    >> >> > Tiitle2 Specs & Co
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >> > --
    >> >> >
    >> >> > Terry Kreft
    >> >> >
    >> >> >
    >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> >> >> Would the parties table then have to have fields like company1name,
    >> >> >> company2name, company3name and then
    >> >> >> contact1name,contact2name,contact3name
    >> >> >> etc
    >> >> >> I htought that this was not a good way to set up a database ie

    > having
    >> >> >> repeated fields. What would be the structure of contractparties,
    >> >> >> how
    >> >> >> would
    >> >> >> it link the parties to the contacts?
    >> >> >> Thanks for your help
    >> >> >> Tony
    >> >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> >> >> > Probably not. I would expect 3 tables
    >> >> >> >
    >> >> >> > contracts
    >> >> >> > parties
    >> >> >> > contractsparties
    >> >> >> >
    >> >> >> > Where
    >> >> >> > contracts - holds the details about a contract
    >> >> >> > parties - holds details about parties to contracts
    >> >> >> > contractsparties - links the two together
    >> >> >> >
    >> >> >> > In this way you can have as many parties to a contract as you
    >> >> >> > like
    >> > and
    >> >> >> > have
    >> >> >> > as many contracts for a party as you like.
    >> >> >> >
    >> >> >> >
    >> >> >> > --
    >> >> >> >
    >> >> >> > Terry Kreft
    >> >> >> >
    >> >> >> >
    >> >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> >> >> I have a database with 4 tables. Table 1 holds the details of
    >> >> > contracts.
    >> >> >> >> Tables 2, 3 and 4 holds details of the parties to the contract.
    >> >> > Contracts
    >> >> >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> >> >> separate
    >> >> >> >> tables for the parties to hold information like contact names
    >> > address
    >> >> >> >> etc.
    >> >> >> >> Is this the right formation for this database?
    >> >> >> >> Thanks
    >> >> >> >> Tony
    >> >> >> >>
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
  19. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Got it.

    "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    news:eLYfzl8fGHA.3468@TK2MSFTNGP03.phx.gbl...
    > Rick,
    > If you re-read the OPs post where you answered No and I answered Yes <g>,
    > you'll see he's describing a 1:M relationship as the desired one.
    >
    > I'm as surprised at this as you, I suspect, I would have expected a M:M,
    > but
    > the OP should be better at assessing his needs than I am, if he says he
    > wants 1:M then I'm not going to argue.
    >
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Rick Wannall" <cwannall@yahoo.com> wrote in message
    > news:ZF6dg.97952$dW3.58254@newssvr21.news.prodigy.com...
    >> TK: I'm confused. You are one of the presenters of the 3-table solution,
    >> but you answer "Yes" to the poster's question about whether he can do
    >> what
    >> he wants with 2 tables. ???
    >>
    >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> news:erFGlV2fGHA.764@TK2MSFTNGP05.phx.gbl...
    >> > Yes.
    >> >
    >> > The schema I showed would allow any number of parties to participate in
    >> > any
    >> > number of contracts. This is called a many to many or M:M relationship.
    >> >
    >> > What you describe below would allow any number of parties to
    >> > participate
    >> > ina
    >> > a single contract. This is called a one to many or 1:M relationship.
    >> >
    >> >
    >> >
    >> > --
    >> >
    >> > Terry Kreft
    >> >
    >> >
    >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> > news:eBCEgC2fGHA.1264@TK2MSFTNGP05.phx.gbl...
    >> >> Thinking more about this. Would this work if I just had two tables,
    >> >> contracts and parties and they were linked by say a field called
    >> >> txtrefnbr
    >> >> which was a key field in contracts and was a field in parties which

    > could
    >> >> any number of parties to one contract which is what we want?
    >> >> Thanks
    >> >> Terry
    >> >>
    >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> news:OMJSRb1fGHA.3860@TK2MSFTNGP02.phx.gbl...
    >> >> > Good grief no.
    >> >> >
    >> >> > contracts would be something like
    >> >> > contractID
    >> >> > contractTitle
    >> >> > contractDetails
    >> >> > ... (other stuff to do with a contract)
    >> >> >
    >> >> > parties would be something like
    >> >> > partyID
    >> >> > companyName
    >> >> > contactName
    >> >> > ... (other stuff to do with a party to a contract)
    >> >> >
    >> >> > contractsparties would be something like
    >> >> > contractID
    >> >> > partyID
    >> >> > ... (other stuff to do with a contract and party combination)
    >> >> >
    >> >> > Data might look like this
    >> >> >
    >> >> > contracts
    >> >> > -----------
    >> >> > contractID contractTitle contractDetails
    >> >> > 1 Tiitle1 Do something
    >> >> > 2 Tiitle2 Do something else
    >> >> > 3 Tiitle3 Something else again
    >> >> >
    >> >> > parties
    >> >> > --------
    >> >> > partyID companyName contactName
    >> >> > A Acme Ltd Fred Bloggs
    >> >> > B Steptoe & Son HH Corbett
    >> >> > C Specs & Co S Smith
    >> >> >
    >> >> > contractsparties
    >> >> > -----------------
    >> >> > contractID partyID
    >> >> > 1 A
    >> >> > 1 B
    >> >> > 2 A
    >> >> > 2 C
    >> >> > 3 A
    >> >> > 3 B
    >> >> > 3 C
    >> >> >
    >> >> >
    >> >> > You could then do something like
    >> >> >
    >> >> > SELECT
    >> >> > contracts.contractTitle,
    >> >> > parties.companyName
    >> >> > FROM
    >> >> > contracts
    >> >> > INNER JOIN
    >> >> > contractsparties
    >> >> > ON
    >> >> > contracts.contractID = contractsparties.contractID
    >> >> > INNER JOIN
    >> >> > parties
    >> >> > ON
    >> >> > parties.partyID = contractsparties.partyID
    >> >> > WHERE
    >> >> > contracts.contractID = 2
    >> >> >
    >> >> > And get back
    >> >> > contractTitle companyName
    >> >> > ------------- ----------------
    >> >> > Tiitle2 Acme Ltd
    >> >> > Tiitle2 Specs & Co
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >> >
    >> >> > --
    >> >> >
    >> >> > Terry Kreft
    >> >> >
    >> >> >
    >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> > news:ehNQr$0fGHA.2456@TK2MSFTNGP04.phx.gbl...
    >> >> >> Would the parties table then have to have fields like company1name,
    >> >> >> company2name, company3name and then
    >> >> >> contact1name,contact2name,contact3name
    >> >> >> etc
    >> >> >> I htought that this was not a good way to set up a database ie

    > having
    >> >> >> repeated fields. What would be the structure of contractparties,
    >> >> >> how
    >> >> >> would
    >> >> >> it link the parties to the contacts?
    >> >> >> Thanks for your help
    >> >> >> Tony
    >> >> >> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message
    >> >> >> news:e0GUu40fGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> >> >> > Probably not. I would expect 3 tables
    >> >> >> >
    >> >> >> > contracts
    >> >> >> > parties
    >> >> >> > contractsparties
    >> >> >> >
    >> >> >> > Where
    >> >> >> > contracts - holds the details about a contract
    >> >> >> > parties - holds details about parties to contracts
    >> >> >> > contractsparties - links the two together
    >> >> >> >
    >> >> >> > In this way you can have as many parties to a contract as you
    >> >> >> > like
    >> > and
    >> >> >> > have
    >> >> >> > as many contracts for a party as you like.
    >> >> >> >
    >> >> >> >
    >> >> >> > --
    >> >> >> >
    >> >> >> > Terry Kreft
    >> >> >> >
    >> >> >> >
    >> >> >> > "Tony Williams" <tw@invalid.com> wrote in message
    >> >> >> > news:ejkeGw0fGHA.3572@TK2MSFTNGP04.phx.gbl...
    >> >> >> >> I have a database with 4 tables. Table 1 holds the details of
    >> >> > contracts.
    >> >> >> >> Tables 2, 3 and 4 holds details of the parties to the contract.
    >> >> > Contracts
    >> >> >> >> can have 1 2 or 3 parties apart from ourselves. I have created
    >> >> >> >> separate
    >> >> >> >> tables for the parties to hold information like contact names
    >> > address
    >> >> >> >> etc.
    >> >> >> >> Is this the right formation for this database?
    >> >> >> >> Thanks
    >> >> >> >> Tony
    >> >> >> >>
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     

Share This Page