summing and count query help

[pgsch]

I am creating a simple software compliance database. It marries up purchased software with installed software. The names used in the purchase order table do not match that of the installed table. I have created a link(license) table that creates a link between both installed and purchase tables: eg.



Purchases.item_description links to License.productname
Installed.product_name links to license.productname

License table has anothe field call display_name.

EG:
License tables has records like:
productname="MS Office", displayname="Microsoft Office" (From purchase orders)
productname="Office Pro Plus", displayname="Microsoft Office" (From Installed)
productname="Microsoft Office", displayname="Microsoft Office" (From purchase orders)

From the above you see that there 2 purchases of Office and one installation and the product will be known as "Microsoft Office"

I have a quesry that joins installed to license where installed.productname=license.productname and also joins purchases to license where purchases.item_description=license.productname

I am trying to get an sql query that with count how many installs.productname and purchases.item_description there are for each license.displayname

The purpose is to get some form of compliance list

displayname count(installed) sum(purchases.qty)

I hope that makes sense.

Anyone have any ideas?

[Rod]

Well, you've got me confused. I read your post a number of times and assume that the reason for the rather inefficient lininking of tables on text attributes is that you cannot change the structure of the purchase orders and Installed tables. Correct?

Even if correct, does either of those tables include a unique primary key?

Anyway let's proceed with the situation as you describe it. I assume that License!displayname is the 'official' software name as far as licenses are concerned. I also assume that License!productname contains the various terms purchasers and installers use for the software, which may or may not be the same as the 'official' license name. If I'm right then License!productname is unique whereas License!displayname will probably contain duplicates.

What you have is not really a linking table (a table that resolves a many:many relationship) but a 'lookup' table for converting user names into a tandard 'official' name. You then want, for every 'official' name, to count the number of purchases (this is a sum) and the number of installations (this is a count).

OK, let me know if I have understood correctly while I put my thinking cap on. Tell me what the name of the attribute is on Purchases that contains the number of copies bought.

[orange]

I agree with Rod - a confusing set up at best.

I would be looking at some sort of reconciliation table where all the various spellings of MS Office, Microsoft Office etc(which represent the very same product) are referenced by the same unique number. As suggested I would use the unique number for PK and FK.

Using text for keys where variations in spelling, and typos can "ruin" any linkage is a non starter , in my view.

[pgsch]

Thanks for replies. I am all ears for better ways to link than the text fields. Rod you are correct it is more like reconciliation table. In fact that is the label I have on the form - "Reconcile to license record".

I am not exactly sure how I can use any other method linking both installations and purchases to the license record other than name. I dont want to have to reconcile every instance of the same purchase item to a license record, just the first instance, so every other instance of that product in the purchase database is linked to the appropriate license record.

I am not sure I am making myself clear. But in your reply your understanding is correct.

There is a single record in the installation table for each install of a product and there is a qty field in the purchases table that represents the number of licenses owned.

The qty field is numeric.

I have made a work around by creating other queries, one that count the installation and the other sums the qty fields. I have then used these queries in a third to produce a form that display a compliance summary. I am not sure it is the best way.. From your reposnes I guess not.

Appreciate your advice.

thanks

Paul

[Rod]

I am not exactly sure how I can use any other method linking both installations and purchases to the license record other than name. I dont want to have to reconcile every instance of the same purchase item to a license record, just the first instance, so every other instance of that product in the purchase database is linked to the appropriate license record.

I appreciate that if the source tables are large then it is somewhat tedious to manually assign each and every record to the License table. However be aware that using the relationships you propose is prone to omissions and ambiguities. If this is something you plan to run frequently - say daily - then I would suggest a proper analysis of the requirement and solution is undertaken. Meanwhile it is possible to devise queries that would identify any source records that do not have corresponding license records; running such queries and correcting any omissions is, to my mind, a necessary first step.

I believe it was Hilaire Belloc who said, "Tell them what you're going to tell them, tell them and then tell them what you've told them." Here goes with the first of his recommendations.

I doubt whether there is a solution to your requirement that uses a structure whereby all three tables are linked together simultaneously. Instead I envisage one query that counts the installations and another that sums the purchases. These two queries are concatenated in a union query. Finally the result you want is obtained from the concatenated query. It may be possible to nest all these separate steps in one enormous mass; otherwise you may have to suffer intermediate result sets. For development I would certainly recommend using intermediate result sets as debugging complex nested queries is a nightmare. I will now go and construct a simple test database according to your description (I have the time). When I have a working solution, I shall post again.

However I still need the name of that attribute on the Purchases table that contains the count of software copies bought.

[Rod]

Much simpler than I feared! Here is the three-query solution.

qryInstalledCount

SELECT License.displayname, Count(License.displayname) AS Installed, CLng(0) AS Purchased
FROM Installed INNER JOIN License ON Installed.product_name = License.productname
GROUP BY License.displayname, CLng(0);

qryPurchasedSum

SELECT License.displayname, CLng(0) AS Installed, Sum(Purchases.quantity) AS Purchased
FROM Purchases INNER JOIN License ON Purchases.item_description=License.productname
GROUP BY License.displayname, CLng(0);

qryResult

SELECT qryInstalledCount.displayname, Sum(qryInstalledCount.Installed) AS SumOfInstalled, Sum(qryPurchasedSum.Purchased) AS SumOfPurchased
FROM qryInstalledCount INNER JOIN qryPurchasedSum ON qryInstalledCount.displayname = qryPurchasedSum.displayname
GROUP BY qryInstalledCount.displayname;


Note: I have assumed the purchase order attribute is named 'quantity.'

Do you want me to put it all in one?

[pgsch]

I can appreciate what you are saying about how I am linking the tables via the displayname. The three query solution you have shown is how I have implemented the result. If it is possible to look at a one query solution that would be great if you think there is a significant advantage.

I will look at implementing your recommendation about reconciling each individual purchase and installed record to a license record.

Thanks for your help.

Regards

Paul

[Rod]

Hi Paul,

In this case there is no significant advantage in terms of processing by having one integrated query; the three queries are how SQL would do the processing. The only downside is that with three queries registered in your project you are vulnerable to someone not understanding how they are used and altering (or even deleting) one of the intermediate queries. If I have time today I shall attempt the integrated SQL.

[Rod]

Here's an all-in-one solution:

SELECT A.col1 AS Name, Sum(A.col2) AS Installed, Sum(A.col3) AS Purchased
FROM (SELECT License.displayname AS col1, Count(License.displayname) AS col2, CLng(0) AS col3
FROM Installed INNER JOIN License ON Installed.product_name = License.productname
GROUP BY License.displayname, CLng(0)
UNION
SELECT License.displayname AS col1, CLng(0) AS col2, Sum(Purchases.quantity) AS col3
FROM Purchases INNER JOIN License ON Purchases.item_description = License.productname
GROUP BY License.displayname, CLng(0)
)  AS A
GROUP BY A.col1;

Note the structure. There is an outer query acting on a result set called A and containing three columns: col1, col2 and col3.

SELECT A.col1 AS Name, Sum(A.col2) AS Installed, Sum(A.col3) AS Purchased FROM *** GROUP BY A.col1;

The inner query (*** in the above) is the union of the two intermediate queries in my previous post with the aliases suitably altered and with the result set named A.

OK, to complete Hilaire Belloc's advice: I have given you a three query solution that uses a linking of two intermediate queries; and an integrated solution that uses an inner union query. Actually the integrated solution could also use a linking but I thought the union was clearer.