Nested queries...

[Kpatel]

I am not too good with queries. I have two queries that I would like to
combine into one. Here are the two individual queries:

Query 1:
SELECT [Claims Details].Job AS BOL, [Claims Details].[Customer/Account Name]
AS [Customer Name], Min([Claims Details].[Claim Open Date]) AS [Claim Date],
DateSerial(Year([Claim Date]),Month([Claim Date])+1,0) AS EOMonth
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Date]) Is Null) AND (([Claims
Details].[Claim Status])<>"Cancelled"))
GROUP BY [Claims Details].Job, [Claims Details].[Customer/Account Name]
ORDER BY [Claims Details].Job;

Query 2:
SELECT Count(UniqueJob.BOL) AS [No Of Moves], UniqueJob.Customer,
DateSerial(Year([Delivery Date]),Month([Delivery Date])+1,0) AS [Move Period]
FROM [SELECT DISTINCT [SFM Data].BOL, [SFM Data].Customer, [SFM
Data].[Delivery Date] FROM [SFM Data] WHERE not([Delivery Date] is null)
ORDER BY [SFM Data].BOL]. AS UniqueJob
GROUP BY UniqueJob.Customer, DateSerial(Year([Delivery
Date]),Month([Delivery Date])+1,0);

I tried combining the two as follows and get a syntax error:

SELECT [Claims Details].Job AS BOL, [Claims Details].[Customer/Account Name]
AS [Customer Name], Min([Claims Details].[Claim Open Date]) AS [Claim Date],
(SELECT Count(UniqueJob.BOL) AS [No Of Moves]
FROM (SELECT DISTINCT [SFM Data].BOL, [SFM Data].Customer, [SFM
Data].[Delivery Date] FROM [SFM Data] WHERE not([Delivery Date] is null)) AS
UniqueJob
GROUP BY UniqueJob.Customer, DateSerial(Year([Delivery
Date]),Month([Delivery Date])+1,0)
HAVING (((UniqueJob.Customer)=[Claims Details].[Customer/Account Name]) AND
((DateSerial(Year([Delivery Date]),Month([Delivery
Date])+1,0))=DateSerial(Year(Min([Claim Open Date])),Month(Min([Claim Open
Date]))+1,0)))) AS [Total Moves], DateSerial(Year([Claim Date]),Month([Claim
Date])+1,0) AS EOMonth
FROM [Claims Details]
WHERE ((Not ([Claims Details].[Check Request Date]) Is Null) AND (([Claims
Details].[Claim Status])<>"Cancelled"))
GROUP BY [Claims Details].Job, [Claims Details].[Customer/Account Name],
(SELECT Count(UniqueJob.BOL) AS [No Of Moves]
FROM (SELECT DISTINCT [SFM Data].BOL, [SFM Data].Customer, [SFM
Data].[Delivery Date] FROM [SFM Data] WHERE not([Delivery Date] is null)) AS
UniqueJob
GROUP BY UniqueJob.Customer, DateSerial(Year([Delivery
Date]),Month([Delivery Date])+1,0)
HAVING (((UniqueJob.Customer)=[Claims Details].[Customer/Account Name]) AND
((DateSerial(Year([Delivery Date]),Month([Delivery
Date])+1,0))=DateSerial(Year(Min([Claim Open Date])),Month(Min([Claim Open
Date]))+1,0))))
ORDER BY [Claims Details].Job;

I intend to put Query 2 into Query 1 such that the [No Of Moves] from query
2 is the output where [Query 1].[Customer Name]=[Query 2].[Customer] and
[Query 1].[EOMonth] = [Query 2].[Move Period].

I hope I am being clear. Do let me know if any further details are
required. I have been struggling with this for days. Any help will be
appreciated.

Thanks