Query to Return Computers without Allocation

[scouser]

I have 2 tables tblComputers & tblComputerAllocations
tblcomputers has 85 records & tblComputerAllocations has 71.

I would like a query that returns the 14 computers that have no allocations.

I tried a union query on field ComputerID but i must misunderstand its use!!
Thanks in advance,
Phil.

 

[KeithG]

Select tblComputers.*
From tblComputers LEFT JOIN tblComputersAllocation ON tblComputers.ComputerID=tblComputerAllocations.ComputerID
Where tblComputerAllocations.ComputerID is null

The above SQL should do the trick. Please post back with the results

 

[scouser]

Thanks

Keith that worked a treat many thanks.

If you fancy a challenge I have been scratching my head over this one for months:

I have 3 tables. tblSoftware tblLicenseDetails tblSoftwareAllocation

tblSoftware lists all software. tblLicenseDetails lists licenses details for the software. tblSoftwareAllocation lists the softwareId & licence key + number of licenses allocated to a computer.

I can produce a query thats shows all allocated software / licenses. What I am unable to do is create a query that displays ALL software with related license details /availability:

i.e.
tblSoftware
SoftwareID:1
Vendor: Microsoft
Description: Office 2007

tblLicenseDetails
License Key: XYZ
License Purchased: 50
SoftwareID:1


I have a query that calculates the number of licences available by subtracting allocated against purchased.

I would like the query that will return:
Full lsit of ALL software even if it has zero allocations.

Query Output:
MS Office License Purchased 50 License Allocated 0 License Available 50
MS XP Pro License Purchased 50 License Allocated 25 License Available 25
etc.....

Any thoughts on that one?
Regards,
Phil.

 

[spasticus]

hey scouser.

i think this will work:

Make a a query from the three tables with whatever fields you want.

(you want to calculate license available right?)

in a blank column put License Available: [License Purchased] - [License Allocated] in the field cell

 

[scouser]

Thanks

I think I have tried that one.
I do not want to display license available as such, more an overview of all software and the current license status.
I will give it a whirl and post back.
Thanks,
Phil.

 

[KeithG]

I think you need two queries

1st Name: qryTotalAllocated

Select SoftwareID,Sum([Number of licenses]) as Total_Allocated
From tblSoftwareAllocation
Group by SoftwareID


2nd

Select Description, [License Purchased], Total_Allocated,IIF(Total_Allocated is null,[Licsense Purchase],([License Purchase]-Total_Allocated])) as License_Available
From ((tblSoftware INNER JOIN tblLicenseDetails ON tblSoftware.SoftwareID=tblLisense.SofwareID) LEFT JOIN qryTotalAllocated ON tblSoftware.SoftwareID=qryTotalAllocated.SoftwareID)


If this does not work can you post an example DB? I did the above free hand because I don't have a copy of Access availbe on this PC.

 

[scouser]

Thanks

Keith I will give that a whirl now and post back.
Phil.

 

[scouser]

Syntax Error

Query 1 ran OK. Query 2 returns a syntax error on the IIF?

I have substituted the correct field names:

Select Description, [NumOfLicensePurch], Total_Allocated,IIF(Total_Allocated is null,[NumOfLicensePurch],([NumOfLicensePurch]-Total_Allocated])) as License_Available
From ((tblSoftware INNER JOIN tblLicenseDetails ON tblSoftware.SoftwareId=tblLicenseDetails.SofwareId) LEFT JOIN qryTotaLicenselAllocated ON tblSoftware.SoftwareId=qryTotaLicenselAllocated.SoftwareId);

Any ideas?
Thanks,
Phil.

 

[Rich]

Iif(Is Null([ some field]), etc

 

[scouser]

Sample DB

Thanks I will try the new syntax.

I have attached a sample DB (stripped down).

I have created a number of licenses previously trying to nail the software / license display issue. In addition if you note on form 'Software License Allocation'. When you allocate the license the details in the combo (License Purchased / Available) does not refresh unless you click to a new line and then back again. I tried adding an event to the afterUpdate but this did not work. I digress.........
Thanks,
Phil.

 

[scouser]

Syntax

I have got my knickers in a twist. Tried a number of variations. My latest failure is posted below.

Can somebody spot the syntax flaw:

Select Description, [NumOfLicensePurch], Total_Allocated,Iif(Is Null([Total_Allocated]),([NumOfLicensePurch]),([NumOfLicensePurch]-[Total_Allocated])) as License_Available
From ((tblSoftware INNER JOIN tblLicenseDetails ON tblSoftware.SoftwareId=tblLicenseDetails.SofwareId ) LEFT JOIN qryTotaLicenselAllocated ON tblSoftware.SoftwareId=qryTotaLicenselAllocated.SoftwareId);

Thanks,
Phil.

 

[scouser]

Not Quite

I have this that nearly works:

SELECT tblSoftware.Vendor, tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, tblSoftwareAllocation.NumberOfLicences, [tblLicenseDetails].[NumOfLicensePurch]-[tblSoftwareAllocation].[NumberOfLicences] AS [License Available]
FROM (tblSoftware INNER JOIN tblLicenseDetails ON tblSoftware.SoftwareId = tblLicenseDetails.SoftwareID) LEFT JOIN tblSoftwareAllocation ON tblSoftware.SoftwareId = tblSoftwareAllocation.SoftwareId;

This returns:
License Purchased............License Allocated............License Available
..........1..................................Blank.........................Blank...........
.........50....................................1..............................49............

I would like line one to read:
..........1..................................0.........................1...........

Something Like:
If license allocated is null, license allocated equals license purchased, license available equals license purchased???

Hope that all makes sense!
Many Thanks,
Phil.

 

[scouser]

Progress?

I tried:

License Available: IIf(IsNull([NumberOfLicenceAllocated]),[NumOfLicensePurch])

If the number of licences allocated is null then License Available mirrors the number of licences purchased. (good)

However:
Where licenses HAVE been allocated the field is blank

AND
Number of licences allocated is blank where no licenses have been allocated, I would like this to be 0........
Phil.

 

[Rich]

There is a part missing from the Iif statement posted
IIf(IsNull([NumberOfLicenceAllocated]),[NumOfLicensePurch],[NumberOfLicenceAllocated])

 

[KeithG]

try this

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

 

[scouser]

Great Effort

Keith many thanks for your efforts. I have ended up with this:

SELECT tblSoftware.Description, tblLicenseDetails.LicenseKey, tblLicenseDetails.NumOfLicensePurch, qryALLOCATIONS_LICENSE.SumOfNumberOfLicences 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, qryALLOCATIONS_LICENSE.SumOfNumberOfLicences;

This gives me what I want regards displaying the number of licences purchased and the number of licences available!! GREAT. However License Allocation displays the quantity where there is an allocation. Where there is no allocation the field is blank. It would be great if this read 0.

Many many thanks,
Phil.

 

[KeithG]

You need to use the Nz function to display a zero for the null value.

Nz([FieldName],0) should do the trick

 

[scouser]

Cheers

Thanks, I will give that a try.
Phil

 

[scouser]

Error

Tried:

License Allocation: nz([SumOfNumberOfLicences],0)

Error:.............specified expression Nz([SumOfNumberOfLicences]) as part of an aggregate function...............

Any ideas?
thanks,
Phil.

 

[KeithG]

Change the toals dropdown from GROUP By to EXPESSION in your query.