Results 1 to 6 of 6
  1. #1
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35

    Question Join 4 Tables in 1 Query

    Hi every1,



    I have a Table joining problem. Please help me to do this Query.

    I have 4 table and each table have 3 columns each. All Tables have ID as their
    primary key.

    Here's the table names and columns (a, b , c are column names)

    Tab1 - ID, a, b
    Tab2 - ID, c, d
    Tab3 - ID, e, f
    Tab4 - ID, g, h

    I need to change the order of columns in retrieved data should this order
    ID, a, h, c,g,b,d, e, f
    Can Order by statement can be use with Table joining?

    I can join 2 tables but, I need to join all 4. Not only that I need the exact order (not the ID, a ,b, c, d, e, f, g, h order)

    Using Order by is the biggest problem I have. Hope this is easy but I'm new 2 Database.

    Please kindly help me to do this Query

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Order By clause orders records, not fields. Fields are ordered by the sequence they are listed in the SELECT clause. Why should this matter? Use the Access query designer to build the query. If still have problem, post the SQL statement for analysis.

    SELECT Tab1.ID, Tab1.a, Tab4.h, ... ORDER BY Tab1.ID;

    Does every table have all the same ID values? If not, will need to include in the query a master table that has all the possible ID values.
    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
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Here's the code,
    SELECT Main.SVID, Main.Genus, Main.Species, Climate.Zone, FlowerCol.FlwCol, LeafCol.Leafcol
    FROM Main, Climate, FlowerCol, LeafCol
    WHERE (((Main.SVID)=[Climate].[SVID] And (Main.SVID)=[FlowerCol].[SVID] And (Main.SVID)=[LeafCol].[SVID]))
    ORDER BY Main.SVID, Main.Genus, Main.Species;
    biggest problem is order by, It will show all columns i select, but I only need to show
    SVID, Genus, Species in query results.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't want the field to show in query, just uncheck them in the query designer, can still be in the WHERE or ORDER BY clause. But then, so what if they show? If you use the query as RecordSource for form or report, just don't bind any controls to those fields.

    There are no JOIN operators in this query. Related records are therefore not joined. Does your query output look like what you want?

    If you don't want these fields (Climate.Zone, FlowerCol.FlwCol, LeafCol.Leafcol) to show, why are you selecting them? They aren't included in either the WHERE or ORDER BY clauses.
    Last edited by June7; 06-12-2011 at 06:30 PM.
    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
    sandlucky is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Mar 2011
    Posts
    35
    Thanxalot,

    Please can you tell me how to add Where clause (Where Climate='Wet') in this same query.
    You know I already have
    WHERE (((Main.SVID)=[Climate].[SVID] And (Main.SVID)=[FlowerCol].[SVID] And (Main.SVID)=[LeafCol].[SVID]))

    in my Query

    Now I need to include (Where Climate='Wet') within the same Query

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have a field in one of the tables called 'Climate'? In the query designer, drag this table to the grid then in the criteria row, type the word 'Wet'.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-13-2011, 06:04 PM
  2. Join for multiple tables?
    By Etownguy in forum Queries
    Replies: 3
    Last Post: 05-30-2011, 04:54 PM
  3. how do i join records from two tables via a form
    By Kananelo in forum Programming
    Replies: 2
    Last Post: 02-24-2011, 02:01 AM
  4. Join three or more tables in many to many link
    By elicoten in forum Database Design
    Replies: 3
    Last Post: 02-04-2010, 06:51 PM
  5. Replies: 3
    Last Post: 08-06-2009, 11:49 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