Results 1 to 6 of 6
  1. #1
    danjc424 is offline Novice
    Windows XP Access 2000
    Join Date
    Jan 2011
    Posts
    6

    Relating One Field in a Table to Multiple Fields in Another

    Hi,



    Can anyone help me out with this problem?

    I am creating a table within my database that contains the fields: Work Order #1, Work Order #2, Work Order #3, Work Order #4. Each record in this table represents what I call a "Reasonable Accommodation" or "RA." So every "RA," or record, can have values in any or all of these Work Order Fields.

    What I am trying to do, is have the user be able to import an excel file into ONE table that contains a master list of Work Order #'s. Then, I wanted to create relationships between this imported table and EACH of those four Work Order Fields in my original table. In doing so, I wanted to create a query that would display only those Work Order #'s that were also contained in the imported Work Order File. I am finding that if I create a relationship between Work Order #1 and the Work Order Import File, I get the desired result. But as soon as I create additional relationships between, for example, Work Order #2 and the Work Order Import File, my query displays no results. Is there any way to relate this imported file to each of these four fields so that I can create a query that would display only those values that are also listed in the Work Order Import File?

    Please keep in mind that I have absolutely no SQL/Visual Basic knowledge so my preferred solution would be in the basic access interface. I am a quick learner, however, so I would accept a SQL response if it was articulated well.

    Thanks in advance for your help.

  2. #2
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by danjc424 View Post
    Hi,

    Can anyone help me out with this problem?

    I am creating a table within my database that contains the fields: Work Order #1, Work Order #2, Work Order #3, Work Order #4. Each record in this table represents what I call a "Reasonable Accommodation" or "RA." So every "RA," or record, can have values in any or all of these Work Order Fields.
    This type of model will give you fits down the road. I would break it down into at least 2 parts,
    WorkOrders and WorkOrdersDetails. You could include a field for RA.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do some reading and work through some examples on Normalization. This will get your table structured properly for any and all queries, reports etc down the road..

    Good luck..

  4. #4
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    21
    It sound like outer joins are needed in your query rather than inner joins. Read up a little on this.

  5. #5
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    At a guess I would say that you have gained very little.

    I you want further help please post a pic of your table designs.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Rainlover View Post
    At a guess I would say that you have gained very little.
    I you want further help please post a pic of your table designs.
    You probably didn't notice that you responded to a 8.5 year old post?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-14-2010, 08:35 AM
  2. Replies: 1
    Last Post: 11-11-2010, 11:00 AM
  3. Lookup column relating items in same field
    By rmroberts76 in forum Access
    Replies: 8
    Last Post: 06-05-2010, 07:41 PM
  4. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  5. Replies: 0
    Last Post: 01-08-2009, 05:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums