Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Not sure about my db design ??

    Hi all,

    I'm a "recreational" access user. The db i'm trying to setup should hold the following information:

    1. Years
    2. Clients (a client can have many contracts)
    3. Contracts (a contract can have only one client. a contract can have many suppliers)
    4. Suppliers (a supplier can have many contract)

    With the above in mind i made the following tables:

    1. tbl Year (year ID, year)
    2. tbl Clients (client ID, year ID, Client Name)
    3. tbl Contracts (contract ID, Client ID, contract name, fee,
    4. tbl Joint (Joint ID, Contract ID, Supplier ID)
    5. tbl Supplier (Supplier ID, Supplier Name)

    1 to 2 (1 to many, "year ID" to "year ID")
    2 to 3 (1 to many, "client ID" to "client ID")
    3 to 4 (1 to many, "contract ID" to "contract ID")
    5 to 4 (1 to many, "Supplier ID" to "Supplier ID")

    Now, if I group by "Year", group by "Contracts" and Sum by "Fee", I get more fees than I should.

    What is happening is that as "tbl Joint" can contain more than 1 record for each contract, the fee is multiplied for these records and therefore the total fee is more than it should.

    The question is whether the database design is correct or not and therefore should i change the design, OR if i should change the query to select distinct in which case the result can be obtained.

    What do you think?


  2. #2
    Join Date
    Feb 2012
    Is that the LEFT JOIN or RIGHT JOIN problem?

    You may quot your SQL string to let others know what's happening.

    (I'm a beginner too...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts