Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Roster Listing (Access 2000 SP1)

    I am running Access 2000. I have a problem that I can't seem to figure out. I have a database that has the following fields:
    BowlerID
    Person
    LeagueID
    This database is not normalized (the data is imported from an old program running on Xenix, and the only output capability is a text file that I use to import), there is a record for each occurrence of a person in a league, ie;

    BowlerID Name LeagueID
    4 Bill Smith 2631
    4 Bill Smith 3631

    I want to be able to create a roster listing all of the people in a specific league (which I can do very easily); however, I also want to also list in the same report any other leagues that each person might be in.

    League Name Other Leagues
    2631 Bill Smith
    3631
    4631
    2631 Jane Smith
    4521
    I am sure there must be an easy solution but I can't seem to see the forest for all of those trees!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Roster Listing (Access 2000 SP1)

    In your report's Sorting and Grouping dialog, set it to group by BowlerID. That will create a group for each bowler and you can list the leagues by bowler.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Roster Listing (Access 2000 SP1)

    Thanks for your suggestion and I apologize for not responding sooner, but this is a busy time in the bowling business and have not even had time until now to try your suggestion. This did not do what I wanted - unless I am not setting it up properly, let me see if I can explain better what I am trying to do.
    First the user will be asked for the league that they want a roster of, the output will be in order of BowlerID. I then want to provide any other leagues that that individual bowler may have bowled in.

    Bowlers for Monday League (2030)

    BowlerID Bowler Other Leagues

    1111 Joe Smith 2151, 2321
    2222 Ben Smith

    and so on. As I mentioned before this database is not normalized - so I have a record for every instance of BowlerID, BowlerName, LeagueID.

    Thanks again for your help.

    shihalud

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

    Re: Roster Listing (Access 2000 SP1)

    If you really want the other leagues to be on one line, comma-separated, you'll have to use Visual Basic to concatenate the items. I don't know how familiar you are with VBA.

    It is not very hard to create a query that lists the other leagues below each other. Add the bowlers table twice to the query grid and join the copies on BowlerID. Set the criteria for LeagueID to prompt for a parameter and also specify that it should be different from the LeagueID from the other copy. Since a picture says more than a thousand words, I have attached a screenshot of such a query.
    Attached Images Attached Images
    • File Type: png y.PNG (8.3 KB, 0 views)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Roster Listing (Access 2000 SP1)

    Thanks this worked just fine - I will have to work on the printing situation; but this gets all of my info in one place thanks
    shihalud

Posting Permissions

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