Simplifying two queries into one help... 1

[iuianj07]

Hello guys,

I have a Job Tracking database wherein a record (a loan/work we do) in the database goes through a different phases/status to track status of the record (Loan received, In Process, Ready for Review, Sent to Client etc)

For a quick background here is my table structure (that relates to my question):

tblJobTracking:

SitusID -- PK
WeekNumber
DealName
AssetType -- FK to tblAssetType
PropertyCount
Etc (other fields)

tblDealStatus

DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst -- user who is assigned for the record/deal
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date
StatusHours -- how many hours did the user worked on that specific status

tblStatusChange

StatusChangeID -- PK
Status -- different status/phase (Unassigned, Received, In Process etc)

tblAssetType

AssetID -- PK
AssetType -- example (Retail, Office, Multi-Family, Self Storage


We are now trying to generate reports out of the database wherein we need to track how many deals (records) we received per Asset Type and how many total (sum)Properties per Asset Type...

I could create this report with how we need it although I am dumb enough that I always need to create 2 queries for this to happen. Which I am sure that there is a much simplified way to create this into a single query:

This is the queries I have:

qryAssetType

SELECT
 tblJobTracking.SitusID,
 tblAssetType.AssetType,
 tblJobTracking.PropertyCount,
 tblDealStatus.StatusDate,
 tblDealStatus.StatusChangeID
FROM
 (tblAssetType 
   INNER JOIN 
      tblJobTracking
   ON
      tblAssetType.AssetID = tblJobTracking.AssetType)
   INNER JOIN tblDealStatus
   ON tblJobTracking.SitusID = tblDealStatus.SitusID
WHERE
 (((tblDealStatus.StatusChangeID)=1));

I have the WHERE clause StatusChangeID = 1 (which means "Unassigned" in tblStatusChange) so that It'll only return one child record from tblDealStatus.


Then using the first query, I create another query:

qryTotalAssetsRcvd:

SELECT
 qryAssetType.AssetType,
 Count(qryAssetType.PropertyCount) AS Sizings,
 Sum(qryAssetType.PropertyCount) AS Properties
FROM
 qryAssetType
WHERE (((qryAssetType.StatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY qryAssetType.AssetType;

I have a WHERE clause in case the user would only want to see the numbers per week.


I know there is a much simpler way to create this query into one... and I would appreciate it if you could help me out as it will save me a lot of time, I have several more queries/reports that I need to create that has this pattern... (ex. per State, per Region, per Broker, per Originator) and in the only way I know how to create this query, it'll take 2 queries per report..

Thank you again,

 

[PHV]

Something like this ?

SELECT tblAssetType.AssetType, Count(*) AS Sizings, Sum(tblJobTracking.PropertyCount) AS Properties
FROM (tblAssetType 
INNER JOIN tblJobTracking ON tblAssetType.AssetID = tblJobTracking.AssetType)
INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID
WHERE tblDealStatus.StatusChangeID=1
AND tblDealStatus.StatusDate Between [Enter Start Date] And [Enter End Date]
GROUP BY tblAssetType.AssetType;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Thanks PHV!

I really don't know how I wasn't able to come up with this on my first attempt to create the query... I know I should have known how to do this approach but for some reason I always find myself taking the longer method on writing queries... =(

Thank you again