Results 1 to 6 of 6
  1. #1
    cjd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    4

    Question query get name of data field in one table to match with a record in other table

    table 1 prodkey field1 field2 field3

    table 2 prodkey description fieldno

    eg table1 prodkey field1 field2 field3
    khc Joe bloggs 12


    dir 07451254 red sally

    table 2 prodkey description fieldno
    khc name 1
    khc surname 2
    khc age 3
    DIR tel 1
    DIR colour 2
    DIr name 3

    output for product selected as khc

    name surname age

    Joe bloggs 12

    output for product selected as dir
    tel colour name
    07451254 red sally

    how do i query so that i get based on product codes, the heading names form table 2 with the data from table 1? selection will always be based on product, and only 1 product at a time?

    i know this is unusual, the table is being populated from data where the fields coming through are unknown. This was a solution to stop testing every product for every description to see where to file it.

    any help on query appreciated

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Try this:

    Query1
    SELECT Table2.*, IIf([fieldno]='1',[field1],Null) AS FieldData1, IIf([fieldno]='2',[field2],Null) AS FieldData2, IIf([fieldno]='3',[field3],Null) AS FieldData3
    FROM Table2 INNER JOIN Table1 ON Table2.prodkey = Table1.prodky;

    Query2
    SELECT prodkey, description, fieldno, fielddata1 As MyData FROM Query1
    UNION SELECT prodkey, description, fieldno, fielddata2 FROM Query1
    UNION SELECT prodkey, description, fieldno, fielddata3 FROM Query1;

    There is no query designer or wizard for UNION, must type in the SQL View of query. There is a limit of 50 lines in UNION.

    Query3
    TRANSFORM Max(Query2.[MyData]) AS MaxOfMyData
    SELECT Query2.prodkey
    FROM Query2
    WHERE (((Query2.prodkey)="khc"))
    GROUP BY Query2.prodkey
    PIVOT Query2.description;

    The crosstab will fail if there is more than one record for each prodkey in table1.
    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
    cjd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    4
    that is almost what i need - thank you so much. the only thing is that I have multiple details records in table 1 that match prod key khc.
    i will try and work out from the code how to get the full list.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The crosstab will fail unless there is another identifier field that can be used along with prodkey as row heading.
    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.

  5. #5
    cjd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    4
    i have an order number as well - sorry I should have expanded my example a bit more - i think i have the first query working with expanded example.

    eg table1
    prodkey field1 field2 field3 orderno item
    khc Joe bloggs 12 1 1
    khc sue smith 10 1 2
    khc lucy brown 7 1 3

    table 2 as above

    i have changed query 1 & 2 to include orderno and item
    battling with cross tab query now


  6. #6
    cjd is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2012
    Posts
    4
    Thank you so so much for your help. I've got it working perfectly. I had the right idea and was going in the right direction, but did not know how to pull all those different queries together.

    I really appreciate it - hope the rest of your day is great.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-05-2011, 08:13 PM
  2. Replies: 11
    Last Post: 12-04-2010, 10:20 AM
  3. Replies: 1
    Last Post: 12-02-2010, 04:54 PM
  4. Replies: 1
    Last Post: 11-05-2010, 04:51 AM
  5. Replies: 1
    Last Post: 05-17-2010, 12:21 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