Results 1 to 13 of 13
  1. #1
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46

    Very simple link two tables

    Hi, this should be so simple but I can't work out how



    In a table I have columns N, X, Y
    In another table I have columns N1, N2

    How can I make a new table, or run a query to produce:

    N1, N2, X1, Y1, X2, Y2

    Where X1 & Y1 are X & Y referring to N1
    and likewise for referring to N2.

    Hope that makes sense?
    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why not describe the real tables and what you are trying to do? You have given some sort description that doesn't show what you have tried.

    You may want to review http://www.w3schools.com/sql/sql_join_inner.asp

  3. #3
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46

    Talking Hopefully a little clearer

    N1 N2
    1 2
    2 3
    4 3
    12z 4
    Table 1








    N X Y
    1 375 219
    2 369 208
    3 312 275
    4 268 342
    12z 298 529
    Table 2









    I want to combine both to have something like:

    N1 N2 X1 Y1 X2 Y2
    1 2 375 219 369 208
    2 3 369 208 312 275
    4 3 268 342 312 275
    12z 4 298 529 268 342









    There's a lot more data than just this - but that is all I want to do.
    Hopefully that's a little clearer?

    Thanks Orange

  4. #4
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    I've reviewed that link, but that only tells me how to link it once.

    Using the code below, I get columns N1, N2, X1 & Y1. Does anyone know how I can get X2 & Y2 involved as well. It's really annoying me...... Thanks!

    SELECT Table1.N1, Table1.N2, Table2.X AS X1, Table2.Y AS Y1
    FROM Table1
    INNER JOIN Table2
    ON Table1.N1=Table2.N;

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What do N1 and N2 represent? Same for N, X1,X2,Y1,Y2.

    It looks like you have spreadsheet app that you're trying to solve using Access -- but I have no idea what these things represent.
    Perhaps, it's as simple as adjusting table definitions, or concatenating results.

  6. #6
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Thanks Orange,

    Access is required for this operation due to automation required with changing numbers.
    N is a node number, X and Y are coordinates. Table1 has the node numbers for the links, Table2 has the coordinates for each node.
    So I have Links formed from two nodes (N1 and N2), and coordinates for these nodes (X1 & Y1 for N1, X2 & Y2 for N2).
    By producing this final table I'll have start and end coordinates for the links - which I can then convert into lines in a GIS package.
    This step is vital! Hope that's a little clearer

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You need a table structure to handle records

    tblNodes

    NodeId autonumber PK
    NodeName text
    XCoord Number
    YCoord Number
    otherNodeSpecific

    One record one fact

  8. #8
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Thanks Orange,
    I'm not sure I really understand how that relates to my tables, call me an idiot if you like?!
    Table1 and Table2 are both linked to txt files so I'm not sure I can change the structure of these....

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    So, tell us exactly what you have

    table1 definition
    table2 definition
    text files layouts.

    If you are going to use access you have to have normalized relational tables.
    If you are coming from Excel spreadsheet or flat text, you have some reconfiguration to do.

    But we need all of the structures to help and advise.

  10. #10
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Sorry if I'm being a bit dim, but aren't the structures included with my attached tables?

    Table 1:
    N1 - Text
    N2 - Text

    Table 2:
    N - Text
    X - Number
    Y - Number

    Table 1 is linked by using VBA to one text file
    Table 2 is linked by VBA to another text file

    So I want to have:
    N1 - Text from Table 1
    N2 - Text from Table 1
    X1 - Number - this is X from Table 2 where N1 equals N from Table 1
    Y1 - Number - this is Y from Table 2 where N1 equals N from Table 1
    X2 -Number - this is X from Table 2 where N2 equals N from Table 1
    Y2 - Number - this is Y from Table 2 where N2 equals N from Table 1


    Hope that helps

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't think you are being dim. The difficulty in trying to respond to any request is to get the gist of the problem, and the context in order to offer some suggestions. From your posts I got table and columns, then nodes and coordinates, then some desired output format that looks like a spreadsheet. on rereading, I see Links (which has a different meaning in Access context) -

    From coordinates, I think Points. You said the coordinates represent Nodes, so there are Nodes.
    From Links, I am getting Network or Network segment.

    So in database terms--

    Consider building /describing a Network. The network is composed of one or more segments where each segment consists of 2 nodes and each node is identified by a Point which means of a pair of coordinates.

    Network-1-m>Segment-1-2>Nodes------Point(x,y)

    Net1 -----Segment1
    -----Segment2
    -----Segment3

    Segment1--Node1 to Node2
    Segment2--Node2 to Node3
    Segment3--Node3 to Node4


    Node1--X1,Y1
    Node2--X2,Y2
    Node3--X3,Y3

    I am attaching a few jpgs to show the Tables and relationships, the table fields and values,
    a query design and output based on my interpretation of your setup.

    I set up the tables based on your data.

    Here's the query sql
    Code:
    SELECT Network.NetName
    , Segment.StartNode
    , Segment.EndNode
    , NodesCopy2.XCoord
    , NodesCopy2.YCoord
    , NodesCopy1.XCoord
    , NodesCopy1.YCoord
    FROM NodesCopy2 INNER JOIN
     (NodesCopy1 INNER JOIN (Network INNER JOIN
     Segment ON Network.Netid = Segment.NetId) ON NodesCopy1.NodeId = Segment.EndNode) ON 
    NodesCopy2.NodeId = Segment.StartNode
    WHERE (((Network.NetName)="sample1"));
    NOTE: The Nodes table is used (TWICE) TO
    -identify StartNodes and EndNodes. I made separate copies of the Nodes Table as Copy1 and Copy2 to differentiate the use of the same table for 2 different purposes. 1 to identify StartNodes and 1 to identify EndNodes.


    Good luck with your project.
    Attached Thumbnails Attached Thumbnails NetworkandNodesRelationships.jpg   NetworkAndNodesTables.jpg   NetworkAndNodesQueryDesign.jpg   NetworkAndNodesQuery_sql.jpg   NetworksAndNodesQueryOutput.jpg  


  12. #12
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Thanks for your help Orange, but I think there's been a breakdown in communication somewhere and I think you're reading too much into my brief posts.
    I've managed a workaround by linking Table 2 twice and using the following query:

    SELECT Table1.N1, Table1.N2, Table2a.X AS X1, Table2a.Y AS Y1,Table2b.X AS X2, Table2b.Y AS Y2
    FROM
    (Table1
    INNER JOIN Table2a
    ON Table1.N1=Table2a.N)
    INNER JOIN Table2b
    ON Table1.N2=Table2a.N

    I'm sure there are better ways to do this, not least linking the same table twice isn't ideal, but it works now and I can use the results for my next stage. I can also try and speed up this process later on when I'm more proficient at SQL.
    Thanks for persisting

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Linking the table twice is not an issue if you are using the table for separate things.

    It's a bit like having and Employees table and using it to identify

    - regular employees
    - managers/supervisors.

    I don't think our solutions are much different.

    How close was my network analogy? -- easier for me than N1 and N2

    Glad you got it working.

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

Similar Threads

  1. link tables using vba
    By dumbledown in forum Import/Export Data
    Replies: 3
    Last Post: 03-09-2012, 05:27 AM
  2. Link tables
    By compooper in forum Import/Export Data
    Replies: 2
    Last Post: 06-23-2011, 07:08 PM
  3. Trying to Link tables
    By brandonze in forum Access
    Replies: 1
    Last Post: 05-19-2011, 11:03 PM
  4. One-way link between tables
    By Mikele di Sagitter in forum Database Design
    Replies: 1
    Last Post: 07-23-2010, 08:26 AM
  5. Best way to link tables together?
    By marleyuk in forum Access
    Replies: 1
    Last Post: 06-07-2009, 08:44 AM

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