Results 1 to 3 of 3
  1. #1
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114

    2 Columns with the same information and the query gives the right information on the wrong column

    Sorry for the weird title. :0

    Here's my problem, I am building a specifications database that uses unique item numbers/description to differentiate between items. Part of the exercise is creating a Parent/Child relationship where the Parent Item has multiple Child Items assigned to it. Think in terms of a formula. The product (parent) is called "1" and you need three ingredients (children) to make 1, Items 2, 3 & 4. The table is set up like this:

    1 | 2
    1 | 3
    1 | 4

    All items (both parent & child) reside in the same table because they are the same format, each is unique and I need to use them interchangeably, i.e. a parent item number for one formula may become the child in another formula.

    This setup works fine, except when I run a query. I need the query to pull the Parent Item #, Child Item # and Child Item Description. However, the query can't differentiate between the two item numbers and always pulls the Parent Item Description. I have split the numbers into two tables, Parent & Child, and got the query to work fine. But I would rather have one table from which I can pull any item number.


    Query Design
    Click image for larger version. 

Name:	Comp Query  01.JPG 
Views:	8 
Size:	50.0 KB 
ID:	19470

    Query With Unified Item Number Table (The description is for the parent item #)
    Click image for larger version. 

Name:	Comp Query  02.JPG 
Views:	8 
Size:	100.8 KB 
ID:	19471


    Query with items split into two tables (The description is for the child item #)
    Click image for larger version. 

Name:	Comp Query  03.JPG 
Views:	8 
Size:	103.2 KB 
ID:	19472

    I want the 1st query to look like the 2nd query.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    Provide example of raw data table. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    Sorry for the delayed response, I got pulled away. I actually followed the advice you gave me in another post, "Don't use the relationship editor". After I killed the link between the tables and renamed the column it allows me to combine the parent and child item numbers into one table. Then I used a form with combo boxes to control the information that's entered into the table. It works perfectly and my queries are delivering the correct information. Thanks!

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

Similar Threads

  1. Replies: 4
    Last Post: 11-25-2014, 11:23 AM
  2. Replies: 3
    Last Post: 05-02-2014, 09:27 AM
  3. Form Combo Box Saving Wrong Information
    By Steve62 in forum Reports
    Replies: 5
    Last Post: 11-12-2012, 02:29 PM
  4. Distribute row information to columns
    By HowardlyDog in forum Access
    Replies: 3
    Last Post: 06-18-2012, 08:19 AM
  5. Replies: 1
    Last Post: 09-30-2011, 06:54 PM

Tags for this Thread

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