Using table entries for a criteria in a query

[DubCap01]

Hi all,



Not sure how to go about this, but I have an append query [QryResults] that executes and selects records for a whole month, based on the first day of any selected month. I have a table [TblDateStamps] with all the 'first' days of each month going back to May 2016. I want to be able to run the query for every month after a selected first day of any month in [CboMonth].

So, if I first select January 2017 in CboMonth, I would like the query to run four times (for Jan-17, Feb-17, Mar-17 and Apr-17 - May-17 is not yet in TblDatestamps table). If anyone can suggest a good approach to this, I'd appreciate some help with what code to use.

Happy to provide any other information if it is required

thanks in anticipation
Pete

[CJ_London]

how about providing the query you are using at the moment for one month

[DubCap01]

SELECT TblTmpNewEpisodes.SOURCE, TblTmpNewEpisodes.AgencyCode, TblTmpNewEpisodes.AgencyLocation, Count(TblTmpNewEpisodes.EpisodeID) AS Episodes, TblDateStamps.MonthName, TblDateStamps.YearMonth, TblDateStamps.FinYear, AgencyList.ServiceType
FROM TblDateStamps, TblTmpNewEpisodes INNER JOIN AgencyList ON TblTmpNewEpisodes.AgencyCode = AgencyList.AgencyCode
GROUP BY TblTmpNewEpisodes.SOURCE, TblTmpNewEpisodes.AgencyCode, TblTmpNewEpisodes.AgencyLocation, TblDateStamps.MonthName, TblDateStamps.YearMonth, TblDateStamps.FinYear, AgencyList.ServiceType, TblDateStamps.DateStamp
HAVING (((TblDateStamps.DateStamp)=[forms]![FrmRptDialog1]![StartDate]));

[DubCap01]

sorry Ajax, I think I should elaborate here..
After selecting the 'Month' from CboDateRange, two text fields are updated [StartDate] and [EndDate].
Then, this code runs when I click a command button...

        DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete TblTmpNewEpisodes.* from TblTmpNewEpisodes;"
        DoCmd.RunSQL "SELECT TblEpisodes.SOURCE, TblEpisodes.AgencyCode, TblEpisodes.AgencyLocation, TblEpisodes.EpisodeID, TblEpisodes.Commencement INTO TblTmpNewEpisodes FROM TblEpisodes WHERE (((TblEpisodes.Commencement)>=[forms]![FrmRptDialog1]![StartDate] And (TblEpisodes.Commencement)<=[Forms]![FrmRptDialog1]![EndDate]));"
        DoCmd.OpenQuery "QryNEWEPISODES_Crosstab", acViewNormal
        DoCmd.SetWarnings True

The code I sent before is just for "QryNEWEPISODES_CrossTab"
I can get everything automated except the update of CboDateRange to each 'first day' of subsequent months after January, so it runs again

Gets more complicated every time I reply haha

cheers
Pete

[CJ_London]

why does it need to run multiple times? why not just the once for all relevant months? I presume you don't want to delete the table between each iteration?

[DubCap01]

as it is, if I run it between 1 Jan 17 and 30 Apr 17 I get a 'four month' sum total situation. I have to have a month-by-month sum total for KPI comparisons and trends

[DubCap01]

It has to be this way, as the data is then exported to Microsoft Excel and onwards. A complex crosstab doesn't provide for the layout needed in MS Excel - besides which, I don't know if Access could produce a table with individual results for the months for all the parameters?

[CJ_London]

getting confused by the requirement. you delete a table contents, populate with what - a single record, multiple records? then open a query then delete the table? I see no export to excel, and cannot comment on the lay

Perhaps explain in simple English (no technical terms) what you are trying to do

[DubCap01]

At the moment, I am selecting the month from the combo box. Then I am running the code behind the command button which populates a (now empty) temporary table with ALL the new records for that month. Then I am running a crosstab query which counts the particular instances by agency and produces totals. I then copy these totals and paste into Excel.
I then repeat the whole process for the next month and paste into Excel below the ones I just pasted! And so on...

I just wanted to get the process to run in this format:

Delete the contents of the temporary table.
Select the first month for which to collect records (Jan-17) via CboDateRange
Run the append query and populate the temporary table with all records matching the append query criteria
[code] SELECT the next month in the sequence (Feb-17)
[code] Run the append query and populate the temporary table with all records matching the append query criteria
[code] SELECT the next month in the sequence (Mar-17)
[code] Run the append query and populate the temporary table with all records matching the append query criteria
[code] STOP running the append query when it has completed for the last month (pre-defined in TblDateRange - Apr-17)

Once it has run the append query for the number of months since the first date selected (in this case four months), I would then open the temporary table - containing four months of data - copy all and paste as one entity into Excel.

I can sort out the bits around the repetition of the running of the query, it's the code to CHANGE the value in CboDateRange to the next month and then repeat the query to append that I cannot work out.

Does that make more sense?

cheers
Pete

[DubCap01]

I can see where I am causing confusion here.


What I do now... :


a. run an append query finding all new episodes commencing between 1-Jan and 31-Jan.
b. populate a temporary table with the results. In simplistic terms [TblTemp1]
c. then run a crosstab query over TblTemp1 to view the totals
d. copy the records and paste into Excel
e. change CboDateRange to Feb-17 and repeat the process


example for Jan-17:
61 records appended to TblTemp1, for four agencies (Agency1, Agency 2, Agency 3 and Agency 4)
My crosstab query produces the following totals:
Agency1 12 Jan-17
Agency2 11 Jan-17
Agency3 14 Jan-17
Agency4 24 Jan-17
(12 + 11 + 14 + 24 = 61)


Probably what should happen...
What I do now - but only stages a, b and c (as above)
I should then append the crosstab totals to another (empty) temporary table. In simplistic terms [TblTemp2]
I should then delete all rows in the FIRST temporary table and repeat the whole process for Feb-17, Mar-17 and Apr-17.
Once the crosstab totals have been appended to TblTemp2 for Apr-17, the process stops and TblTemp2 is opened so I can then copy the whole table and paste into Excel


cheers
Pete

[CJ_London]

so your cross tab looks like

Month....Agency1...Agency2...Agency3...Agency4
Jan-17......12............11.........14.............24

is it the same 4 agencies for each month? Just trying to understand why you can't have

Month....Agency1...Agency2...Agency3...Agency4
Jan-17......12............11.........14.............24
Feb-17......15............10.........12.............18
etc

[DubCap01]

ermm that's exactly what I want... and it is the same agencies every month

cheers
Pete

[CJ_London]

in that case, all you need to do is modify your query to not use the criteria <=enddate

[DubCap01]

removing the <=enddate simply adds every record per agency and gives one total, and not monthly totals.
Don't worry, I am going to attack this from another perspective, but many thanks for your input and knowledge Ajax
closing the thread,
cheers
Pete

[CJ_London]

removing the <=enddate simply adds every record per agency and gives one total, and not monthly totals

it should do if your crosstab has a row heading for the month column per the example I suggested you were seeking