I'm working in inventory management project in one of the form

I want to populate Specified data from the tables my tables are with a relation

The problem that I'm facing is that after choosing the dataset which is my Transport table, It's only populate the the data inside that table therefor, I create a query to populate the data based on the relations inside the table the query is

SELECT Transport.ID, Items.Item, Transport.Quantity, Departments.Department, Buildings.Building
FROM Transport 
INNER JOIN Buildings ON Transport.BuildingID = Buildings.ID 
INNER JOIN Departments ON Transport.DepID = Departments.ID 
INNER JOIN Items ON Transport.ItemID = Items.ID

created with help of Query Builder and executed well, when added to The Search Criteria Builder it shows an error

The schema returned by the new query differs from the base query 

My tables :

[dbo].[Items] (
    [ID]        
    [Item]       
    [Supplier]   
    [Category]  
    [Quntity]    
    [Price]     
    [TotalPrice] 
    [AddedDate] 
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

[dbo].[Buildings] (
    [ID]     
    [Building]
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

[dbo].[Departments] (
    [ID]
    [Department]
    PRIMARY KEY CLUSTERED ([ID] ASC)
);

[dbo].[Transport] (
    [ID]         
    [ItemID]     
    [DepID]      
    [BuildingID] 
    [Quantity]   
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [Transport_Items] FOREIGN KEY ([ItemID]) REFERENCES [dbo].[Items] ([ID]),
    CONSTRAINT [Transport_Dep] FOREIGN KEY ([DepID]) REFERENCES [dbo].[Departments] ([ID]),
    CONSTRAINT [Building_Relation] FOREIGN KEY ([BuildingID]) REFERENCES [dbo].[Buildings] ([ID])
);

The reason behind import from dataset not populate using data Table cause it is easier to add search and filter when the data comes from dataset

CodePudding user response：