Results 1 to 3 of 3
  1. #1
    dambrown is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    2

    Adding data to a query

    Updating this from previous explanation, I feel this is a better way to ask.



    I have two tables, T1 and T2. T1 has the following information:
    T1
    Order----Name------Other Info

    101-------Item A-------1
    101-------Item B-------2
    102-------Item C-------3
    103-------Item B-------4
    103-------Item D-------5
    103-------Item C-------6

    T2 has the following information:
    T2
    AddName--------LinkedItem

    Item E------------Item B
    Item E------------Item D

    Every time there is Item B or Item D, I'd like to add the AddName to the Order field. I can create a query to find out every Order the LinkedItem appears in (see below for example result for Query1)
    Q1
    T1.Order---T2.LinkedItem----T2.AddName
    101---------Item B-------------Item E
    103---------Item B-------------Item E
    103---------Item D-------------Item E

    But I'd like to create a query where the field "Name" could apply to both T1.Name and T2.Name, and that it would add to what we see in T1, and fields that T1 has and T2 doesn't have would appear as null values. For end result example, see below.
    Q2
    Order----Name------Other Info
    101-------Item A-------1
    101-------Item B-------2
    101-------Item E-------
    102-------Item C-------3
    103-------Item B-------4
    103-------Item E-------
    103-------Item D-------5
    103-------Item E-------
    103-------Item C-------6

    How would I write SQL to accomplish this? Where Query 2's Order and Name fields are pulling from T1, and if T1's Name matched Q1's (from T2) LinkedItem, then it would also pull Q1.Order data for Query 2's Order field and Q1.AddName data for Query 2's Name field. I would also like for the Other Info field to be left blank when this was the case.

    I'm just getting all sorts of syntax errors when I try things, and when I do write it correctly (avoiding syntax errors), what I get is the AddName information replacing it's LinkedItem counterpart:
    Q2 (No Item B's or Item C's)
    Order----Name------Other Info
    101-------Item A-------1
    101-------Item E-------
    102-------Item C-------3
    103-------Item E-------
    103-------Item E-------
    103-------Item C-------6

    Or I get a similar result as Q1:
    Q2 (No Item A's, B's, C's, or D's)
    Order----Name------Other Info
    101-------Item E-------
    103-------Item E-------
    103-------Item E-------

    And all other information is not displayed.

    Any suggestions would be welcome!
    Last edited by dambrown; 05-02-2011 at 02:31 PM. Reason: Better explanation of what I'd like to do

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    You will need to create two queries and then join them in a Union Query. I have attached a database that does this. Union Queries must match exactly on field names and must be written in SQL. They cannot be created in the QBE. Look at the attached and post back with any questions you have.

    BTW--the field names you used "Name" and "Order" are Access reserved names and should not be used. They may be part of your consternation. For a list of reserved field names and symbols, look at this site.
    http://office.microsoft.com/en-us/ac...010030643.aspx
    Alan

  3. #3
    dambrown is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    2
    Ah, I used Name and Order in this example to simplify things in my post. My first attempt was too cluttered to clearly show what I was attempting to do, so I had to edit and simplify. All the same, I wasn't aware that Access had reserved names and symbols, and I appreciate the link you provided!

    Thanks for the database! That makes perfect sense, I was able to apply it to my work, and has helped me out greatly!

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

Similar Threads

  1. Replies: 0
    Last Post: 03-29-2011, 09:37 AM
  2. Query involving data from different sources
    By nodnud in forum Queries
    Replies: 1
    Last Post: 08-12-2010, 06:11 PM
  3. Cross check data from 2 sources
    By Zukster in forum Queries
    Replies: 7
    Last Post: 09-22-2009, 10:54 AM
  4. Replies: 0
    Last Post: 03-31-2009, 02:05 AM
  5. Combining textbox and checkbox data
    By jgarner in forum Access
    Replies: 0
    Last Post: 12-11-2008, 11:10 AM

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