Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unhappy Unanswered: help with sql query

    hi all , i have an access table which has ID`s in .
    in another table i have the same ID`s with data entries in, but there may be more than one entry to an id. The entries have already been sorted by the latest date and i need all the entries now by the id .What i need to do is select the is from one table match it up to the other table but display all the data entries on one line .Is this possible? something like
    Code:
    table a
    ID 
    ---
    1
    2
    3
    
    table b
    ID     |   entry
    --------------
    1             a
    2             b
    3             c
    1             a1
    2             b1
    3             c1
    i would need the result:
    Code:
    id     |     entry   |   entry 2 
    ----------------------------
    1              a          a1
    2              b          b1
    3              c          c1
    Or ideally:
    Code:
    id     |     entry   
    ----------------------------
    1              a , a1
    2              b , b1
    3              c , c1
    thanks all in advance
    Last edited by davieboy_xr; 04-27-04 at 17:29.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    The easiest way and most flexible way to do this is with VBA.

    However, if you know that you will only have a set number of Entries per ID (say 5)

    You can use the following SQL statement

    Code:
    SELECT TB3.ID, Max(TB3.Entry1) AS Entry1, Max(TB3.Entry2) AS Entry2, Max(TB3.Entry3) AS Entry3, Max(TB3.Entry4) AS Entry4, Max(TB3.Entry5) AS Entry5
    FROM (SELECT TB2.ID, TB2.Entry, TB2.Rank, IIf([Rank]=1,[Entry],"") AS Entry1, IIf([Rank]=2,[Entry],"") AS Entry2, IIf([Rank]=3,[Entry],"") AS Entry3, IIf([Rank]=4,[Entry],"") AS Entry4, IIf([Rank]=5,[Entry],"") AS Entry5
    FROM (SELECT TB.ID, TB.Entry,
     (Select Count (*) from TableB Where [Entry] < [Tb].[Entry] ANd [ID] = [TB].[ID])+1 AS Rank
    FROM TableB as TB
    ORDER BY TB.ID, TB.Entry) AS TB2) AS TB3
    GROUP BY TB3.ID;
    The table I used was Table B with the following data
    Code:
    ID	Entry
    1	a
    1	a1
    2	b
    2	b1
    2	b2
    3	c
    3	c1
    The results were

    Code:
    ID	Entry1	Entry2	Entry3	Entry4	Entry5
    1	a	a1			
    2	b	b1	b2		
    3	c	c1

    S-
    Last edited by sbaxter; 04-27-04 at 18:15. Reason: Change Brackets

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    wow .. thanks... one more question. Im very limited in my knowledge of sql so how would i get these reults from the object designer in ms access.Thanks again

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    You could use ADO, Open this statement as a recordset and do anything you want with it.

    S-

  5. #5
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks i can see that it is using just one table... so how would i now select the values if i had more than 5 values???
    Last edited by davieboy_xr; 04-27-04 at 19:17.

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    Adjust to SQl statement to allow for more


    To make it totally dynamic, Process it in VBA
    S-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •