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)

relationships
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?

thanks,
alex

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...)