consolidate queries

I have many too many queries, which I use to gather data for a report.  I 
hope someone can suggest how to consolidate them.  For example, 8 of my 
queries are of the exact same form, except the value of one field is one of 
8 possibilities.  Here is the SQL for the qCUS query:
    SELECT q1Main.DReferred, q1Main.CaseType
    FROM q1Main
    WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And 
(q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND 
((q1Main.CaseType)="CUS"));

The only difference between the 8 queries is that in the qCUS query, 
CaseType = "CUS", in the qLTD query, CaseType = "LTD", in the qOTH, CaseType 
= "OTH", etc.  In my report, I need to list the CaseType totals, so I'm 
using the following as the controlSource in the form/report:
    =DCount("*","qCUS")

How can I consolidate my 8 queries into a single one, with the only 
difference being the CaseType?  Or, do I put the criteria for the CaseType 
field/parameter in the DCount equation?

TIA




0
zSplash
3/29/2007 9:40:53 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1056 Views

Similar Articles

[PageSpeed] 14

Create a CaseType table with two fields - CaseType & Active (Yes/No).

Put the CaseType table in the query like this ---
    SELECT q1Main.DReferred, q1Main.CaseType
    FROM q1Main, CaseType
    WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And 
(q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND 
((q1Main.CaseType)=[CaseType].[CaseType])) AND [CaseType].[Active]= -1;

Check as active those of the CaseType table that you want to use in the query.
-- 
KARL DEWEY
Build a little - Test a little


"zSplash" wrote:

> I have many too many queries, which I use to gather data for a report.  I 
> hope someone can suggest how to consolidate them.  For example, 8 of my 
> queries are of the exact same form, except the value of one field is one of 
> 8 possibilities.  Here is the SQL for the qCUS query:
>     SELECT q1Main.DReferred, q1Main.CaseType
>     FROM q1Main
>     WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And 
> (q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND 
> ((q1Main.CaseType)="CUS"));
> 
> The only difference between the 8 queries is that in the qCUS query, 
> CaseType = "CUS", in the qLTD query, CaseType = "LTD", in the qOTH, CaseType 
> = "OTH", etc.  In my report, I need to list the CaseType totals, so I'm 
> using the following as the controlSource in the form/report:
>     =DCount("*","qCUS")
> 
> How can I consolidate my 8 queries into a single one, with the only 
> difference being the CaseType?  Or, do I put the criteria for the CaseType 
> field/parameter in the DCount equation?
> 
> TIA
> 
> 
> 
> 
> 
0
Utf
3/29/2007 10:44:01 PM
Reply:

Similar Artilces:

Macro to check data from excel list against access query and return value back to excel
(I posted this yesterday but am tryign to make it clearer) I have an excel list with "name" and "title" and there are many erros and mis-spellings. I havbe an acess quesry that has the correct names and titles and also some mis-spellings and foreign language spellings pointing to the correct english name and title. I would like to write a macro in excel to do a lookup of the excel value in the access quesry and find EITHER an exact match or close match and return the value of a specified field back into an excel column. Here is a hypothetic example: Access list: PK=Van G...

Selecting columns from a query by using a form
I have querys with sums of patient-properties (they are symptoms like : coughing, Shingles, disability etc etc.) They query is a summing query making sums of all these properties for clients that can now be displayed, ordered by age and gender. I want users to be able to select from a listbox which column, ie which sum they want to view. How do I get a textbox to be bound to a column which is still to be selected from a listbox. I tried making a table for the listbox: columns: Userchoice, Fieldname where Fieldname is a text of the name of the column to be selected. ...

Trouble with Query
Hey. I've got trouble with a query. I'm trying to create a report based on if a customer has spent more than $250.00 in sales...the trouble is the total sales is not stored anywhere. On another report it's just a text box that says =sum(subtotal). Is there anyway for me to reference this in another query? Cathy On Oct 4, 2:51 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Just redo the calculation in your query from the raw data and use criteria of>250. > > -- > KARL DEWEY > Build a little - Test a little > > > > "cathy...

Consolidating Personal Folders
I have somehow opened up 4 personal folders. How do I consolidate all of them into one personal folder and merge all the information. Next, how do I delete the other personal folders? I've read through a couple of books but I can't find the information. Any help would be very much appreciated Thanks MikeSellers Move the contents of the folders to the folder that you want to keep. You can disconnect from the other pst-files by rightclicking the root folder and choose Close. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook I...

