minimize repeating query criteria & generating monthly YTD totals

I have 5 queries. One for each month. There are # things I want to do here.

Consolidate to a single query. currently the "Query is too complex" message 
appears.
Simplify my code. I'm only changing the date range in my criteria, but 
repeating the 
Aggregate a YTD column for Count, Sum, Avg Completion Time fields within the 
same record.
Please keep in mind that my query must start with December 2009 to current.

"Location" Mandatory field for all tables

SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS 
[Avg Completion Time], "Table_A April 2010" AS MyTable
FROM [Table_A]
WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query 
April 2010" AS MyTable
FROM [Table_B 50k or More Query]
WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less 
than 100k Query April 2010" AS MyTable
FROM [Table_B Less than 50k]
WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS 
Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April 2010" AS 
MyTable
FROM [Table_C]
WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
GROUP BY Region
..
..
..
UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, 
"" AS [Avg Completion Time], "Table_A April YTD 2010" AS MyTable
FROM [Table_A]
WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query 
April YTD 2010" AS MyTable
FROM [Table_B 50k or More Query]
WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less 
than 100k Query April YTD 2010" AS MyTable
FROM [Table_B Less than 50k]
WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
GROUP BY Location
UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS 
Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April YTD 
2010" AS MyTable
FROM [Table_C]
WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
GROUP BY Region;
0
Utf
5/11/2010 5:07:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
886 Views

Similar Articles

[PageSpeed] 17

I slipped up a bit in my labeling. The 100k should be 50k.

"Liv" wrote:

> I have 5 queries. One for each month. There are # things I want to do here.
> 
> Consolidate to a single query. currently the "Query is too complex" message 
> appears.
> Simplify my code. I'm only changing the date range in my criteria, but 
> repeating the 
> Aggregate a YTD column for Count, Sum, Avg Completion Time fields within the 
> same record.
> Please keep in mind that my query must start with December 2009 to current.
> 
> "Location" Mandatory field for all tables
> 
> SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS 
> [Avg Completion Time], "Table_A April 2010" AS MyTable
> FROM [Table_A]
> WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
> Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query 
> April 2010" AS MyTable
> FROM [Table_B 50k or More Query]
> WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
> Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less 
> than 100k Query April 2010" AS MyTable
> FROM [Table_B Less than 50k]
> WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS 
> Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April 2010" AS 
> MyTable
> FROM [Table_C]
> WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010#
> GROUP BY Region
> .
> .
> .
> UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, 
> "" AS [Avg Completion Time], "Table_A April YTD 2010" AS MyTable
> FROM [Table_A]
> WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
> Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query 
> April YTD 2010" AS MyTable
> FROM [Table_B 50k or More Query]
> WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, 
> Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less 
> than 100k Query April YTD 2010" AS MyTable
> FROM [Table_B Less than 50k]
> WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
> GROUP BY Location
> UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS 
> Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April YTD 
> 2010" AS MyTable
> FROM [Table_C]
> WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010#
> GROUP BY Region;
0
Utf
5/11/2010 5:30:01 PM
Reply:

Similar Artilces:

How do I minimize to the trey
I have a requirements to put an icon of my app in the trey when it is minimized. How would I do this. 1. From a console app. 2. From an MFC dialog app? Thanks, Yes, an MFC dialog app. All Dialog apps, if I remember correctly, come with an App.ico file. You can draw on this or replace it with your own. If you replace it, then you may need to find how it links with the code and make sure your file is used instead. Not sure if you can override the icon of a console window. >-----Original Message----- >I have a requirements to put an icon of my app in the trey when it is >mini...

Remove Meeting Time from Monthly Calendar Print
Is it possible to remove the meeting time from a monthly calendar print and only show the meeting title? Yes, you need to use portrait mode so the cells are too narrow to include the time. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Do you sync your mailbox with a smartphone or pd...

