Join 4 Tables in 1 Query

[sandlucky]

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

[June7]

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.

[sandlucky]

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.

[June7]

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.

[sandlucky]

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

[June7]

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'.