Merging queries by column in access

Hi, I have a series of queries which get data aggregated by year, e.g.

select year(date), min(var1), avg(var1), max(var1) from table where 
(ridiculously complicated set of conditions) group by year(date)

select year(date), min(var2), avg(var2), max(var2) from table where 
(entirely different ridiculously complicated set of conditions) group by 
year(date)

etc etc

There are six queries like this so what I want is to amalgamate these 
columns together into one results table (and also into one query so 
users can just run one stored query rather than six!) as follows:

year, min(var1), avg(var1), max(var1), min(var2), avg(var2), max
(var2)...

I would have thought this is an area where a join would be effective, 
but I am being defeated by Access' syntax.  Have to admit I've been away 
from SQL for some years and am a bit rusty!  Any thoughts?

Cheers
0
Xmas
11/26/2009 8:09:52 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
760 Views

Similar Articles

[PageSpeed] 38

You can try:

SELECT Year(date), min( iif( condition1, var1, null)), max( iif(condition1, 
var1, null)),  ... , min( iif(condition2, var2, null)) , ...
FROM ...
GROUP BY Year(date)


that is, remove the conditions from the WHERE clause which are not common to 
all expressions, since they are not in the iif on which operates the 
aggregations.


Vanderghast, Access MVP


"Xmas" <me@privacy.net> wrote in message 
news:MPG.2578f0eb2a02c23c989853@localhost...
> Hi, I have a series of queries which get data aggregated by year, e.g.
>
> select year(date), min(var1), avg(var1), max(var1) from table where
> (ridiculously complicated set of conditions) group by year(date)
>
> select year(date), min(var2), avg(var2), max(var2) from table where
> (entirely different ridiculously complicated set of conditions) group by
> year(date)
>
> etc etc
>
> There are six queries like this so what I want is to amalgamate these
> columns together into one results table (and also into one query so
> users can just run one stored query rather than six!) as follows:
>
> year, min(var1), avg(var1), max(var1), min(var2), avg(var2), max
> (var2)...
>
> I would have thought this is an area where a join would be effective,
> but I am being defeated by Access' syntax.  Have to admit I've been away
> from SQL for some years and am a bit rusty!  Any thoughts?
>
> Cheers 

0
vanderghast
11/26/2009 8:44:25 PM
Reply:

Similar Artilces:

Merge to email w/Word and Outlook 2002 security issue
I am trying to merge email using Word and Outlook 2002. This merge consists of about 3000 recipients. When I commence the merge I get an Outlook Security warning that tells me that some outside program is trying to send email using my Outlook profile and I have to click Yes or No. It procedes to ask me for ever single recipient... I'm not going to sit there and say yes for 3000 people. Do you know of a setting somewhere that I can change to not ask me everytime? Thanks, Brian What service pack(s) have you applied? Did you choose HTML as the message format for the merge? -- ...

ClistCtrl > How to auto-adjust columns size ?
Hi all, I have a CListCtrl in report view, two columns, hidden headerCtrl. I populate it with some items (less than 10). I'd like to programmatically auto-adjust the columns width according to their content, exactly like pressing Ctrl+'+' on a ListCtrl. Any idea ? Thanks, Dansk "Dansk" <dansk@laouilest.com> wrote in message news:e1x4VF9wHHA.276@TK2MSFTNGP06.phx.gbl... > Hi all, > > I have a CListCtrl in report view, two columns, hidden headerCtrl. > I populate it with some items (less than 10). > > I'd like to programmatically auto-adjus...

TRIM FUNCTION in QUERY
Hello, What do I put to trim off the spaces after a value in query? The table is obtained from ODBC link and the values on a field somehow got spaces. Example: My field in the query is: WC: PO_WORKCENTER_ID Not sure WC: TRIM(PO_WORKCENTER_ID) ???? That should work. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Cam" wrote: > Hello, > > What do I put to trim off the spaces after a value in query? The table is > obtained from ODBC link and the values on a field somehow got spaces. Example: > > My...

Custom reports/queries across multiple companies in Business Porta
Is there any way to create a custom query or report that can be run inside Business Portal and which pulls figures from multiple Great Plains companies. Thank you. A View can be constructed in one database that pulls data from other databases. This view can then be used as the source for reports in tools like Crystal etc -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "KT" wrote: > Is there any way to create a custom query or report th...