Minimized Outlook
I was glad to discover that I could minimize Outlook to an icon at the right of my task bar instead of to the standard big minimize bar. At least, until I found out that half of the time when I click on it to expand it, I get a ! message telling me that Outlook is not responding. I suppose that's a function of my Exchange server. But having to click again slows things down. Is there a way to disable this message and maximize Outlook quickly? Howard Brazee <howard@brazee.net> wrote: > I was glad to discover that I could minimize Outlook to an icon at > the right of my ta...

When does a query/subquery return a NULL and when no value at all, not even a NULL?
hi a) If a query/subquery doesn=92t find any matching rows, then it either returns NULL or no value at all, thus not even a NULL value. Based on what criteria does a query or a subquery return a NULL and when doesn=92t it return any results, not even a NULL value? b) I assume a scalar subquery will always return NULL, when no matching rows are found? I assume most-outer scalar query also returns NULL if no rows are found? c) SELECT FirstName, LastName, YEAR(BirthDate) FROM Persons WHERE YEAR(BirthDate) IN (SELECT YearReleased FROM Albums); * If subquery finds no results...

Dynamic Chart Generation
I am trying to use VBA to dynamically produce a number of charts. Th number of charts produced depends on the amount of data that appears i a seperate spreadsheet... I have taken the code produced when I recorded a macro to produce on such chart and adapted it to meet my needs, however I am getting erro 1004, "Unable to set the XValues property of the Series class" when try to assign the x-axis values using a range I have dynamicall created. I'm sure the problem is somewhere with my syntax, but however I try t phrase it I cannot seem to get it to work. Please find a copy o...

Multiple lines of criteria without repeating header
Hi I'm using the database functions DMAX etc and want to have a variety o criteria in a column. However I believe the "criteria" range has to b a minimum of 2 rows with the top row containing headers. If I want t reference a row of the range of criteria, I am then lacking th headers. Including the headers would then include all rows of criteri in between too - creating an undesirable OR condition. Eg: ----B--------C--------D 2--Angle--Angle---Max 3-->=1----<3-----=DMAX(dbase,2,B2:C3) 4-->=3----<5-----=DMAX(dbase,2,B2:C4) 5-->=5----<6-----=DMAX(dbase,2,B2:C5) 6...

Difficult One-to-Many Query
I have (2) back-end SQL Server tables that I've linked to a front-end ACCESS db. One table contains Overall (summarized) data associated with a Product Order, and the other contains Specific data associated with the order. Both tables have Customer Number as a Primary Key -- here's the structures of the (2) tables, as well as what I'm trying to do with the data: Table 1 Overall Data. (No Primary Key) FIELDS: IndexNumber CustomerNumber OrderStatus OrderDate CompanyName SalesRep TotalSaleRevenue Table 2 Specific Data. (No Primary Key) FIELDS: IndexNumber CustomerNumber O...

Problems Query with MSQuery
Hell I try to Query from CSV file to Excel worksheet using MSQuery One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data What can i do to fix the problem The following article on Dick Kusleika's weblog may help (from a post by onedaywhen): http://www.dicks-blog.com/excel/2004/06/external_data_m.html#trackback IZI wrote: > Hello > > I try to Query from CSV file to Excel worksheet using MSQuery. > > One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data. > > ...

Complex (for me) IIf query
I have tblEmployeeProduction which is populated via a form using an Update query with the following sql: PARAMETERS forms![frmSetEmpHours]![txtDate] DateTime; INSERT INTO EmployeeProduction ( EmployeeID, ProductionDate, Department, Shift, JobFunctionID, ShiftHours ) SELECT Employees.EmployeeID, forms!frmSetEmpHours!txtDate AS ProductionDate, Employees.Department, Employees.Shift, JobFunctionID, Shift.ShiftHours FROM Shift INNER JOIN Employees ON Shift.Shift=Employees.Shift WHERE (((Employees.Department)=forms!frmSetEmpHours!cboDept) And ((Employees.Shift)=forms!frmSetEmpHours!cboShift)); Thi...

