Query to Return Computers without Allocation

[scouser]

Expression

Keith I should have stated in my post that I had already changed totals to expression. Still received error?
Thanks,
Phil.

 

[KeithG]

can you post your SQL?

 

[scouser]

Here you go

Here you go Keith:

SELECT tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, Nz([SumOfNumberOfLicences],0) AS [License Allocation], nz(([NumOfLicensePurch]-[SumOfNumberOfLicences]),[NumofLicensePurch]) AS [License Available]
FROM (tblSoftware LEFT JOIN qryALLOCATIONS_LICENSE ON tblSoftware.SoftwareId = qryALLOCATIONS_LICENSE.SoftwareId) INNER JOIN tblLicenseDetails ON tblSoftware.SoftwareId = tblLicenseDetails.SoftwareID
GROUP BY tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch;

Thanks,
Phil.

 

[scouser]

Resolved

I have managed to resolve this issue with a lot of help so many thanks. I created an additional query.
The SQL that worked as follows:

SELECT tblSoftware.Vendor, qry_LICENSE_AVAILABILITY.Description, qry_LICENSE_AVAILABILITY.LicenseKey, qry_LICENSE_AVAILABILITY.NumOfLicensePurch, nz([License Allocation],0) AS License, qry_LICENSE_AVAILABILITY.[License Available]
FROM tblSoftware INNER JOIN qry_LICENSE_AVAILABILITY ON tblSoftware.Description = qry_LICENSE_AVAILABILITY.Description;

Regards,
Phil.

 

[scouser]

Spoke to soon.......

There is a design floor. Unresolved. I will post back if and when I manage to fix it!!!
Phil.

 

[scouser]

Two Days Later

OK, after nearly throwing in the towel I edge ever closer to completion.
I have created 2 new queries:
1) qryLICENSE_ALLOCATION_STATUS (96 records)
2) qryLICENSE_AVAILABILITY_STATUS (96 records)

1) Displays list of Software / License 'Allocations' displaying a zero value if the license has no allocations.
2) Displays list of Software / License 'Availability' based on allocations.

I have attempted to put this all into one query but the values returned are incorrect.

tblSoftware holds 73 records
tblLicenseDetails holds 96 records (some software has more than one key)

If I try to display LICENSE_STATUS by combining qryLICENSE_ALLOCATION_STATUS & qryLICENSE_AVAILABILITY_STATUS it returns 188 records?

Any ideas? I have attached a sample DB (2000 format) as a picture paints a thousand words and all that.........
Thanks,
Phil.