Query to pull out 2 month, 6 month and 12 month extracts based on a previous query (1 Viewer)

[Cark]

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:

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:

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;

 

[Ranman256]

you only need 2 boxes, txtStartDate and txtEndDate.
add a combo box for the user to pick: 2, 6, 12
this would set the txtEndDate.
txtEndDate = DateAdd("m", cboMo, txtStartDate)
The txtStartDate would come from your data, Dlookup(.....)

The query would use these boxes.
.....where [date] beteween forms!myForm!txtStartDate and forms!myForm!txtEndDate

 

[Cark]

I am needing to do the 2 month, 6 month and 12 month export all in one so is this doable with your method Ranman256? Effectively I would like to set the 2 month period and then the form will calculate the dates for 6 month and 12 month and then all 3 exports would be exported after clicking the export button.

 

[Cark]

I have tried to have a bash at coming up with a solution (don't think I am ignoring your input because I am doing it this way. I am doing it this way because it is just a way that I understand.) and have created 2 invisible text boxes which automatically update when I change my start date.

This means that when I come to doing my queries I have the following Dates to use:

BegDate (from this date to EndDate = 2 months)
BegDateSub4Months (from this date to EndDate = 6 months)
BegDateSub10Months (from this date to EndDate = 12 months)
EndDate

I have managed to write my queries so that it gives me the counts of the ATA2DIGITs which occur within those time periods (I just copied the original counting query and edited it slightly to use the ATA2DIGITs isolated in the first query and to use the dates using the respective BegDateSub....

One thing I would like to do would be to sort the order of the 6 month and 12 month queries so that they match the order provided in the 2 month query.

For example my first query sorted out the values for March and April and gave me the following ATA2DIGITs (the number of times they occurred is in the brackets):

34 (4), 28(4), 27(4), 21(4), 78(2), 29(2), 26(2), 25(2), 22(2), 20(2) as they were sorted by the count (the number of times they occurred in the 2 month period).

For the 6 month export, it is coming out as:

20 (3), 21(6), 22(4), 25(3), 26(3), 27(6), 28(10), 29(6), 34(10), 78(2).

I have checked these values with the date that is in the database, so I know this values are correct so I am at least able to export the correct data, I would just like to make it so that the 6 month export would have come out as:

34 (10), 28(10), 27(6), 21(6), 78(2), 29(6), 26(3), 25(3), 22(4), 20(3).

Note: In the 6 month export that I am hoping for, neither of the columns are in numerical order. The sorting I am wanting hopefully match the order in which the 2 month was given in.

 

[The_Doc_Man]

The problem is simple. You have 3 different selection criteria based on three different date ranges and should expect to use three different queries. Unless there is some crazy syntax out there, I know of no DIRECT method to do this because a query can have only one WHERE clause.

But there are always sneaky ways around that...

If you wanted to do this as a single query, it is possible to do it with layered queries.

You might play with the idea of doing three individual queries with whatever details you need to get each one to have the complete list of whatever you are seeking. Based on those three individual queries, tinker together a series of JOINs to take the 2 month, 6 month, and 12 month queries together as three differently named fields in the top-level JOIN even though they are from the same data source so normally would have the same names. For example,

SELECT x, y, q2month.z AS z2, q6month.z as z6, q12month.z as z12, etc etc
FROM ( ( qcommon INNER JOIN q2month ON q2month.x = qcommon.x) INNER JOIN q6month on q6month.x = qcommon.x ) etc. etc.

See discusson for qcommon properties, and this join string is shown for illustration only.

The tricky part will be the x or y field that you can use as the base of the JOIN, it will have to be something that would appear in all three queries. You have to have that commonality even if you have to synthesize something for the purpose because otherwise your join will not be quite right.

Here is the problem with sorting. Each query has its own sort order, but if you join the queries, only one sort order can apply in the outermost query. That is simply because just as a query can have only one WHERE clause, it can also have only one ORDER BY clause. This means that you have to do the sorting in the sub-queries and include some type of ordering index. In this forum, look up Ordinal Number as a topic. Then do not include an ORDER BY in the overarching query, but instead use the ordinal numbers as part of a compound JOIN criterion. So you need two extra columns - one with some common element for ALL records to make the JOIN work and one for the individual query ordinals to preserve the order of the sub-queries.

Heck, you might even build a "fake" table that contains your ordinals (1-10) and just update that table to have the common field in question. Then join the three subqueries to that fake table and go from there. A possible common field might be if you use the start date or end date (whichever is more appropriate) as the thing that would be common to all of the queries. I.e. if you are looking a the top 10 calls for periods ending in March, the common date might be March 31 and the start date for each subquery would be computed from the common date with a DateAdd or some other computation.

I know this is complicated. But then, you are looking to do something that isn't trivial. You should expect a speed bump or two.

 

[Cark]

Maybe I misdescribed my issues/what I am wanting to get out of the export.

Here is what I am currently able to export with the queries that I have set up:

• A list of the Top10 Most Commonly Occurring ATA2DIGITs for the 2 Month Period (e.g 34, 25, 49 +7more etc etc)
• A list of the 6 month period Numbers of the "2Month Period's Top10" (this is effectively using the ATA2DIGITs isolated in the 2 month and then finding how many times those values occur in a 6 month date range. e.g 34 occurs 21 times, 25 occurs 42 times and 49 occurs 8 times +7 more etc etc) Note: this list isn't sorted in the order which matches the first query. What I am wanting is for this 2nd query to be sorted in the same way as the first query so that if 34 is top of the list in the 2 month query, then 34 in the second query will be top of the list regardless of whether it occurs more or less frequency in the 6 month period than other values such as 25 and 49.

I am happy to have the exports come out as 3 separate workbooks or 3 separate worksheets in one workbook, the main issue I want resolved, is to have my 6 month and 12 month queries, come out in a sorted fashion which matches the layout for the 2 month query. This is because I will need to take the data and put the 2 month 6 month and 12 month columns next to each other in a table to be presented for a report. This table needs to be presented with the list sorted by ATA2DIGITCount for 2 month period, then ATA2DIGITCount for 6 month period and then ATA2DIGITCount for 12 month period.

 

[Cark]

Attached is the kind of table I would like to be able to produce. If it isn't quite possible to do this all in one query / export, then I am happy for them to be done in separate ones as long as the 6 and 12 month ones are sorted as per the 2 month export to save me having to faff about with creating formulae in spreadsheets for when they get imported.

I currently have 3 individual queries set up so I am at least able to extract the data required to compile the table, but the sorting/ordering format is not how I would like it.

Whilst waiting for you guys to reply I will have an attempt myself to merge these queries together although I have a feeling with my little knowledge of Access I will probably end up barking up the wrong tree.

 

[The_Doc_Man]

So let me say this another way. Based on the ordering determined for only the 2-month caller frequency, you also want to see 6-month and 12-month counts in the same record regardless of the ordering of counts in that record to determine if you were looking at a spike or a longer-term trend.

This still requires three summation queries for the three periods of consideration but in this case you could use a JOIN query to regroup things based on the 2-month query. My prior discussion changes only a little. The difference will be in the JOINs. You will still be using INNER JOIN because numbers that occur in the 2-month period will of mathematical necessity also appear in any overlapping periods. So that means your ATA2DIGIT combinations will appear in all three queries. And that is why INNER JOIN will work.

The only major differences are that the fields we are talking about to be the linkage will be the ATA2DIGIT fields and the 6-month and 12-month queries don't need to have sorting or "TOP" constraints.

First query, for this call it Q2M, you have something like I show below, and see after the code snippets for a discussion on dates.

SELECT TOP 10 ATA2DIGIT, COUNT( ATA2DIGIT ) AS F2 FROM sourcetablename 
WHERE CALLDATE BETWEEN start2date AND end2date GROUP BY ATA2DIGIT;

For Q6M you have

SELECT ATA2DIGIT, COUNT ( ATA2DIGIT ) AS F6 FROM sourcetablename
WHERE CALLDATE BETWEEN start6date AND end6date GROUP BY ATA2DIGIT ;

For Q12M you have

SELECT ATA2DIGIT, COUNT( ATA2DIGIT ) AS F12 FROM sourcetablename
WHERE CALLDATE BETWEEN start12date AND end12date GROUP BY ATA2DIGIT ;

Now you can do something like this:

SELECT Q2M.ATA2DIGIT, Q2M.F2, Q6M.F6, Q12M.F12 
FROM ( Q2M INNER JOIN Q6M ON Q2M.ATA2DIGIT = Q6M.ATA2DIGIT ) 
           INNER JOIN Q12M ON Q2M.ATA2DIGIT = Q12M.ATA2DIGIT
ORDER BY Q2M.F2 ;

A note is in order on the start and end dates. I am not clear on whether it is the starting date or the ending date that must be the same for each sub-query. Therefore I left it ambiguous. You will have to decide what you want for start/end dates, but let's say that you want everything to start from the same date and that it will always be the first of the month. You can then use the same date for the Q2M, Q6M, and Q12M start date, and for the end dates something like DATEADD( "m", 2, startdate ) or DATEADD( "m", 6, startdate ) or DATEADD( "y", 1, startdate ) for the end dates.

If you want different dates than are returned from DATEADD, you can compute the dates in variables and make the QxM queries become parameter queries where you supply the parameters when running the queries. Note that the final query that joins the whole mess together does NOT require parameters, but the child queries might.