clicking TOOLS/OPTIONS gives me error msg "cannot access dir D:/"
I need to change settings in the TOOLS/OPTIONS menu. But when I click on any tab in the OPTIONS menu it gives me the error message "cannot access dir D:/documents" I may have changed the name of that directory at some point and I suspect that is the problem. However I still need to change settings in the TOOLS/OPTIONS menu. How do I fix it? Hi Lori Close Excel Add this directory D:/documents and try again And try again -- Regards Ron de Bruin http://www.rondebruin.nl "Lori Rossi" <Lori Rossi@discussions.microsoft.com> wrote in message news:34A25E1A-D...

tab in access
-- I added a tab control to a form what do i do so it should be from right to left? Gal On Sun, 21 Mar 2010 02:57:01 -0700, Gal <Gal@discussions.microsoft.com> wrote: That is currently not supported. Why do you need it? -Tom. Microsoft Access MVP > >-- I added a tab control to a form what do i do so it should be from right >to left? >Gal I am programing for a company in Israel and they write in hebrew plz help me if u can -- Gal "Gal" wrote: > > -- I added a tab control to a form what do i do so it should be from right ...

Outlook failure to send Access reports
I email reports from Access using Outlook. At times, the email will hang up in my outbox and will not send even if I open it and try to send it manually. Any of you fine folks know what's going on and how to fix this? Thanks - Ken Smith -- KMS Technical Solutions ...

Create form similar to MS Access 2000 Help
Is it possible to create a form that works similar to the Help - Index form in Access 2000? My StreetTable consists of the following fields: Street_ID(autonumber), StreetName(text), Directions(memo). I want to be able to type the beginning letters of the street and the List of StreetNames continually reduces until I see the street I need in the StreetNames window. I would then click on that street and the Directions would appear in the third window panel. Since I'm dealing with approx. 5000 streets, scrolling really isn't an option that I want to consider. I found that a combo ...

outlook mail merge from category
How do I separate out a particular category from my contacts to do a mail merge? Doesn't the "By Category" View of the Contacts Folder work for you? -- Russ Valentine [MVP-Outlook] "vedaggett" <vedaggett@discussions.microsoft.com> wrote in message news:1CAD3829-DB46-4F94-9A79-9716640842DF@microsoft.com... > How do I separate out a particular category from my contacts to do a mail > merge? ...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

Sending emailes from Access
Once i week i would like an easy way from access to send out a a email to some customer that query will find. Whats the eaasyest wasy to send a email to about 20- 30 people, the email will be the same to every one, I know i could go throught outlook and do a mail merge but is there a way i can just have a button on a access form that will automatical send out an email to lots of people Thanks very much Any help would be great Best Regards Simon Dickson Poke around the Access EMail FAQ that Tony Toews has at http://www.granite.ab.ca/access/email.htm. There's likely something there...

Outlook Web Access error
Got this error message when trying to access my mailbox from Outlook Web Access. "Unable to get renderer" This happened on a particular machine but work fine on other machine. Anyone know what going on? ...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

Access Reports Disappear
I had a database that I had used for years with no problem. Lately I've noticed that some of the reports are disappearing. Yet, if I try to create a new report with the same name, Access says "Name conflicts with existing module, project or object library." So, I created a new database. The only thing I imported was the tables from the old database. Yet, I'm having the same problem. Has anyone seen this before? Make sure you have SHOW HIDDEN OBJECTS set to YES. Then take a look around for those "missing" reports... "JG" wrote: > I had a da...

How to add summary fields to Group Footer in Access Reports?
How do you add a Summary fields to Group Footers in Access? I have a Detail field I want to Sum in the Group Footer in my report. -- Pat Dools ...

Options Group and Query Criteria, revisited
I recently posted a question about option groups passing info to a query for a Yes/No field, and received some great advice. However, I've tried to apply that advice in a similar way to a Date field, to no avail. In the query, I want the user to be able to display (1) only records with dates present or (2) only records without dates present or (3) all records. I changed the query criterion that worked for the checkbox as follows, and placed it under the Date field which I want to select by (this statement yields no records): IIF([forms].[fmQryByClinicSurveys].[emailOptions]=1...

