Two list into one!

[Herriot]

Hi folks I wonder if anyone can help.

I work in a college and am trying to ensure that we are trading legally with regards to software licensing. I need to find out what software we have installed and what we actually have licences for. To that end I have compiled two lists. One list contains a note of all the software currently on the machines in the following format …

Software Name Qty
Office 97 20
Windows 95 15
Etc
(It’s much more detailed than this as I have fields for location & which department owns the machine etc. But this is the summary I need to extract.)

I got this list by physically visiting each PC in the college reformatting the PC and then installing only that software needed for that PC to run. A note of what software was installed was then taken. This is a list I have called SW_Installed.

The second list is one with a similar format to the above (software Name & Qty) but this time it is a list of software we physically have licences for. In other words we have the (hard copy) printed licence. I have called this Actual_Licences_held.

In an ideal world both these lists will be equal and after this project it should be but for the moment it isn’t. So, It is possible for both lists to have software that is not in the other list. For example we may have a licence to use Photoshop but it may not be physically installed on any of the machines. Conversely we may have software installed that we don’t have a licence for.

What I am aiming for is one list that would hold ALL the software noted on both lists with a quantity of both actual against the licences we hold. This would show a report that tells us how many licences we need to buy to keep us legal. Or how many licences we have extra.

Example:

Col1 Col2 Col3 Col4
Office 97 20 15 +5
Works 15 20 -5

Col1 = Software Name
Col2 = QtySW_Installed
Col3 = QtyActual_Licences_Held
Col4 = Discrepancy

Now I am not looking for a full solution (although I wont turn one away ) but if I could get some pointers I would be grateful. Although I have posted this in the Access Forum as that is the program i will use I also have access to Crystal Reports.
TIA

 

[Dimples]

Hi,

You're on the right track, what you need to do is specify the "relationship" between your to tables (1. "Installed" is one table) and (2. "Licenses" is your second table).

Let me back up for a minute. Where is the information stored, currently? Because you reference "columns", it leads me to believe that it is in Word or Excel?

If your data is not currently in Access, Start here:

In Access:
1. Create a new table with the following fields:
a. Software Product Name "text"
b. Software Licence "yes/no"
c. Software Licence # "text" (as may be alphanumeric)
d. Software License Qty. "numeric" set format to single digit (0)
e. Software License Location "text"
f. Comments "memo"
2. Save your table without a primary key (for now), name it something like Programs and Licenses List.
3. Create a new form generated from the "Programs and Licenses List" table you have just made; use the wizard, try justification for formatting, save as, "Add New Programs and Licenses Form"
4. Open your new Form and enter/re-enter all your collected data as well as data from suggested fields (additional pertinent data, never hurts, unless your under direct data specifications).
5. Create a new report generated from the Programs and Lists table (which now holds all your data that you have entered via your form). Use the wizard, and name your report, "Products and Licenses Report."

These steps should assist you in achieving your goal, also give you additional data references regarding your project, and allow for editing, retrieving, expanding, reviewing, and displaying.

When you get that down, you can also use Queries to create reports that reflect targeted data.

Good luck!
Angelica Alcala

http://www.angelicnames.com

Please sign our guest book and let me know if this helps or if you need additional help.

Have a wonderful holiday.

P.S. If this does not help, or I misunderstood your original question, feel free to email me at: webmaster@angelicnames.com and I we can go over it again, until you have the information you need.