Sum & Amount problem

[_Nickel_]

I am making a hardware & software inventory database. I've got a many-to-many relationship between my PC table and my software table so I can keep track of which software is installed on which PC. I also have a one-to-many relationship between my software table and the license table (the license table counts how many licences I have and when they expire).
Now I need a query that compares how often a software is installed with how many licences I've got for that software. I made a query that uses the amount function to count how often the softwareID occurs in the link table between the software and the pc table. I also made a query that uses the sum function to count the total licences of each software. These both work on their own, but if I combine them into one query the total number of licences are for some reason multiplied by the number of PCs that software is installed on

Can anyone tell me how to fix this?

Thanks

 

[dcx693]

Sounds like you have the old Cartesian product problem. You've got a join relationship between the software and the license table, and a relationship between the software table and the PC table. But you still need some type of relationship between the PC table and the license table. Make sure you have that. The "connecting table" should be the software table.

 

[_Nickel_]

I am not reallly sure what you mean (or if my explanation above was any good). Below is a pic of my relationships (55kB) but it's in German. The tbl_Rechner_Allgemein is the PC table and the tbl_Software_Lizenzen is the License table.

 

[Pat Hartman]

There is no relationship between the installed instances of software and their license information in your structure. to create such a relationship, you would need to store the license number in the relation table that links software to PCs. Therefore, it does not make sense to create a query that includes both sets of data unless the sets are summarized. Create separate queries that count the number of installed instances and sum the number of licenses. Then join those queries to the software inventory table. Since each of the totals queries will return only a single row for each product, you won't have any problems calculating costs.

Just because two tables contain a common column, which in this case was SoftwareID, does not mean that it makes sense to join them. The two tables have a relationship with the software table but NOT with each other.