Data Query
I would like to create a query. .. ie. NFL sched for 2005. on the 1st sheet that pulls individual team info from the 2nd sheet. So I will input a team, and have the query search through a range located in two columns on the 2nd sheet. The info in the first column, 2nd sheet, is date (not necessarily in order). the second will contain info re events on that date. One of two column headings on 2nd sheet will allow me to specify the query subject (ie.ny giants) as the range identifier.. So really, my problem is, setting up the correct query, (dependant range) - then have it displayed in ...

Query File As
My Table Fields First Middle Last Suffix Need the new field in the qurey Will be FileAs: What is the expression so that FileAs displays Last (space) Suffix (if not null) then a (,) and a (space) then Fist (space) Middle Can anyone give me the correct expression for this so I can copy and paste from your reply Thank You from Marsman Try this -- IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix], "") & ", " & [First], & " " & [Middle], "") -- Build a little, test a little...

How to run a date based query for many months
Hello, I have one query based on a parameter "MyDate". I enter a full date ( dd/mm/yyyy french format but no matter ) and the query result is expressed by the month ( yyyy_mm) eg : i enter 25/12/2009 and the result is expressed in 2009_12 I have to run this query for n months before the entered date and 2 months after this date. Is it possible to get a SQL code for this ? Can you help me ? Thanks by anticipation. It would help if you posted the SQL of your query. Also, your requirement as stated is a bit vague. If you enter 25/12/2009 an...

Business Card query
I wish to produce my own business cards for a self catering flat with details on one side and a picture on the other side. So far I have produced the details and the photograph in Word but now want to match the two sides so that when printed they coincide. I wondered if Publisher has this facility. Blair What is on the right on the front will have to be on the left for side two. Might do better with either two pages or two separate publications. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Blair" <Darrach@aol.co...

select query of parent and child in one row
I have a parent table and a child table one parent has between 1 and 6 children in the child table I wont a query to have the parent and the child in one "row" how do I create that? thanks for helping me Your table structure is not correct for a relational database but this will get you there. This has three but you get the idea and expand on it. Place the table in the design grid as many times as you have field to combine. SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleF...

Routine to run/export queries
Looking for some links to examples that do the following (or something close): 1. user would create a whole bunch of queries themselves (using Access) 2. form that lets the user runs these queries to display results and/or export 3. Would be good if user could select multiple queries and get multiple resultsets to display or be exported to an excel file with multiple worksheets. Example: user selects 5 queries and wants an excel file made with 5 tabs with the 5 different outputs of the queries, or the outputs of the 5 queries show in a form with multiple tabs in Access (hide...

consolidate queries
I have many too many queries, which I use to gather data for a report. I hope someone can suggest how to consolidate them. For example, 8 of my queries are of the exact same form, except the value of one field is one of 8 possibilities. Here is the SQL for the qCUS query: SELECT q1Main.DReferred, q1Main.CaseType FROM q1Main WHERE (((q1Main.DReferred)>=[Forms]![fgetStats]![tbxBegDate] And (q1Main.DReferred)<=[Forms]![fgetStats]![tbxEndDate]) AND ((q1Main.CaseType)="CUS")); The only difference between the 8 queries is that in the qCUS query, CaseType = "...

BP
Suggestion: Make all find and queries case sensative or none We've discovered that the queries are not case sensative while the find functions (for example, the find function in the requisition vendor applet) tend to be case sensative. This creates a lot of confusion for the end users and a lot of "I can't find..." support calls. Consistency is a good thing, especially in a user interface. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...

How get text of query in VB?
Is there an easy way to get the text of a query so I can concatenate some extra 'where' conditions onto the end? CurrentDb.QueryDefs("NameOfQuery").SQL -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "mscertified" <rupert@tigerlily.com> wrote in message news:DBD58D7C-9280-437E-958D-AF3941FEC681@microsoft.com... > Is there an easy way to get the text of a query so I can concatenate some > extra 'where' conditions onto the end? ...

Data Consolidation
Biff: Thanks so much for your input on the automation. After a small bit of tweaking, your formula worked like a charm. Now that I have the correlation multi-year table comparison automated, I would like to consolidate each commodities multi-year correlation into an overall correlation set. What I have now is five sets of correlation rows/commodity (representing the five historical data sets) which look like the following: Australian Dollar (AD) Correlations ============================== 1yr: C S PN SI SM 5yr: BO BP CD DX EU GC S SF SM 10yr: DX EU GC MP SF SI W 15yr: DX TU SP MP 20yr: CC G...

Querying Dates
Hello, I am looking for some advice on how to tackle querying some data. I have two tables, table one with around 10,000 records per month and columns that give me (for each record) a start date, start time, end date and end time. Table two details a percentage for each hour and day of the week (so 24 hours * 7 days = 168 records). This has three columns, the day of the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage. What I want to do is sum the total of the percentage column in table 2 where the day of the week and the hour of the week fall within the st...

FRx Consolidations #2
I am trying to consolidate two companies with a different account structure. The column format has an account filter and the condolidation is not working. Company One: ##### Company Two: #####-## Column Accout Filter: ????? Note: The first five places of the account in both companies is the same, Company Two has additional departments. When I try to run the consolidation, I can only get one company to tree up, the one whose account structure matches the column account filter. What do I need to do to make the consolidation work? ...

query on dates
I want to compare DateReleased to DateNextAppt (both are date fields) then I need to get => 15 days. Please help me KT Kim See Access HELP for the DateDiff() function, which you can use in a query to subtract two dates. You could even use something like: [DateNextAppt] - [DateReleased] to create a new field in your query, and use: => 15 in the selection criterion. Regards Jeff Boyce Micosoft Office/Access MVP "Kim T" <KimT@discussions.microsoft.com> wrote in message news:F35F133B-CFD7-49E2-9668-7CAF9346CB3F@microsoft.com... >I want to compare Date...

WHY DOES ACCESS 2007 SHUT DOWN WHEN I TRY TO RUN A QUERY OR OPEN A
have just installed office 2007 on new machine - when i try to open some tables or run a query access drops out - HELP PLEASE !!!!!!! Sounds like you have a corrupt installation. I would do the following steps: 1.) Uninstall Office 2007. 2.) Run Disk Cleanup to remove temporary files and such. 3.) Run the Disk Error checking utility to mark any sectors on your hard drive that might be defective. 4.) Defragment your hard drive. 5.) Try re-installing the software. Good Luck, Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/...

