Does Access poorly design multi-table queries?

[Guest]

I have a query that uses the same table up to 6 times. Aside from the regular
one-to-many relationships, the table itself has a one-to-many relationship as
it gets used over and over again.

Access adds _1, _2, _3, etc. when you keep using the same table. Is this
inefficient, and if so, how can I fix it using an SQL statement? Here is my
nightmare of a SQL View from the Access-designed query:

SELECT Reporting_Marks.Initials, Master.Number, Master.LE,
STCC_used.Commodity_Description, Master.Tons, IIf([Customer633.Name] Is
Null,"**" & [Customer633.a633],[Customer633.Name]) AS Consignee,
IIf([Customer633_1.Name] Is Null,"**" &
[Customer633_1.a633],[Customer633_1.Name]) AS Shipper,
Stations333_1.StationName, Stations333_2.StationName,
Stations333_3.StationName, Stations333_4.StationName, Contents.Contents,
Stations333.StationName, [Reporting Marks_1].Initials, Master.Waybill_Date,
Master.Waybill, Symbols.[Train Symbol]
FROM Stations333 AS Stations333_4 INNER JOIN (Stations333 AS Stations333_3
INNER JOIN (Stations333 AS Stations333_2 INNER JOIN (Stations333 AS
Stations333_1 INNER JOIN (Symbols INNER JOIN (STCC_used RIGHT JOIN
(Reporting_Marks INNER JOIN ((Customer633 AS Customer633_1 INNER JOIN
(Contents RIGHT JOIN (Query1a RIGHT JOIN (Reporting_Marks AS [Reporting
Marks_1] RIGHT JOIN (Customer633 INNER JOIN (Stations333 INNER JOIN Master ON
Stations333.StationID = Master.OperStatID) ON Customer633.CustID =
Master.ConsigneeID) ON [Reporting Marks_1].InitialsID = Master.RAJP_ID) ON
Query1a.SCHI_ID = Master.SCHI_ID) ON Contents.ContentsID = Master.ContentsID)
ON Customer633_1.CustID = Master.ShipperID) INNER JOIN From_To_Symbols ON
Master.[Record Num] = From_To_Symbols.Record_Number) ON
Reporting_Marks.InitialsID = Master.InitialsCode) ON STCC_used.STCC =
Master.STCC) ON Symbols.SymbolID = From_To_Symbols.[Symbol ID]) ON
Stations333_1.StationID = Master.OriginID) ON Stations333_2.StationID =
Master.DestID) ON Stations333_3.StationID = From_To_Symbols.FromID) ON
Stations333_4.StationID = From_To_Symbols.ToID;

 

[Douglas J Steele]

When you use the same table multiple times, you must have some way of
identifying which of the instances of that table you want to use. That's one
of the reasons why "Aliasing" was introduced into SQL. That's what the
"Stations333 AS Stations333_4" or "Stations333 AS Stations333_3" statements
are.

Microsoft chooses to use aliases that are the name of the table followed by
a number, but they could just have easily used Tom, Dick or Harry as the
alias names.

Why do you think it's inefficient?

 

[Guest]

Self joins on a table are often warrented. For example you have an employee
field and a boss field. As a boss is also an employee, you do a self join on
the table.

But up to 6 times? Possibly it's your database design that's the problem.

 

[aaron.kempf]

access sucks balls for anything grow up and don't listen to these
sissies