VB and Table Structure

[NeoNemesis]

First let me outline my problem.

I am creating a database that will catalogue all of the computers at my office. It will also catalogue what software is installed IN them and what Software is installed ON them.

I am having no problems with the hardware side of things. The software is a different matter altogether!

I have a table called "PC Base Unit", "Software" and "LicenceTable".

As there are many software titles and many computers I have the link box "LicenceTable". The records in this table are dynamially generated using a script which adds as many rows per software title as there is in the "MaxLicenceNumber" field. This means that if we have 5 versions of MS Excel (with the ID MSE00) then the "LicenceID" field of the Licence table will have:

Licence ID | PC Asset Tag

MSE00-001 | GEN0101
MSE00-002 | ITS0100
MSE00-003 | other PC Id's
MSE00-004 |
MSE00-005 |

in it.

There is another field in this table which is used to link the LicenceTable to Software. That is the software ID.

Now, as i understand it in the Software field it should automatically put the PackageID value that the LicenceID value realates to, like this:

Licence ID | PC Asset Tag | PackageID

MSE00-001 | GEN0101 | MSE00
MSE00-002 | ITS0100 | MSE00
MSE00-003 | other PC Id's | MSE00
MSE00-004 |
MSE00-005 |

This then should allow me to run a query that will be able to determine the name and version (stored in the software table) of the software on each computer eg.

On GEN0100 - Microsoft Excel 2000.

This is not happening! When I open the LicenceTable I have the generated (using INSERT INTO) LicenceID and whichever PC ID numbers I have entered into the PCAssetTag field. But the PackageID field is empty.

I cannot work out what the problem is... can any of you fine people help??

Regards

Joe

 

[LittleSmudge]

First off - what do you mean by differentiating between Software IN the machine and Software ON the machine?

Secondly, see the FAQ700-2190 about avoiding space characters in any object name

Thirdly, If packageId is just the first few characters of the [Licence ID] field then there is no reason to store it separately ( and some very good reasons why NOT to store it separately).

Fourthly, How do you imagine that the PackageId field is being populated ?
You state
"Now, as i understand it in the Software field it should automatically put.. .. "
so some mechanism exists - but how ? What is it ?
Where would the data come from ?




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[JeremyNYC]

LittleSmudge,

In thread705-414876 I advised Joe to normalize his data, and pointed him to that Litwin article on my site that I'm sure you've seen me reference enough times to make you ill. I'm confused as to why the data are still so denormalized.

========

Joe,

Did you read the article? If so, I'd say read it again, or ask questions about it here. It really will help.

Also, in general, when you post with a question, it's best to include relevant code and SQL statements, so people can see what you're doing, as well as what you want to do, and what's happening (which you've captured in your write-up).


It sounds like your INSERT INTO statement doesn't contain a value in that last field of yours. Are you expecting it to get picked up because of a main form/sub form setup? That will only happen with data inserted into the form itself. SQL does the append to the table, not the form.

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.