Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normalized myself into trouble (Access XP Sp3, Windows XP)

    Hello,

    What I'd like to do is have a flat, de-normalized file of, say, MainTopic and Reviewer1, Reviewer2 ... Reviewer(N), when the data exists in two tables -- the Main Topic table that is unique, and the Reviewer table that are the "many" to that.

    On the SQL Server, I usually gerry-rig it by calling a min(Reviewer), and then call out for a sub-select for the max(Reviewer) etc., w/ comparisons in a where clause to not pick it if the two Reviewers are the same, or outer-join the Reviewer table several times over, and, well, that isn't ideal.

    I've tried crosstabs, but .. the Reviewers are many -- anyway, I got myself in a pickle. I can use code or a nested report to get myself out of trouble, but ... does anyone out there know of a brilliant way to "pretend" de-normalize my two tables? <gg>

    thx
    Pat

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Not sure what you mean by "pretend" denormalize. You can download a free sample database with code to normalize/denormalize from Roger's Access Library.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Hi Patricia

    Can you just just call a view

    SELECT MainTopic.*, Reviewer.*
    FROM MainTopic, Reviewer;

    That will provide a completely denormalised view
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Thank you, I downloaded Roger's Access Library and, the code will be perfect. Looks like I'll have to go the code route! I'll have to wait 'til this evening to send him a $1 via Amazon, though, so I don't feel quite right, yet <gg>.
    thx
    Pat

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Well, it doesn't want to do exactly what I wanted, but thanks, Jezza!
    I need "MainTopic - Reviewer1 - Reviewer2 - Reviewer3 .." etc -- kind of a flat pan across, just like those wide tables that are the horror of most good database people, that's the type of layout I need now. It sounds like code is the best way to go,
    thx
    Pat

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    No probs Pat

    It was a shot in the dark <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    But I do what to know what "QuidQuid latine dictum sit, etc etc means." (Whoever speaks latin, will be seen as "something else?" <gg>)
    thx
    Pat

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Click on my Jezza Link to the <----- left, and you will understand <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> all is explained
    Jerry

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Have a look at the thread beginning with <post#= 514,047>post 514,047</post#>

    It talks about using a concatentation function to bring together values from different records.

    I think you could use it to concatenate the reviewers for each topic into a single field in a query, then use that for a report.
    Regards
    John



  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    Thanks, John. I don't think that would really work for me, in part because I the "many" side can be an unknown, non-fixed number, and the other part is -- I tried something similar at first, but I still ended up w/ redundant rows, although nicely flat <g>. I've modified the one that Hans put me on to fit what I need.
    thx
    Pat

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Normalized myself into trouble (Access XP Sp3, Windows XP)

    I believe that the concat function is designed to deal exactly with the situation where you have an unknow number of rows on the many side.

    For each record in the one side, find all matching values in the many and concatenate them together.

    But if you have an answer that works for you, then obviously you don't need to use concat.
    Regards
    John



Posting Permissions

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