Simple Query

[scouser]

Hi guys. I have been away a while and boy am I rusty.

I would like to show how many available licenses there are. Here is what I have:
Software (Office 2003)
license purchased (8)
license used (2)

I added an expression along the lines of:
Available: Sum([tblSoftware]![Number of licences purchased]-[tblPC-LicenceRelation]![Number of licences])

This gave 14 and not the desired result of 6!!!
Simple I am sure!!
Phil.

 

[statsman]

Try:

Available: Sum([tblSoftware]![Number of licences purchased])-([tblPC-LicenceRelation]![Number of licences])

Separate each data source into its own function.

 

[scouser]

thanks

Thanks for the reply. That gave me an error?
See attached screen dump. Must be me!!
Thanks,
Phil.

 

[scouser]

Resolved

I managed to resolve this once I applied my tiny brain!! I created a query to sum the licenses allocated. I then used this as part of an expression so ended up with:

LicenceAvailable:
[tblSoftware.LicencesPurchased]-[qryLicenseUsed.SumOfNumberOfLicences]

Probably a better way, but seems to give the desired result!!
Phil.

 

[neileg]

One of my pet hates is the use of Sum() where it is completely redundant. Pleased you got rid of it!