Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25

    Unanswered: Reporting from a Poorly Structured Database

    I'm just a student, but as near as I can tell this database I'm currently working on is pretty far from being properly normalized.

    It's a SQL Server and is used with another peice of somewhat commercial software which is also who I'm assuming designed the database. The user talked with the vendor about trying to get slightly more advanced reports.

    The gist of what the vendor told the user was that it was not possible. I thought it would be fairly trivial to do in Access until I got a look at the structure.
    Code:
    --------------------
    | dbo_ViewUser     |
    --------------------
    |userNumber(PK)    |
    |userInfoDropDown1 |
    |...               |
    |userInfoDropDown11|
    --------------------
    
    -------------------------
    | dbo_ViewDropDownFields|
    -------------------------
    |DropDownID(PK)         |
    |Index_                 |
    |NameIndex              |
    -------------------------
    The NameIndex field contains the name of the company that the user might work for. Index_ is a number from 0 to 9 which classifies the different types of companies together, IE delivery, contractor, anything, this also appears to be the only field in all the tables which can be used to relate a user to a company.

    For example on the ViewUser table there might be a user who has a userInfoDropDown5 value of 64.

    Looking on the ViewDropDown Fields for a dropDownID of 64 we see it has a index_ of 4 (Because the index starts at 0 I guess it's userInfoDropDownN+1)

    I just for the life of me don't see how I can relate these two fields, I have some experience with Oracle building fairly complex queries, but that was awhile ago.

    I've tried my best to explain the layout but if you're read through all of this and have questions please let me know.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    May I suggest that you start looking at the data ... Question: Does the userInfoDropDownXX have Index_ values in it? It may have the DropDownID in it ... You tell us.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Quote Originally Posted by M Owen
    May I suggest that you start looking at the data ... Question: Does the userInfoDropDownXX have Index_ values in it? It may have the DropDownID in it ... You tell us.
    userInfoDropDownXX does have DropDownID stored... if they do work for a company that fits within that group.

    The XX part of userInfoDropDownXX is the _index value

    The problem I'm having is only showing the userInfoDropDownXX that have a value in them. I've tried not blank, not null > 0 for the fields and using OR to join the statements.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So ISNULL(field, 0) <> 0 doesn't work?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Quote Originally Posted by Teddy
    So ISNULL(field, 0) <> 0 doesn't work?
    I've just tried putting

    -Is Not Null
    ->0

    In the in the criteria field. It would work fine for one of the UserInfoDropDownXX columns, but once I applied the criteria to more then one column I wouldn't get Is it better to work from the SQL view?

    I've tried, i'm sure I did a horrible job putting the code in and that's why it's not working.

    Code:
    SELECT dbo_ViewUser.FirstName, dbo_ViewUser.UserInfoDropDown1, dbo_ViewUser.UserInfoDropDown2, dbo_ViewUser.UserInfoDropDown3
    FROM dbo_ViewUser, dbo_ViewDropDownFields
    WHERE isNULL(dbo_ViewUser.UserInfoDropDown1) <> 0;
    but that's probably poorly typed.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Lith
    I'm just a student, but as near as I can tell this database I'm currently working on is pretty far from being properly normalized.
    ...
    This database may be heirarchical ... In which case you cannot apply relational normalization to it ...

    As for you issue: You are going to have to make a series of queries tying the UserDropDownXX to a specific Index_ value ...

    Ted was trying to do like this: NZ([UserDropDownXX],0) <> 0

    You could do us all a BIG favor by posting some sample data from both tables to look at ... It'll give a us a better idea of wht you're after.
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Ok sure thing I appreciate the help alot, attatched is a excel spreadsheet with a few rows of data from both tables.

    After looking over the data I was thinking some kind of temporary query might work, but really I didn't have any idea how to do it using VBA or with queries.

    So thanks again and I'll try to look up exactly what the NZ function is.
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Just seeking clarification on where I should use the NZ function, in the criteria box or just edited the SQL from the SQL view.

  9. #9
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Any help possible with this? I've been looking at using code adodb to evaluate row by row but if it was possible to solve it just using this NZ function I'd be all ears.

  10. #10
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Solved my problem using a union query to get the 10+ rows down to 1 row and then > 0 to remove any variables with 0.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •