Link to home
Start Free TrialLog in
Avatar of Gary Croxford
Gary CroxfordFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Combine Two Queries Into One

Thank you for looking at my question,

I have a query  in MS Access 2013 that I know contains more than one contract against item

qryContracts_Current
Select Item, Contract, StartDate, EndDate, Value
From tblContracts
Where EndDate > Now();

To find the multiple contracts I did a count of the instances of each item no ...

qryContracts_Multiple
Select Item, Count of Item
From qryContracts_Current
Group By qryContracts_Current.Item;
Where Count of Item>1;

... and then re-queried the original query vs the multiples query

qryContracts_Multi_Detail
Select qryContracts_Multiple.Item, qryContracts_Current.*
FROM qryContracts_Current_Multiple INNER JOIN qryContracts_Current ON qryContracts_Current_Multiple.Item = qryContracts_Current.Item;


It strikes me that, if I were a little more experienced/sophisticated I should be able to achieve the same result with one query or at most two.

Could somebody show me how to do this more efficiently please?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gary Croxford

ASKER

Excellent, Thank you