CRM Queries #2
Hi There, I am new to MS CRM. I will highly appriciate if anyone could answer my below queries. · 1) Why My Work in CRM showing All personal & official Emails - How & from where should disable this emails. as i want to see only CRM Cases instead emails. · 2) Where can i explore & see more information about CRM General Tab & Details tab of MS CRM · 3) What should i do to access my MS CRM from outside of company (Web Interface accessibility). · 4) How should i Add columns in KB Article's in Published section....

Minimized form
Hi, I have a big issue with Acces 2007,whne i open a form,the window is always minimized,i try to set the property "Auto resize" to yes,but when i open the form it's minimized as usual and displays one record only unless users scroll down the bar to see the records one by one... Is there any solution for this? What I did ( Access 2003 though ) was to put a macro with the command Maximize in the form onload event "Pietro" wrote: > Hi, > I have a big issue with Acces 2007,whne i open a form,the window is > always minimized,i try to set the proper...

Month-End Closing in Great Plains 8.0
Might I get some information about Month-End Closing? The detailed steps I should take The precautions and cautions I shoud aware of. Thanks, Alexis Hello Alexis, There are so many factors that goes into this depending on which modules you have, but generally speaking, Great Plains doesn't have a "hard" month-end as with many other ERP solutions. Perhaps contact your VAR to help you determine what procedures you need to perform as part of your month-end. Kind Regards Eddie Fourie MBS Specialist A> Might I get some information about Month-End Closing? A> A> Th...

why does my parameter query repeat the prompt?
I have a query with three parameters. The query asks for each parameter and then repeats itself before returning the data. Probably because the query is being run more than once -- perchance, is this query the RecordSource of a subreport or subform? -- Ken Snell <MS ACCESS MVP> "nickaf" <nickaf@discussions.microsoft.com> wrote in message news:F895CCAD-125C-4350-A8A0-FE469FA58DBC@microsoft.com... >I have a query with three parameters. The query asks for each parameter and > then repeats itself before returning the data. OR have you applied a filt...

Repeating 9095 9096
Hello all, We are running Exchange 2003 SP1 on Windows 2003, in an all 2003 server environment. Exchange server is running 4gbs of ram, quad xeon, stores are located on a SAN on 120gb drives. Did I miss anything? Outlook clients are Outlook 2000 or 2003 We have a bit of a strange thing going on. Every few days (randomly) we have the 9095 & 9096 events filling the log, every minute. While this is happening, the server is extremely slow to respond, and clients cannot access the application. There is NO problem getting to OWA, just through Outlook. Sometimes it stops on it's own,...

Repeating (or Looping?) an Action with VBA in Word 2007
Hello, I am very new to VBA. I am trying to create a macro for a Word 2007 document that finds a graphic, then resizes it to original size (Reset in Format Picture dialog box). I have recorded the following, which produces the result I want for one graphic. How do I make it loop so that it will perform the action on ALL the graphics in my document? When responding, please keep in mind that I am a novice with VBA. :) Sub ResizeSlide() ' ' ResizeSlide Macro ' ' Selection.Find.ClearFormatting With Selection.Find .Text = "^g" ...

Copy the sub-totals not the details
I have a worksheet (Excel 2007) that shows accounts sales data. The table has sub-totals showing the sum of the sales for each account. I need to transfer the sub-total data to another sheet to create a table that contains just the rows with sub-total values, and without the rest of the source data (so that I can import the sub-total figures into another piece of software). Anyone know how to do this?Jeff __________ Information from ESET Smart Security, version of virus signature database 4888 (20100222) __________ The message was checked by ESET Smart Security. http...

sql aggregate query
My table ID - Autonumber Field Patientid - This can have a number of records with the same number dDate is the date of the record and can be duplicated. I need to create a new table with the max of date for each unique patient id. So far not a problem. SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate) AS MaxOfdDate FROM TblCurrentEncounter GROUP BY TblCurrentEncounter.Patientid; But the kicker is that i need the id of the row containing the max of date. As soon as i try to add ID to the query I get error about not being part of the aggregate function. Any ideas w...

