I have a query which pulls out and orders the Top 10 Most Frequently Occurring ATA2DIGITs in my dataset which fall in the criteria of 2 set dates. These dates are typically 2 months apart and on the first and last of the month e.g 1st March to 30th April (I'm mentioning this just in case it helps direct which solution is best to solve this). This query is working perfectly just how I want it and when I link this query into another query I can export the Top 10 Most Frequently Occuring ATA2DIGITs and the corresponding rows associated with them.
What I am also wanting to do is to extract the 6 month and 12 month data which corresponds with the Top 10 ATA2DIGITs set in the first query.
Currently my form has a Beginning Date text box and an End Date text box where I set the 2 month period.
I guess I could solve this issue by having another 4 boxes (2 for 6 month period and 2 for 12 month period) which would automatically calculate a date based on what is entered in the first couple of boxes and I would make these boxes invisible. I could then get my 6 and 12 month queries to use the data from these boxes.
Naturally, I am open to accepting other solutions which might be a bit more efficient.
Note: I may have explained it badly, but if for example the Top10ATA2DIGITs for 1st March to 30th April give (ATA2DIGITs of 28, 29, 15, 35, 75, 74, 70, 80, 25, 21) then the ATA2DIGITs I want my 6 and 12 month queries to search for will be those ones these are not necessarily the Top10 for the 6/12 month period, but are defined by what is the Top 10 in the 2 month.
Query 1 is as follows:
Query 2 is as follows:
What I am also wanting to do is to extract the 6 month and 12 month data which corresponds with the Top 10 ATA2DIGITs set in the first query.
Currently my form has a Beginning Date text box and an End Date text box where I set the 2 month period.
I guess I could solve this issue by having another 4 boxes (2 for 6 month period and 2 for 12 month period) which would automatically calculate a date based on what is entered in the first couple of boxes and I would make these boxes invisible. I could then get my 6 and 12 month queries to use the data from these boxes.
Naturally, I am open to accepting other solutions which might be a bit more efficient.
Note: I may have explained it badly, but if for example the Top10ATA2DIGITs for 1st March to 30th April give (ATA2DIGITs of 28, 29, 15, 35, 75, 74, 70, 80, 25, 21) then the ATA2DIGITs I want my 6 and 12 month queries to search for will be those ones these are not necessarily the Top10 for the 6/12 month period, but are defined by what is the Top 10 in the 2 month.
Query 1 is as follows:
Code:
SELECT DISTINCTROW TOP 10 TblTcDel.ATA2DIGIT, Count(*) AS [Count Of TblTcDel]
FROM TblAcrft INNER JOIN TblTcDel ON TblAcrft.Reg = TblTcDel.REG
WHERE (((TblTcDel.DATE)>=[Forms]![FrmRptCriteria]![Beg_Date_Txt] And (TblTcDel.DATE)<=[Forms]![FrmRptCriteria]![End_Date_txt]) AND ((TblAcrft.ModelLink)=2))
GROUP BY TblTcDel.ATA2DIGIT
ORDER BY Count(*) DESC;
Query 2 is as follows:
Code:
SELECT TblTcDel.ATA2DIGIT AS ATA, TblTcDel.Date, TblTcDel.REG AS Aircraft, TblTcDel.DelayTime AS [Delay Time], TblTcDel.[REASON FOR DELAY] AS [Reason For Delay], TblTcDel.[Rectification Information]
FROM TblAcrft INNER JOIN (TblTcDel INNER JOIN CountATA2DIGIT737300 ON TblTcDel.ATA2DIGIT = CountATA2DIGIT737300.ATA2DIGIT) ON TblAcrft.Reg = TblTcDel.REG
WHERE (((TblTcDel.Date)>=[Forms]![FrmRptCriteria]![Beg_Date_Txt] And (TblTcDel.Date)<=[Forms]![FrmRptCriteria]![End_Date_txt]) AND ((TblAcrft.ModelLink)=2))
ORDER BY CountATA2DIGIT737300.[Count Of TblTcDel] DESC , CountATA2DIGIT737300.ATA2DIGIT DESC , TblTcDel.Date;