Error message in Excel after exporting Access query to Excel
Hello, I have a few queries in Access that I have been exporting to Excel, and never had any problems for 7 months...until last week. Each Access query is not big, 5 columns, 2-50 rows. Excel file exporting to is about 500kb, not big either. Last week I exported queries from Access without any error messages. However when I tried to open Excel, it says Excel file I am trying to open encountered serious issues last time it was opened, then asked me if I want to report to Microsoft. It did not matter whether I said yes or no to this question, the question box kept coming up for a few ...

customer vendor consolidation #3
I would like to be able to transfer mulitple documents between payables and receivalbles all at once and have the option of having the documents transfer individually or as one lump sum. Currently if mulitple documents are transfered at the same time, a lump sum is transerferred instead of individiuals documents, which mean one credit memo is created instead of one per document being transferred. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in t...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...

consolidating
I have a situation where a credit card statement comes in and we have to assign each line item a particular GL code. On one statement this GL code may be used numerous times. The same GL code may also have credits as well as debits. The person writes on the statement what each line item will have for a GL code. From here we want to enter line by line each item in the statement on a sheet in Excel. When this data entry step is done we would like to have Excel do the consolidation to another sheet so it then does the calculation which should match the bottom line of the statement. (pant, pant,...

What is the best online consolidator
What is the best online consolidator to produce a single page view of numerous bank and brokerage accounts. I understand that each will remain seperate. On Jun 19, 4:09=A0pm, "WOppenhe" <woppe...@ucla.edu> wrote: > What is the best online consolidator to produce a single page view of > numerous bank and brokerage accounts. =A0I understand that each will rema= in > seperate. I've played with Yodlee for a few years - it keeps getting better and better ...

proper bracketing to get combo column into a query
I am trying to get the text value from a combobox into a query where clause like so Like "*" & Chr(34) & [Forms]![frmItemTypes]![cboItemDivision]. [Column(1)] & Chr(34) but nothing works. This works but only for numbers [Forms]! [frmItemTypes]![cboItemDivision]. How do I bracket for text from columns in the combo box? On Jul 28, 3:13 pm, asc4john <j...@kinnear.ca> wrote: > I am trying to get the text value from a combobox into a query where > clause like so > > Like "*" & Chr(34) & [Forms]![frmItemTypes]![cboItemDivision]. > [C...