Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    8

    Unanswered: Access Relationship HELP.

    Hi all,

    Can you look at the relationships on my table and make any possible suggestions for my requirements please? Not touched access in a while and im unsure of what im doing wrong here.
    It’s a database on meetings within the workplace and is needed to keep track of actions and employees who the actions are assigned to. I need the database to list all the actions from each meeting and also all actions assigned to each employee.
    Currently I am unable to get all actions for each employee from all of the meetings they have attended under their name, it duplicates the name in the person table when entered on a form i created. Would this be due to the relationships between the tables? How would I go about rectifing this?

    Any help much appreciated.

    Many thanks,

    Chris
    Attached Thumbnails Attached Thumbnails Relationship.jpg  

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I'm having a little bit of trouble visualising what the problem is exactly...

    Your relationships appear to be setup as a one to many relationship, which could explain why the people are being listed numerous times. Is this what you want?

    A little more information would be helpful, like the SQL you're using for the form. A screenshot of the relationships setup by itself isn't really enough, to go on.

    You could alternatively post your db with a few guidelines to explain what currently happens and what you want to happen.

  3. #3
    Join Date
    Jul 2011
    Posts
    8
    Thanks for your reply.

    I want the table "person" to hold a unique list of employees only however i would like to be able to search by that person and bring up all the available actions from all the meetings they have attended, when i do this currently it duplicates the name each time, is this to do with how i have my replationships setup? How would i make this into a 1:1 to test your suggestion below? If this isnt clear please let me know.

    Chris

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    If I'm following you correctly...

    You want to be able to search for an employee ID, lets say, '101'.

    You put this ID into a form (not sure how you're 'searching'), and you want the form to produce a set of results that look like:

    Code:
    101   -   Meeting 1   -   Action 1
                              Action 2
                              Action 3
              Meeting 2   -   Action 1
                              Action 2
    Am I on the right track here? If so, the problem will be with your SQL statement, not with the table relationship. You're probably missing the DISTINCT keyword in your SQL statement.

  5. #5
    Join Date
    Jul 2011
    Posts
    8
    Yes thats correct,

    Im glad the relationships seem solid and efficent but where do I use the DISTINCT feature? Like I said not used access in a while so finding my way around as I go again.

    Many Thanks,

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    DISTINCT is an SQL keyword, so will appear after the 'SELECT' keyword.

    For example:

    Code:
    SELECT DISTINCT TblPerson.[PersonID] FROM blah WHERE blah.[blah] = blah
    How exactly are you returning the data from your search at the moment. I'm assuming you're using SQL to query and find all matching meetings?

  7. #7
    Join Date
    Jul 2011
    Posts
    8
    Excellent, it now seems to be working and i suppose the last question i could ask you is:

    If there are muliple actions in a meeting how do i populate them within one form when i only have one column named action? for example:

    Meeting Name: Databases
    Action: eg1
    Action: eg2
    Action: eg3

    how do i put multi actions in a form without adding more columns named action1, action2 action3 etc... in the table?

    Thanks

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Where are your 'actions' held at the moment?

    I can't see any fields on the pic in your OP which look like they hold that kind of information.

    If there's a seperate table for actions, you'd do it similarly to the way you've got it setup at the moment. Match up ID's between the two tables when there's a relationship setup.

    Without knowing where the 'actions' are stored within the database it's a little tricky to advise.

    Pleased you got the other bit working alright!

  9. #9
    Join Date
    Jul 2011
    Posts
    8
    Appologies, Its called Minute detail

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You do something similar to what you did before.

    Something like:

    Code:
    SELECT DISTINCT TblPerson.[PersonID], DISTINCT TblMinuteDetails.[MinuteName], TblMinuteDetails.[MinuteDetail]
    FROM TblPerson INNER JOIN TblMinuteDetails ON TblPerson.[PersonID] = TblMinuteDetails.[PersonID]
    WHERE blah = blah
    That might work, can't test it out at the moment.

    Failing that, you can use two queries, one that leads on from the other, to achieve what the above is trying to do.

Posting Permissions

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