Calculating time in query
Hi there, I'm searching for some help with my query. I'm making access to be my administration program for my freelance job. I've made a table with a row 'starttime' and a row 'endtime'. Now in my query I want to calculate the difference between these two rows. And then I want to make another query to calculate the totals of these differences. Is this a logic way to do this? And I'm trying to make a expression to calculate the difference but with no luck. The result is something like this: 3,2515785. I've found a kb article but I still can't figu...

Minimize
I can minimize the whole program, but I can not minimize the file itself, the minimize button for the file is not there, what do I do? THanks S Hi Shawn Your Workbook is protect with Windows Checked Tools>Protection.....Protect Workbook -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Shawn" <anonymous@discussions.microsoft.com> wrote in message news:0f9b01c3932e$360a9df0$a401280a@phx.gbl... > I can minimize the whole program, but I can not minimize > the file itself, the minimize button for the file is not > there, what do I do? &...

Visio Reopens Repeatedly.
XP -Pro Visio 2002 SP 2 When I close Visio, it relaunches the application 6 times before staying closed. Just started doing this today. Anyone see this, have a cure? Hi Rikk, Did you install Adobe Acrobat 6 just before you started seeing this behavior? If so, you may be seeing a conflict between Acrobat and Visio, with suggested workarounds discussed in these newsgroup messages: http://groups.google.com/groups?th=3d5ecc278c1b28cd -- Dick Hamilton Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Rikk" <rfloh...

Rolling 12 month data charts
I was wondering if someone could give me an example to help me get my head around the best way to do this. I have posted before on how my charts us data tables and that I would like to change that. I need to add annotations to specific months that stay with the data point as the calendar year rolls to the next month. What I am needing is someone to help me out by giving me an example of what my chart should look like with the chart and then the data below in a similar format as the data table. I have close to 35 charts that I have to do this for and I want to make sure I start this new pro...

How to add "minimize" button for the dialog
How to add "minimize" button for the dialog - that is - the little "x" on the top right of the window? In resource editor, right click on the dialog and click properties. In styles tab, you will find the option "System Menu". Check it in your case. ------- Ajay Kalra ajaykalra@yahoo.com I am sorry. I also want minimize, that is "-" on the top right of the window How to add it by using code? This option (minimize box) is also available in the styles tab. -------- Ajay Kalra ajaykalra@yahoo.com Play with GetSystemMenu of your dialog and remove...

macro worked for a month and then stopped
Hello: I have a Scheduled Task running on a Windows Server 2003 box. If I am logged on as the administrative user and run the task manually, it runs perfectly. I can tell it runs, because the task places a report as a ".txt" file in a folder. Again, when I run the task manually, this report appears and this task runs. When I schedule the task to run at a certain time of day and stay logged on, it also runs fine. The report appears in that folder. But, when I schedule the task to run at a certain time of day and log off, it does not run. The report is not there. The IT ...

Minimize??
For some reason when I click on Minimize the window still takes up the full screen. Obviously I've changed something. How can I restore it back to where the full screen is not used? Thanks, Doug Do you mean minimise which is the leftmost icon, or do you mean the one in the middle which takes it from full screen to a smaller screen. Assuming it is the latter, hit that and then manually resize your window to the size you want. After that it should alter to this size when you hit the icon. -- Regards Ken....................... Microsoft MVP - Excel S...

Parameter criteria in MS Query to bring in all items for a single day
I think this is an easy one! I am querying on a date time field with e.g. 21/12/2003 07:00:00 and just simply looking to filter for all times on a specific date using a parameter query.(so keying 21/12/2003 in the parameter prompt returns all items for that day although the timestamps will be different) I guess I could create a calculated field using INT or similar but I'm sure there is an easier way than that. I don't think that worked anyway because of the data type situation. Presumably using ROUND is not appropriate. Also tried criteria 'between [date1] and [date1]+1' bu...