An Access problem: Not getting required output while building a report

I have created a report in Access and I have written a query for fetching records from multiple tables as follows:

SELECT BuildingDetails.*, Contractors.Item, ActionDetails.ActionType
FROM Contractors 
INNER JOIN (BuildingDetails 
    INNER JOIN (ActionDetails 
        INNER JOIN DormData ON ActionDetails.ActionID = DormData.ActionID) 
    ON BuildingDetails.BuildingID = DormData.BuildingID) 
ON Contractors.ID = DormData.ItemID;

Now what I want is only actiontype=repair or actionid=1 get retrieved by the query. We have two actontype "repair" 开发者_JAVA百科and "replace".

I have reformatted you query a little to neaten it up. You haven't specified what the data looks like for the filter but based on what you have said I would go with something like the following

SELECT BuildingDetails.*, 
Contractors.Item, 
ActionDetails.ActionType 
FROM Contractors 
INNER JOIN DormData ON Contractors.ID = DormData.ItemID
INNER JOIN ActionDetails ON DormData.ActionID = ActionDetails.ActionID
INNER JOIN BuildingDetails ON DormData.BuildingID = BuildingDetails.BuildingID
WHERE ActionDetails.ActionType = 'Repair' OR ActionID=1

If ActionID is a lookup column that relates ActionID(1) to ActionType ('Repair') then you don't need the or and can stick to one or other of the conditions in the WHERE Clause.

Hope this helps.

I suspect you only need to filter using actiontype = 'repair' (I further guess that ActionID is an autonumber and you have a row {ActionID = 1, actiontype = 'repair'} only by chance... but this is maybe extrapolating too far :)

I'm surprised @David Steele's answer works in Access (ACE, Jet, whatever) because he's removed the parentheses from the JOIN clauses (however if it does -- suggesting a linked table -- then you should "accept" that answer). But I too could resist 'neatening them up' so that the ON clauses are close to the table names:

SELECT BuildingDetails.*, Contractors.Item, ActionDetails.ActionType
  FROM ((DormData 
         INNER JOIN Contractors 
            ON Contractors.ID = DormData.ItemID)
         INNER JOIN BuildingDetails 
            ON BuildingDetails.BuildingID = DormData.BuildingID)
         INNER JOIN ActionDetails
            ON ActionDetails.ActionID = DormData.ActionID
 WHERE ActionDetails.ActionType = 'repair';

Add this to the end of your select statement to fix the issue:

where actiondetails.actiontype = 'repair' or actiondetails.actionid = 1