problem with query

[drstein]

Can you tell me what is wrong with this query using access 2007? can I have two inner joins?

SELECT mp.MRH_Id, mp.MRP_Plan_Id, mp.MRH_Create_Date, mp.MRH_Submitter, mp.MRH_Batch_Id, mp.MRH_Request_Status, mp.TRC_Code_Id, mp.MRH_Tran_Count, mp.MRH_Batch_Size, mp.MRH_Batch_Percent, mp.MRH_Tran_Code_Description
FROM mp INNER JOIN MPlans ON mp.MRP_Plan_Id = MPlans.MRP_Plan_Id
INNER JOIN MR_Transaction_Reply_Codes ON mp.TRC_Code_Id = MR_Transaction_Reply_Codes.TRC_Code_Id;

 

[MSAccessRookie]

Can you tell me what is wrong with this query using access 2007? can I have two inner joins?

SELECT mp.MRH_Id, mp.MRP_Plan_Id, mp.MRH_Create_Date, mp.MRH_Submitter, mp.MRH_Batch_Id, mp.MRH_Request_Status, mp.TRC_Code_Id, mp.MRH_Tran_Count, mp.MRH_Batch_Size, mp.MRH_Batch_Percent, mp.MRH_Tran_Code_Description
FROM ( (mp INNER JOIN MPlans ON mp.MRP_Plan_Id = MPlans.MRP_Plan_Id)
INNER JOIN MR_Transaction_Reply_Codes ON mp.TRC_Code_Id = MR_Transaction_Reply_Codes.TRC_Code_Id);

MS Access JOINs must be designed to reflect the intended order of processing. Adding the appropriate "(" and ")" characters (see above) might solve your problem.

 

[drstein]

Thank you very much for your answer! It definitely worked!

I definitely want to imporve my use of sql using Access. I am working at a company where I work on various databases like oracle, sql server, access, sybase, db2, and nonstop sql. In all these database languages, I have been trying to use the sql directly instead of a designer.

however if you think I should use the access desginer, then I will try that for the next time.

 

[MSAccessRookie]

Thank you very much for your answer! It definitely worked!

I definitely want to imporve my use of sql using Access. I am working at a company where I work on various databases like oracle, sql server, access, sybase, db2, and nonstop sql. In all these database languages, I have been trying to use the sql directly instead of a designer.

however if you think I should use the access desginer, then I will try that for the next time.

You should use what you are most comfortable with. I started out using Dbase II, Dbase III, and Foxbase. I then moved on to using SQL with Oracle, SQL Server and other similar products, followed by MS Access.

The MS Access Design Mode can handle most Query building, but on occasion you must use SQL in order to get the job done. An example would be any time you need a UNION Query, which cannot be constructed in Design Mode.