Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 31st, 2007, 08:19 AM
Authorized User
 
Join Date: Oct 2004
Location: Bristol, Nth. Somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sorting in a Form

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);
Reply With Quote
  #2 (permalink)  
Old August 13th, 2007, 08:38 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old August 13th, 2007, 11:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old August 17th, 2007, 03:32 PM
Authorized User
 
Join Date: Oct 2004
Location: Bristol, Nth. Somerset, United Kingdom.
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes it does help.

Thanks for your comment.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SORTING pallone XSLT 3 October 29th, 2006 08:45 AM
Sorting sunny76 Excel VBA 2 September 19th, 2005 09:31 PM
sorting kondapally Crystal Reports 2 January 21st, 2005 10:51 AM
Datagrid sorting by non alphabetical sorting? LLAndy VS.NET 2002/2003 1 July 15th, 2004 01:20 AM
sorting Greg Griffiths Javascript How-To 1 June 20th, 2003 01:10 PM



All times are GMT -4. The time now is 06:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.