Results 1 to 7 of 7
  1. #1
    Remster is offline Competent Performer
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    317

    Simple forms/queries question (I hope!)

    Hi Folks



    I’m trying to create a database that (for the purposes of this question) contains two tables:

    Table 1: People
    Fields: employee number; last name; first name

    Table 2: Documents
    Fields: document number; document name; owner; administrator

    The two tables are related in such a way that both the owner and the administrator fields in Table 2 are to be populated by data from the employee number field in Table 1. So far so good.

    The problem I have is this. I want to run a query that shows for each document the employee numbers, the last names and the first names of its owner and its administrator. I assume I need my query to contain the following fields: document number (T2), document name (T2), owner (T2), last name (T1), first name (T1), administrator (T2), last name (T1), first name (T1). But how do I tell Access that the first last name and first name are to correspond to the owner’s employee number, while the second last name and first name are to correspond to the administrator’s employee number?

    Apologies if this really is as basic as it sounds – this is the first time I’ve tried to create a database from scratch. The only option that’s occurred to me is to remove the owner and administrator fields from Table 2 and create two separate tables, one of which links an owner’s employee number, last name and first name to each document number, and the other of which links an administrator’s employee number, last name and first name to each document number. Then the query can expressly refer to each of these tables individually.

    Thanks

    Remster

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would add the first table to the query twice (Access will give the table an alias name for the second instance, which you can change). One joins to the owner field, the other to the administrator.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317
    Quote Originally Posted by pbaldy View Post
    You would add the first table to the query twice (Access will give the table an alias name for the second instance, which you can change). One joins to the owner field, the other to the administrator.
    But how will Access know which names to add in each last name and first name field? As I said in my original post, the query has the following fields:

    Document number (T2), document name (T2), owner (T2), last name (T1), first name (T1), administrator (T2), last name (T1), first name (T1)

    I've tried this already, and the query gives the last name and first name corresponding to the owner's employee number both times. This suggests that my last two fields aren't connecting in the right way to the administrator field.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You join one instance of table 1 to the owner field in table 2, the other instance to the administrator field. In the output, you have to make sure you use the aliased name of the table for whichever one was aliased. In SQL view that would look like:

    Table1_1.FirstName

    if you leave the name used by Access (which will add "_1" to the actual name).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Access look on people and people_1 as different tables.

  6. #6
    Remster is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    317
    Aaah, I think I'm with you. I'd misunderstood 'You would add the first table to the query twice', but I know what you're talking about now. Thanks, folks.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Simple Question...I hope
    By AndrewCoy in forum Access
    Replies: 1
    Last Post: 07-18-2010, 10:06 AM
  2. Replies: 5
    Last Post: 07-15-2010, 10:05 AM
  3. simple linking question I hope
    By wing8lc in forum Queries
    Replies: 4
    Last Post: 01-21-2010, 03:13 PM
  4. Very simple question!
    By fiddley in forum Programming
    Replies: 2
    Last Post: 04-28-2009, 02:16 AM
  5. Replies: 4
    Last Post: 04-01-2009, 08: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