First of all I should say I don't have a clue what I'm doing.

I'm trying to build a database which is for Software Inventory. What I have is a table that contains software. Something like:

Id Software

1 Visual Studio .Net
2 Word
3 Microsoft Office
4 Excel
5 Vb.Net
6 C#


Also there is a table which defines relationships between Software:


Id Parent Child
1 3 2
2 1 5
3 3 4
4 1 6


So this says that Word and Excel are dependent, or children, of Office and that VB.Net and C# are dependent on Visual Studio.

I have a form which is designed to allow users to manage these relationships. It consists of two list boxes which list all the software in two columns, one for the parent and one for the child. The list boxes are displayed in alphabetical order. As you move from record to record the relationships are displayed within the list boxes.

Microsoft Office Excel
Microsoft Office Word
Visual Studio .Net C#
Visual Studio .Net VB.Net

You can then delete a relationship, add a new relationship or change a relationship.

The relationship records come out in the order of the Relationship Parent Id not the order of the Software Parent Name which is what I want to happen. Any ideas how to get this to happen?

The form uses as its Source the Relationship table, I did change this to a SQL quer which seemed to work but then prevented me from updating the Relationship table. The Parent and Child list boxes then list the software name using a piece of SQL as follows:

SELECT tblSoftware.SoftwareID, tblSoftware.Software+IIf(IsNull(tblSoftware.Versio n)," "," - "+tblSoftware.Version) AS Expr1 FROM tblSoftware ORDER BY tblSoftware.Software+" "+IIf(IsNull(tblSoftware.Version)," ","- "+tblSoftware.Version);

I would suggest one of two standard ways to go with this.

You could create a table that maintains a list of Software Suites, and have Office and VS in there, and then a list of software titles, and relate them to their suites, where applicable... perhaps have an entry in the Suites table for "No Suite"

OR

Use a reflexive relationship, which is somewhat problematic in Access, but totally doable. That would involve creating a table with this sort of structure.

tblSoftware
SoftwareID - PK
SoftwareTitle - text
Suite - Yes/No - check yes if the title is a Suite.
Suite - FK to SoftwareID in the same table limited by those records where Suite = Yes.

This second reflexive solution is pretty good, and well used in SQL Server, for example. You will have to manually create the look up with Access, since it won't support this kind of relationship with the Wizard.

Did that help?

mmcdonal

Sorry, that tructure should have been:

tblSoftware
SoftwareID - PK
SoftwareTitle - text
Suite - Yes/No - check yes if the title is a Suite.
SuiteID - FK to SoftwareID in the same table limited by those records where Suite = Yes.

Can't have duplicate names as in the original post.

mmcdonal

Yes it does help.

Thanks for your comment.