Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: I was moving pretty good in Access, BUT!

    I am working on my first ever database and I was moving along pretty good for a while. I am building a database for to keep up with members of an organization, events, events they have competed in, and the results of the events as far as placements, points earned throughout the year from those placements. I have gotten stuck on on area. I have a form for entering the results of the events with a Results ID, Event ID, and 1st-40th placements. I am able to get the member ID entered in the correct field for placement but I am having trouble getting any queries to return anything other than the member ID. What I would like to have is the member name. I have no idea which direction to go from here and desperately need some guidance if anyone can help. Thanks in advance!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Assuming that you have another table with member ID and member name (ie. lookup type table which should have a relationship of member ID in that table to your other table which just has member ID)....

    For the form, I'm assuming you have a combobox for the member ID field and this has a rowsource based on the member ID and member name table (ie. 2 columns where the column width's are 0";2" and the first column is member ID and the 2nd column is member name.)

    For a query, you'd join the table with just the member ID to the table with member ID and member name. Then drag down the member name field from the other table. Note though: If some member ID's are blank in the main table, you want to make sure your relationship join type between the 2 tables in the query is a "show all records from your main table..." to return records with a blank member ID.
    Last edited by pkstormy; 10-15-08 at 22:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    I have a members table with a members ID, I have a dog's table with a DOG ID. In the dogs table I have a members ID which links it to the members table.
    In the form I have trial ID, Division ID which are working coreectly, I have a combo box which looks up the dogs for 1st place, then the same thing for 2nd place and the same for 3rd place. I haven't added them yet, waiting until I get the proper method worked out, but will need a combo box or some other way of listing a total of forty dogs at each event. I am aiming for a query in which would look up the trial, division, and competitors in which I could use to calculate 2 different point systems based on placements at each event. I also need to track the number of events competed in within a year in the different divisions. I've attached a shot of my relationships and what the query is returning.
    Attached Thumbnails Attached Thumbnails relationship.png   results query.png  

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I would move away from having one table linked to another one on three different fields. You might be better served with an intersection table that links Dogs to Trials, and has a Position field.
    I'd ask you to post a zipped copy, but from the screenshots you're using Access 2007, and I wouldn't be able to open it.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would

    Agreed with Weejas.

    You need another table... one for each race. Then your results table would just have RaceID, DogID and Place. Place would hold a 1 a 2 or a 3. In fact, doing it that way also allows you to record an number of placements per race.

    It also makes it really easy to list dogs that have had ANY placement... and list all dogs who have won a race.... etc.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2008
    Posts
    6
    Just to make sure I'm understanding correctly, I need to create a new table for each of the trials? Can this be automated? What I have done this afternoon is create three junction tables. But when I tried to create a query, I still did not return any of the dog's info. I've tried different help sites but now I've just thoroughly confused myself over it.
    Attached Thumbnails Attached Thumbnails relationship2.png  

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You don't need to create a table for each trial. What is a trial is that like a race?

    Can you zip and upload the database? It would be easier if I could just change your tables to what I think you need and create a couple of simple queries to show you what might be a better way to go.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Oct 2008
    Posts
    6
    Yes a trial is like a race. There can be up to 40 dogs competing, so I need to be able to enter that many entries. It's 2007.
    Attached Files Attached Files

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A little off your original question but don't forget to click on the joining lines in your relationships form and set the "Join Type". In some places it looks like you'd want to make it a "one-to-Many" join where you return all records from the main table and the matching records from the relational table (usually.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks.

    I was thinking you should consider the change to something like this:
    Attached Thumbnails Attached Thumbnails erd1.jpg  
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Oct 2008
    Posts
    6
    I think I understand where you're going with that and follow you. Let me ask this question though. I'll have 30-40 dogs in 10-12 trials a weekend throughout the year. I was trying to build 1 form to handle 1 trial. Is that possible with access? If it could be done it seems it would be easier on the end user. If I'm following you're table correctly it would be filling out a form for every dog in every trial and a record for it correct? Not a problem, just trying to make it as user friendly as possible. Thanks for the help.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I am renown for saying "all things are possible" in reference to what you can do with Access.

    Irrespective of how easy it is, for now, the design seems correct to me. If you want to make it easier for users to be able to add 30 dogs, then that is something EXTRA you build into the system, such as using VBA to go and select a group of 30 dogs and automatically add them all to the necessary tables, leaving the user with only the job of entering a 1, 2 and 3 next to the dogs who earn those places. The only thing to solve then is how do you select the 30 dogs? My guess is that the DOG table needs a DivisionID in it. You can then create a Trial for, say, division A. You can then automatically add all dogs from division A.

    That's if you even need to add any of the dogs which don't place.

    I'm not sure how your design would be dealing with that either.

    Don't misunderstand me here, I don't KNOW that this is right as I haven't actually done a dog management application before; I'm going on instinct here.

    Also, I just noticed, "Number of Dogs" is probably a calculation rather than a stored field. I'd remove it and count the results records related to a trial to count the number of dogs.

    If I'm following you're table correctly it would be filling out a form for every dog in every trial
    Yes, but only a subform record. You'd have a mainform (trial) and a subform (results) where you select a dog and enter a place... in a continuous subform.

    You could also go to your mainform (dog) and enter it into any number of trials by selecting them in the results subform and then again entering a place.

    All that text, not one wise-crack like you're barking up the wrong tree.

    Sometimes I amaze even myself

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Oct 2008
    Posts
    6
    That's all way to deep for my knowledge level! I'm going to go with your first suggestion right now. I may play around with some of the others once I get operational. That's my biggest concern is to get it up and going to do some testing. Thanks again, I appreciate everyone's help and input.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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