report selection hierarchy (1 Viewer)

[accessuser1023]

all,

I have some requirements whereby there are many reports that are needed to be given to the employer. I had an idea the other day and I wanted to run it by you guys. The reports can be categorized in a hierarchy (like everything else in this world of course):

=> Type
=> SubType
=> Name

Here are some examples of what I have currently:

Types => Finance, Customer, Services
SubTypes => Invoicing (Finance), Proposals (Finance), Customer List (Customer), Recent Customers (Customer), Service Types (Services)
Name => Print Invoice (Invoicing), Print Proposal (Proposals), etc, etc...

What I was thinking of doing is creating a few tables with relationships based on this, then one form from a query (that joins all the tables together) that serves as the report generating form. I would drive the cascading combo boxes by a bunch of hidden fields on the form itself.

Has anyone ever done this before? I think this would be much better than just writing a bunch of SQL statements behind combos on an unbound form. Wouldn't it?

Any thoughts from you guys on this? thanks.

 

[MarkK]

there are many reports that are needed to be given to the employer

Does this mean that you want to write a user interface to make it easier to select and run reports?

I would make one self-referencing table, with fields like . . .

tblReportTree
ReportTreeID (PK)
ParentID (FK, to PK in this table, self-referenced)
Name

 

[accessuser1023]

a self referencing table? can you clarify this for me a bit? I'm not sure I'm following...

 

[MarkK]

You can have three tables, like this . . .

tblType
TypeID (pk)
Name

tblSubType
SubTypeID (pk)
TypeID (fk)
Name

tblSubSubType
SubSubTypeID (pk)
SubTypeID (fk)
Name

. . . but it's not hard to see the repetition of structure. So you can make one table instead . . .

tblTree
NodeID (pk)
ParentNodeID (fk)
Name

. . . and for root nodes, enter a ParentNodeID = 0, so to only show root nodes write a query like . . .

SELECT NodeID, Name FROM tblTree WHERE ParentNodeID = 0

. . . so that's exactly the same as having the stand-alone tblType, from above. And then to only return nodes at the second level, those will be nodes whose parent is a root node . . .

SELECT NodeID, Name FROM tblTree WHERE ParentNodeID IN ( 
   SELECT NodeID 
   FROM tblTree 
   WHERE ParentNodeID = 0 )

. . . which uses a sub-query, but you can do that with an INNER JOIN too . . .

SELECT tChild.NodeID, tChild.Name 
FROM tblTree as tChild 
   INNER JOIN tblTree as tParent on tChild.ParentNodeID = tParent.NodeID 
WHERE tParent.ParentNodeID = 0

. . . but this way all your nodes are in one table, which simplifies things, and rather than link to rows in different tables, you can just link to rows in same table. Is that clearer?

 

[accessuser1023]

No Mark, actually it isn't clearer. It all looks like the same thing to me. Just semantical differences, at best.

I think the only issue here is whether or not one method over the other will result in easy entry for the user through the GUI. Is that not correct? That's what the ultimate goal is obviously. Users want everything done automatically. They can't get that yet obviously, but when Google makes the entire world free then we will finally be there and they'll have to find something else to complain about. At that point, what will it be? Why they don't want to be perfect anymore because there won't be anything left to do at that point?

hey thanks for your help too. This is incredibly useful info. I seriously mean that too.

 

[MarkK]

Let's say you want to use a treeview for the user interface. In that case the user would not be able to tell if you used one self-referencing table, or three different tables, to store that data. So to this . . .

I think the only issue here is whether or not one method over the other will result in easy entry for the user through the GUI. Is that not correct?

. . . I'd say no. The user interface and how the data is stored should not be closely related at all.
Cheers

 

[accessuser1023]

this is great news Mark! It doesn't seem like the semantical issues mean absolutely anything anymore. that's great news, because it makes life easier for us developers.

essentially what that means for me is that I can develop Access in a variety of ways and really not have to care about the design all that much because Access takes care of the possible issues that could arise in a multi-user environment by itself. outside of the obvious standards that is (write conflicts, record locks, etc...).

Does that sound about right to you? I would like to discuss the record locking issue in a shared environment with you a little bit more, but only if you have time. Do you Mr. Expert?