Please help with New Database Query
Newbie to New Database Query - Can you change a spreadsheet to a table if so how? I have a lot of spreadsheets that I would like to work with in New Database Query. Can this be done. Any help will be greatly appreciated. In general, if you select a range and give it a name....MS Query will recognize it as a data source: Example for data in Cells A1:Z500, with column headings (EmpID, FName, Lname, etc): Select A1:Z500 In the Names box (just above the Col_A heading) type rngMyData1 Press [Enter] Next, save your workbook. Then...Data>Import External Data>New Database Query When y...

Spreadsheet Query
One of my clients has devised a list of items in a Microsoft Excel spreadsheet. Against each item (in a different cell) is an amount in �, which represtents the items value. My client wants to be able to use this spreadsheet, so that he can select all items under �6,000 for example. Is this possible in Excel, or should he be using a database ? Any suggestions would be appreciated. Michael. Hi Michael click in the list, choose data / filter / autofilter from the drop down list on the value column choose custom set the first box to less than and type 6000 in the second - click OK - to...

Can't access below toolbar.
I've used Windows Live mail successfully for several months but now I cannot access anything below the File, Edit, View etc bar. My mouse doesn't get a response from any button below that line. Help! This is a multi-part message in MIME format. ------=_NextPart_000_0037_01CB179F.F06F5C60 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Trevecchi. That=E2=80=99s a weird one that I haven=E2=80=99t heard before. But = that bar that starts with File | Edit | View... is the Menu Bar, not the = Toolbar. I hate to be...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

Skip Query Parameter
Dear All I Have a combo box that is feeding a query parameter to filter some rows. sometimes I want to view all records. the row source for this combo box is a table assigned only for that. and I want to add this option to be displayed in the combo box "View All". So I will add a new record in that table that will have the value of "View All" then the filter will not work. how can i solve it ? The data type in this combo box is "text". and I have similar case for "Date" Data type in text box. Thanks -- M.A.Halim mmhalim@hotmail.com Dear "M.A....

stationary column on a spreadsheet
I have a spreadsheet that i want to make the first column not move when i scroll to the right. And one teach a dummy. Select cell B1 Choose Window > Freeze Panes wc4ew wrote: > I have a spreadsheet that i want to make the first column not move when i > scroll to the right. And one teach a dummy. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

MS Query help required
Hi I have a spreadsheet on my notebook which I use for DBA services at a particular client. It connects to database server via an existing ODBC database connection extracting data from system tables. I would like to be able use this spreadsheet at other clients, WITHOUT having to create a new ODBC connection. Is there any way that this can be accomplished? The ideal scenario would be that the logon credentials and server address reside on one sheet and upon arrival at another client, one merely changes this information and then queries the database. Any help would be appreciated. Regards -...

Publisher 97 unable to do Mail Merge
I can not get Publisher 97 to do Mail Merge with Excel 97. Here's what happens: Mail Merge / Open Data Source / PUBMERGE Out of memory. Mail Merge / Create Publisher Address List / PUBMERGE Out of memory. Mail Merge / Edit Publisher Address List / PUBMERGE Out of memory. but, for some reason, Word 97 will do Mail Merge with Excel 97, no problem .. . . Publisher 97 System Requirements, and what I'm running: 386DX, 486DX: Pentium 4, 2.8 GHz MS Windows 95: XP 6 MB RAM, 8 MB: 2 GB C:\WINDOWS\system32\ . . . I have JET Engine DLL 5.1.2600.0 I am thinking that this this could be ...

count data in column
Hi, I am using excel97 and trying to create a chart that has 5 columns of data in it a,b,c,d,e. I an trying to make a chart only for certain data in column a and column d. The data that I key off of is in column d and begins with s/ how can I count the number of s/ in column d? how can I create a chart that shows both and only that data that begins with s/ and the data in column a? --- Message posted from http://www.ExcelForum.com/ In cell F2 (I assume row 1 has headers) enter this formula: =LEFT(D2,2) and fill it down as far as you need. select any cell in the table, and apply an au...