SUM Discrepancy

[scouser]

Hi to all once again. I thought I had resolved this issue but the addition of more data has found a floor in my design.

I have a small DB I intend to use for auditing my companies hardware / software.

frmComputerSoftwareAllocation: Allows me to allocate software / software licenses to computers.

Process: I select the computer I wish to allocate software to:
'computerID'. I then select the software 'cboSoftwareID' and then allocate
a license 'cboLicenseNum'.

As you know there can be multiple license keys for any one instance of software. So this leads me to my problem.

qryLicenseAllocated

Software ID of 32 = Microsoft Office Professional Edition 2003
1 * Volume License = 10
1 * OEM License = 1

Problem: As a test I alocated 1 volume license and the 1 OEM. When I ‘sum’ the number of licences from tblComputerSoftware instead of the query returning the desired result:

Microsoft Office Professional Edition : Licences Purchased 10 – Allocated 1
Microsoft Office Professional Edition : Licences Purchased 1 – Allocated 1

I get:
Microsoft Office Professional Edition : Licences Purchased 10 – Allocated 2
Microsoft Office Professional Edition : Licences Purchased 1 – Allocated 2

I can see what it is doing but after a long day getting this far can not see how to resolve it!!
Help very much appreciated.
Phil.

 

[gemma-the-husky]

you are getting a cartesian join i think. each license/platform is cross matching

you need to do a query just for volume license, another one for oem license and union them together, Make both queries have identical columns

then have a third query - you need to copy the sql from both the first two queries so it looks like

sql from query1

union

sql from query2

has to be done in sql.

good luck

 

[scouser]

Good Luck

Thanks for the reply.

Good Luck

I will need it!!!!!
Phil.

 

[gemma-the-husky]

its not hard, post your dbs - just the tables you are trying to join will be enough, and i'll send you a sample back

in its basic form, it's something like

select * from query1

union

select * from query2

as long as the columns match you get 1 big query.

 

[scouser]

Db

Hi Gemma. Many thanks for the offer of looking regards viewing the database.
I posted the Database in my initial post 'Forum.zip' (2003 format), let me know if you need it saved as a previous version. This contains the tables & queries (stripped down version of the main DB).
Phil.

 

[gemma-the-husky]

Thanks, I didn't notice the zipped file.

I am not exactly sure how you are trying to model everything now

However

TABLE
Software - I take your software table to be all the different programmes you have

LicenseDetails - to be blocks of licences you hold

therefore

ComputerSoftware - If its trying to identify licences allocated to individual computers, should be linked to the LicenseDetails as a FK, not to the initial Software table.

That is why you are getting this cross table join. I am not sure what effect changing this will have on the rest of your design, though.

 

[scouser]

Hi

Hi Gemma.
You follow my flawed design.........
tblComputerSoftware is as it sounds, to hold information regards what software is allocated to a particular computer. I would like to record both the software and related license key. It does this currently by recording the softwareID and license key. License key is currently included within tblComputerSoftware as you suggest, although I changed my design recently as not all the software I have has a license key. tblLicenseDetails PK is now LicenseID, LicenseKey is just a text field.
Thanks for your time and efforts.
Phil.

 

[scouser]

Getting Closer or Further Away?

OK, so here I go again after having a play. New set of anomalies!!
1)
frmComputerSoftwareAllocation. If you click in the SoftwareID field then the associated LicenseKey is displayed. When you click out it becomes invisible (mystery...wooo...woooo) OK joking aside it is down to little old me.
2)
qryLicenseAllocated. It now lists the Allocated license count OK, but alas doubles up on the data (see attached DB and all will become clear!!).
Any takers to the challenge, help very much appreciated.
Phil.

 

[scouser]

Still Trying!!

OK, so I am no further forward but I have created another query that does something different

qrySoftwareLicenses lists all software and the number of licenses purchased.
qrySoftwareLicenseAllocated counts all license keys that have an allocation
qrySoftwareLicenseAvailable does erm..........not a lot.............

I tried to create a union query an hour or two ago (it returned some data) but a mental block regards how to extract the required data dragged me back to my current state. Maths nor logic have ever been my strong suit!!

I am close to finishing the DB. When complete I intend posting the much improved DB to the 'Sample Database' forum so others might make use of it. I would really appreciate some help over the finish line.

I have posted yet another sample DB. Do not be fooled by the similarities in relation to previous versions, this has many more man hours in it!!!
Many Thanks,
Phil.