Criteria question

[_Nickel_]

I am working on a software and hardware inventory database for my company. I have all the software stored in one table that basically looks like this
SoftwareID AutoNumber
SoftwareName Text
OperatingSystem? Boolean

I need a query that only returns the names of all Operating systems as well as one that gives me the names of all other software.
Can anyone tell me what criteria I have to use for this?

Thx

 

[Mile-O]

You need two tables.

A table for Operating Systems and a table for Software.

tblSoftware
SoftwareID
SoftwareTitle
OperatingSystemID

tblOperatingSystems
OperatingSystemID
OperatingSystem


You can then relate these two tables on the OperatingSystemID


If software can run on more than operating system then you've identified a many-to-many and need a third table

tblOperatingSystemsToSoftware
OperatingSystemID
SoftwareID

both of these being foreign keys to the primary key of both tables mentioned already.

You then make these two fields the primary key for this third table.

 

[_Nickel_]

Thanks for the quick reply
I thought of that as well but the problem is that the software table is in turn linked to the software license table which makes it a bit complicated to have separate operating systems and software tables.

I need this query for a dropdownbox that should only contain operating systems so I think it's easier not to use two seperate tables.

 

[Mile-O]

I thought of that as well but the problem is that the software table is in turn linked to the software license table which makes it a bit complicated to have separate operating systems and software tables.

Not really. They are separate entities ergo they should have separate tables.

Originally posted by _Nickel_
I need this query for a dropdownbox that should only contain operating systems so I think it's easier not to use two seperate tables.

SELECT DISTINCT OperatingSystem
FROM MyTable
ORDER BY OperatingSystem;

Also, having a question mark in a field name is a bad naming convention. Special characters, spaces, and reserved words should be avoided.

 

[_Nickel_]

I don't think they are seperate entities. Operating systems just like any other software is installed on computers and needs a license to be used. Whether a software is an OS or not is just one its properties.
If I wanted relationships between software and operating systems then I would have to consider them as seperate entities but the database basically just records a PC's hardware, the software that's installed on it and the licenses we've got for that sofware.

Also, I only put the question mark in the post to make it clearer that the field was asking whether the software is an OS, it's not in the real field name.

 

[Mile-O]

Misunderstanding; that's all.

Your query should be:

SELECT DISTINCT SoftwareName
FROM MyTable
WHERE OperatingSystem = True
ORDER BY SoftwareName;

Changing the True to False for the Software that's not an OS

 

[_Nickel_]

My initial explanation of the problem probably wasn't all that good.

I am not really sure how I am supposed to implement your answer (I am still pretty much an Access newbie).
If I put the "WHERE OperatingSystem = True" in the criteria field of the "OperatingSystem" column and ten run the query it still lists all the software and when I return to the design the "Where OperatingSystem" is in a new field instead and the criteria of the column is "True".

Thanks for the help!

 

[neileg]

You're using the query grid and Mile is giving you SQL. Using the query grid, just put True in the criterion box.

 

[_Nickel_]

Thanks!
I should have been able to figure that out myself...