Count in a Query 2

[FoxProProgrammer]

Given the following Query:

SELECT qry_convert_allJobs.*, qry_convert_parts_replaced.Part_Name, qry_convert_parts_replaced.PartSN
FROM qry_convert_allJobs LEFT JOIN qry_convert_parts_replaced ON qry_convert_allJobs.JobNum = qry_convert_parts_replaced.JobNum;

Here is each Query by itself:

qry_convert_allJobs

SELECT tbl_Jobs.JobNum, tbl_Jobs.Date_Completed, tbl_LRU.LRU_Name, tbl_Jobs.LRU_SN, tbl_Jobs.Comments, tbl_Jobs.NFF, tbl_Jobs.Customer, tbl_aircraft.aircraftType
FROM tbl_LRU INNER JOIN (tbl_aircraft RIGHT JOIN tbl_Jobs ON tbl_aircraft.aircraftID = tbl_Jobs.aircraftID) ON tbl_LRU.LRUID = tbl_Jobs.LRU_Repair;

qry_convert_parts_replaced

SELECT tbl_Jobs.JobNum, tbl_LRU.LRU_Name, tbl_Parts.Part_Name, tbl_Parts_Replaced.PartSN
FROM tbl_Parts INNER JOIN ((tbl_LRU INNER JOIN tbl_Jobs ON tbl_LRU.LRUID = tbl_Jobs.LRU_Repair) INNER JOIN tbl_Parts_Replaced ON tbl_Jobs.JobID = tbl_Parts_Replaced.JobID) ON tbl_Parts.PartID = tbl_Parts_Replaced.PartID
ORDER BY tbl_Jobs.JobNum;

Is there a way to count the following in the combined Query?

1. Number of records that contain a unique JobNum.
2. Number of parts replaced on each Job.
3. Total number of parts replaced on all Jobs.

Thanks!

dz

 

[lameid]

No, counting is an aggregate function and only works with grouping.

So to count something by Job, you must group by job and not show the detail.

However, you could base a report off the query, show the detail and use aggregate functions in the footers to obtain the last 2 items.

I think you can get the first item in a report by adding a literal 1 to your select and then doing a Running sum on that value in the Job header of footer. Then you just reference that control in the report footer and you will have it. If that doesn't work, it can be achieved in code.

The other way to go is to write 3 separate queries to get the three separate answers.

qry_convert_allJobs more or less answers the first... the record count is the unique job or you could modify it to count for its individual query.

The way to go ultimately depends on what you are trying to accomplish.

A poor performing approach would be to create the summary queries and include them and your final query together to get the results. I like the report idea but may not fit your solution.

 

[Remou]

It is possible to do quite a bit with sub queries, however, it seems likely that you will need to re-think your final query as lameid suggests. Do not forget that it is possible to join a grouped query to a detail query.

You appear to have two different IDs for the Jobs table, these are JobNum and JobID:

[tt]SELECT tbl_Jobs.JobNum, tbl_LRU.LRU_Name, tbl_Parts.Part_Name, tbl_Parts_Replaced.PartSN
FROM tbl_Parts INNER JOIN ((tbl_LRU INNER JOIN tbl_Jobs ON tbl_LRU.LRUID = tbl_Jobs.LRU_Repair) INNER JOIN tbl_Parts_Replaced ON [red]tbl_Jobs.JobID[/red] = tbl_Parts_Replaced.JobID) ON tbl_Parts.PartID = tbl_Parts_Replaced.PartID
ORDER BY [red]tbl_Jobs.JobNum[/red];[/tt]


Here is an example of using a sub query in the final query above:

[tt]SELECT qry_convert_allJobs.*, qry_convert_parts_replaced.Part_Name, qry_convert_parts_replaced.PartSN, (Select Count(*) From tbl_Parts_Replaced Where JobID=qry_convert_allJobs.JobID) AS Expr1
FROM qry_convert_allJobs LEFT JOIN qry_convert_parts_replaced ON qry_convert_allJobs.JobNum = qry_convert_parts_replaced.JobNum;[/tt]

 

[FoxProProgrammer]

Thanks to both of you. I combined two databases into one, and had to make major changes to the structure of one of them to make it work. After I got the program working, I had to move the data from the original databases into the new structure. It was a pretty big chore considering all the keys and related tables. The purpose of the query in this thread was to make sure that I didn't lose any records during the conversion process. I verified that I didn't lose any data by using ideas from both of you.

By the way, JobID and JobNum are not both IDs. JobID is the primary key (Autonumber) and JobNum is a work order number (Text).

Thanks!

dz