Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report From Normalized Tables (Access 2000)

    I have two tables: tblRecruit, tblVisitors. tblVisitors has only two fields: RecruitID and Visitor. tblRecruit and tblVisitors are linked one-many by field RecruitID. A recruit can have no visitors up to three visitors. (At least right now they can only have three - that may change in the future.) That part is no problem. My problem is trying to create a report based on these two tables with the recruit and his visitors appearing on one line. I cannot get the visitors to appear next to the recruit on the same line. The recruit name can only appear once with his three visitors appearing off to the right on one line. The report needs to be in this format:

    Recruit Name Visitor1 Name Visitor2 Name Visitor3 Name

    Make sense? Any help anybody can give me with this would be greatly appreciated.

    Thanks,
    Jon

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report From Normalized Tables (Access 2000)

    Take a look at the attached demo. (Thrown together quickly!)
    It uses a crosstab to produce a result set in the format that you want , having first allocated a Column No. using the mod 3 division. (There may be a better way of doing that part) The report is then easy.

    HTH
    Attached Files Attached Files
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report From Normalized Tables (Access 2000)

    Steve,
    That works great! I had to create another query so I could concantenate first and last names in my report but that was easy. I understand your solution except for the mod 3 part. If you have a few moments, can you explain how that works? It looks like it is creating 3 columns to put each visitor name in. Thanks for your help!
    Jon

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report From Normalized Tables (Access 2000)

    The mod operator is modular division. It gives the remainder only as the answer. From a continuous sequence of figures you would get a sequence of 0, 1, 2, 0, 1, 2 etc. as your answer. Add 1 to get 1,2,3 and you can give each visit a number 1, 2 or 3. This will fall down if your Autonumber sequence has holes in it. There are other ways of generating a sequence of row numbers in a query, but they are slooooow. See Microsoft's Report Samples demo database for an example. (I do not have the URL to hand at present)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Report From Normalized Tables (Access 2000)


Posting Permissions

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