Query Design.DB Design question (1 Viewer)

[rsmonkey]

Hey,

I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.

I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.

In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:

tblLicenseInformation
License_ID
LicenseDescription
NumOfLicensesPurch
SoftwareOverview_ID

tblHardwareSoftwareLicense
HSL_ID
Hardware_ID
Vender_ID
Software_ID
AppEdition_ID
AppVersion_ID
SoftwareOverview_ID
License_ID

However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.

I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!

cheers

 

[neileg]

I would do one of two things depending on where you want to use this data.

In a form you could use DCount() to count the number of licences in use in tblHardwareSoftwareLicense. You could use DLookup() to look up the licences purchased in tblLicenseInformation and subtract one from the other. This works if you want one SoftwareOverview_ID at a time.

Or create an aggregate query to count all of the licences used in tblHardwareSoftwareLicense for all SoftwareOverview_IDs. Join this query in another query with the data in tblLicenseInformation and use a calculated field to calculate the balance.

 

[rsmonkey]

thx for the quick reply neileg,

I created an aggregate query and (CountOfLicense_ID) within the tblHardwareSoftwareLicense and then subtracted this from the NumOfLicensesPurch
in the tblLicense_Information however when it came to me inserting this data into either of the tables it asks for the License_Id even though it is defined..

Here is the code the query builder is using when it runs a Enter Parameter Value box comes up:

INSERT INTO tblHardwareSoftware ( Available )
SELECT [NumOfLicensePurch]-[qrySoftwareLicenseAllocated.CountOfLicense_ID] AS Available
FROM qrySoftwareLicenseAllocated INNER JOIN tblLicense_Information ON qrySoftwareLicenseAllocated.License_ID = tblLicense_Information.License_ID
WHERE (((qrySoftwareLicenseAllocated.License_ID)=[tblHardwareSoftware]![License_ID]));

I also tried placing the code within VB but i get the error: 'zero parameters to insert into table/query' or something along those lines! And yes there is information in their to append/update!

cheers,

 

[neileg]

But you shouldn't be saving this value, anyway. Any changes in the underlying data will invalidate the stored balance. You therefore calculate the balance any time you need it.