Help please I'm getting frustrated

[iain1978]

Hi All,

I was wondering if a kind sole out there can help me or give me some advice.

I'm creating a DB (I have attached a simple copy) but I'm having real problems with it. On the Roleprofileform I want to give the users the ability to choose what applications they need. I've done this by double clicking the text box will produce a list box from which they choose their apps. On closure this will then update the text box with the respective ID's. This solution was supplied through help received on this forum.

The trouble is I still don't under stand how I can ever run a report with this information without have mountains of code.

What I would like to happen and I'm all ears to any suggestions is to somehow have the ability for users to choose say their drives and it be reflected in the table tblRoleApps as I can then run reports easily. My main aim of engaging you help is to help me with
A. How to allow users to choose their apps
B. How this is displayed on the form (would prefer the names of the apps but it is not that important if the reports reflect this)
C. How to reflect what apps match up to what roles for the purpose of reporting.

Any help or advise is appreciated.

Regards

 

[The_Doc_Man]

My site's networking rules prohibit downloads, so I cannot look at your example. Therefore I have to shoot from the hip.

Some years ago, Nicklaus Wirth (father of the Pascal language) was quoted as saying that 80+% of all programming problems stemmed from data design issues. So my odds are 4 in 5 of being right by suggesting that you might have a data layout issue.

If you are trying to organize reports based on varying numbers of applications for each user, perhaps you need to normalize your DB so that you have a one (user) to many (apps) relationship. Then you could write a query based on the user table, the user-apps table, and let the wizard put the report breaks for you. You might need another table, though, to record details of each application. Therefore, ...

In this forum, you can Search for the topic "many to many" (some entries with and some without hypens, look up both possibilities). That will tell you how to manage a table of users, a table of applications, and the overlap table that stands between them to identify their intersections.

If you are not familiar with normalization, do a Google search on "database normalization" and pick some decent sources. You'll catch about a gazillion hits on that topic, conservatively speaking, so only pick the articles supplied from either database companies or universities whose names you know.

If you can build the overlap table that shows which users need which applications, you have the problem mostly licked. The Forum Search will give you plenty to read on that topic. Then, your future designs should take normalization into account. You really want your DB to at least be in 3rd normal form. If it is even applicable, 4th normal would be nice - but not everyone needs that level of normalization. Let's not talk about 5th-normal or the special-case normalization.

Don't be afraid to step backwards a bit for a redesign. It happens to all of us. Sort of like an initiation ceremony, a "rite of passage" for all true DB designers.