Data from two fields in the same table using different criteria

[hkh]

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())));

[ssanfu]

I looked at part of the WHERE clause that deals with movement type.
Does this make sense:

([dbo_Movement_Headers].[Movement_Type]=3) AND 
([dbo_Movement_Headers].[Movement_Type]=4) AND 
([dbo_Movement_Headers].[Movement_Type]=4)

Can the value of [Movement_Type] be 3 AND 4 at the same time? No - so no records are returned
(also note that you have TWO "[Movement_Type]=4" in the criteria - totally unnecessary! )

That is like saying that Bob's (poor Bob) age is 20 AND 22. But you can say his age is 20 OR 22.

So you need an "OR" in the WHERE clause.
Something like:

WHERE ([dbo_Movement_Headers].[Movement_Type]=3 OR [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());

[hkh]

thank you for your time.

i tried the OR before, but iwill return only the Data with movement_type=3....thus only the returns......and then i will not get the requested and approved fileds.


the problem is that the three fileds (requested, approved , and returns) are in the same table,and same filed name, but with different movement type.

in othet words, filed named "quantity" contains the returns if the movement type is 3 and it contains requested if the movement type is 4.

i hope i made my self clear.

[ssanfu]

Yes, I understand. BUT, you cannot have criteria of

[dbo_Movement_Headers].[Movement_Type]=3) AND ([dbo_Movement_Headers].[Movement_Type]=4

and expect to get records returned!!
The field [Movement_Type] cannot contain a 3 and a 4 at the same time.

i tried the OR before,

OK, but did you try this query???????

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 OR [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());

Create a new query, switch to SQL view, paste in the above query, then execute it...

[orange]

hkh,

In 4 or 5 lines please tell us WHAT you are trying to do in simple terms.

Steve has given great advice concerning your Where clause use of AND.

You might try watching some free video tutorials by Dr.Daniel Soper that will help you with database concepts.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Good luck.