Hello,
i am new to access.
i have linked the access to a database (sql 2008) and i am trying to extract data from sql to be shown in access 2010.
i have two tables in SQL : MOVEMENT HEADERS and MOVEMENT DETAILS.
The movement details contains the fields : REQUESTED , APPROVED, and RETURNS.
The REQUESTED and the APPROVED both have a movevment type = 4 in the MOVEMENT HEADER.
I can use a query to extract the data easily using the criteria MOVEMENT TYP=4.
but the RETURNS have the criteria MOVEMENT TYPE =3.
When i try to add the RETURNS filed to the query and using a criteria MOVEMENT TYPE=3 under it, the result is empty page.
the sql statement of the query containing the REQUESTED and APPROVED (having the same movement type ...working fine)
SELECT dbo_Users.User_Code, dbo_Users.User_Description, dbo_Movement_Details.Item_Code, dbo_Movement_Details.Item_Description, dbo_Movement_Headers.Stamp_Date, dbo_Movement_Details.Quantity_SU AS Requested, dbo_Movement_Details.Quantity_Level3 AS Approved, IIf([dbo_Movement_Details].[Approve_User_Code]=102,"Approved","Not Approved") AS [Approved/Not Approved], dbo_Movement_Headers.Movement_Type
FROM dbo_Users INNER JOIN (dbo_Movement_Details INNER JOIN dbo_Movement_Headers ON dbo_Movement_Details.Movement_Code = dbo_Movement_Headers.Movement_Code) ON dbo_Users.User_Code = dbo_Movement_Headers.User_Code
WHERE (((dbo_Movement_Headers.Movement_Type)=4) AND ((DatePart("ww",[dbo_Movement_Details].[Stamp_Date]))=DatePart("ww",Date())) AND ((Year([dbo_Movement_Details].[Stamp_Date]))=Year(Date())));
the sql statement of the query when i try to add the RETURNS filed with diffirent criteria (movement type=3...no data is shown)
SELECT dbo_Users.User_Code, dbo_Users.User_Description, dbo_Movement_Details.Item_Code, dbo_Movement_Details.Item_Description, dbo_Movement_Headers.Stamp_Date, dbo_Movement_Details.Quantity_Level3 AS Returns, dbo_Movement_Details.Quantity_SU AS Requested, dbo_Movement_Details.Quantity_Level3 AS Approved, IIf([dbo_Movement_Details].[Approve_User_Code]=102,"Approved","Not Approved") AS [Approved/Not Approved]
FROM dbo_Users INNER JOIN (dbo_Movement_Details INNER JOIN dbo_Movement_Headers ON dbo_Movement_Details.Movement_Code = dbo_Movement_Headers.Movement_Code) ON dbo_Users.User_Code = dbo_Movement_Headers.User_Code
WHERE (([dbo_Movement_Headers].[Movement_Type]=3) AND ([dbo_Movement_Headers].[Movement_Type]=4) AND ([dbo_Movement_Headers].[Movement_Type]=4) AND ((DatePart("ww",[dbo_Movement_Details].[Stamp_Date]))=DatePart("ww",Date())) AND ((Year([dbo_Movement_Details].[Stamp_Date]))=Year(Date())));