queries / forms/ report

I have a query for every month of the year and a report that has all 12 
months on that report. What  I would like to be able to do is to have one 
query and show each month on the report, but i would also like to able to 
input the time span that the query should calculate. I hope this make sense. 
Please let me know if this needs for explination. Thanks for your time.
0
Utf
2/11/2008 6:16:00 PM
access 16762 articles. 3 followers. Follow

3 Replies
889 Views

Similar Articles

[PageSpeed] 1

On Mon, 11 Feb 2008 10:16:00 -0800, Erik <Erik@discussions.microsoft.com>
wrote:

>I have a query for every month of the year and a report that has all 12 
>months on that report. What  I would like to be able to do is to have one 
>query and show each month on the report, but i would also like to able to 
>input the time span that the query should calculate. I hope this make sense. 
>Please let me know if this needs for explination. Thanks for your time.

Please open one of your queries, select View... SQL, and post the SQL text
here.

It's certainly possible to do this using a parameter query, and you do indeed
need only one query, not 12. Rather than twelve subreports (if that's what you
now have) you can use a single Report, and use its Sorting and Grouping to
group by the month.
-- 
             John W. Vinson [MVP]
0
John
2/11/2008 6:29:56 PM
I am not to sure on how to setup the  Sorting and Grouping  feature. Is there 
any examples or information you might be able to give. Thanks

"John W. Vinson" wrote:

> On Mon, 11 Feb 2008 10:16:00 -0800, Erik <Erik@discussions.microsoft.com>
> wrote:
> 
> >I have a query for every month of the year and a report that has all 12 
> >months on that report. What  I would like to be able to do is to have one 
> >query and show each month on the report, but i would also like to able to 
> >input the time span that the query should calculate. I hope this make sense. 
> >Please let me know if this needs for explination. Thanks for your time.
> 
> Please open one of your queries, select View... SQL, and post the SQL text
> here.
> 
> It's certainly possible to do this using a parameter query, and you do indeed
> need only one query, not 12. Rather than twelve subreports (if that's what you
> now have) you can use a single Report, and use its Sorting and Grouping to
> group by the month.
> -- 
>              John W. Vinson [MVP]
> 
0
Utf
2/11/2008 6:51:02 PM
It depends to some degree whether you are returning rows from the table(s) or 
aggregating values.  If you are simply returning rows then you can include 
two computed columns in a query to return the Year and month (as numbers) for 
each date value, e.g.

SELECT TransactionID, TransactionDate, Amount, 
YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth
FROM Transactions;

You can then group a report, using its internal sorting and grouping 
maechanism, firstly on the TransactionYear and then on the TransactionMonth, 
giving the TransactionMonth group a Group Header.  Include the 
TransactionYear and TransactionMonth in the group header and the other fields 
in the detail section.  You could of course give both groups group headers if 
you prefer and out the year in one and the month in the other so that the 
year only prints once in the report.

If you are aggregating data you do much the same but group the query on the 
year and month, e.g.

SELECT YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth,
SUM(Amount) AS TotalMonthlyAmount
FROM Transactions
GROUP BY YEAR(TransactionDate), MONTH(TransactionDate);

With a query like this you' d probably not use group headers in the report 
at all, or just have a TransactionYear group header, putting the month and 
the total amount in the detail section.

To restrict the report to  date range you can include parameters in the 
query.  Date/time parameters are best declared as otherwise a date in short 
date format might be interpreted as an arithmetical expression rather than a 
date and give the wrong results, so taking the first query above as an 
example, adding parameters would give you;

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT TransactionID, TransactionDate, Amount, 
YEAR(TransactionDate) AS TransactionYear,
MONTH(TransactionDate) AS TransactionMonth
FROM Transactions
WHERE TransactionDate >= [Enter start date:]
AND TransactionDate < [Enter end date:] + 1;

The method used her for defining the data range, returning all rows on or 
after the start date and before the day following the end date, makes sure 
that if any TransactionDate values in the table inadvertently include 
non-zero times of day (which can easily happen if you haven't taken steps in 
the table design to prevent this, i.e. a validation rule limiting the values 
to dates with non-zero times of day) and those dates fall on the final day of 
the range, are nevertheless returned.  A BETWEEN….AND operation would exclude 
such dates.

I've shown the queries as SQL here, but you can also build them visually in 
query design view of course.

You can also aggregate values in the report; for instance with a report 
based on the first query you could have group footers for the month and year, 
and a report footer and out a text box in each with a ControlSource of 
=Sum([Amount]).  This would give you sub-totals by month and year and an 
overall grand total.  You can also use other aggregation operators of course 
as well as Sum, e.g. Avg, Min or Max.

I hope that gets you started.  If you need any help applying it to your own 
database post back.

Ken Sheridan
Stafford, 

"Erik" wrote:

> I have a query for every month of the year and a report that has all 12 
> months on that report. What  I would like to be able to do is to have one 
> query and show each month on the report, but i would also like to able to 
> input the time span that the query should calculate. I hope this make sense. 
> Please let me know if this needs for explination. Thanks for your time.

0
Utf
2/11/2008 7:07:01 PM
Reply:

Similar Artilces:

Frx Reports to be use on other client machines without installing
Hi, I installed Frx as a thin clinet on my GP client machine and created Balance sheet and income statement reports successfully but now I want to run these reports on other clients machine without installing the Frx. Can u please tell me the correct way. Your immediate help will be greatly appreciated. -- Developer -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 "Dexdev" wrote: > Hi, > > I installed Frx as a thin clinet on my GP client machine and created Balance > sheet and income statement reports successfully but now I want t...

Import Form
I would like an opinion on this... I have system that allows data to be saved each day in a MS Access Database called Test with one Table that contains the data. I would like to create a form in a MS Access Database called IMPORT that would allow a user to browse to the Test Database and each day automatically import the data from the Test database table to the IMPORT Database. I am trying to avoid users having access to the tables and importing data from the tables of a database to the IMPORT Database table. I would appreciate any suggestions! -- Message posted via AccessMonster.com http:...

Populate Excel form using Access
I have a form created in Excel that must be filled out with 200 different sets of information to create 200 different forms. Is it possible to do this using an access database I create? I would like it to product the 200 forms all at once so I do not have to print them individually. "BML" <BML@discussions.microsoft.com> wrote in message news:4A41E066-89D0-413E-B9C9-A4845AD80A86@microsoft.com... >I have a form created in Excel that must be filled out with 200 >different > sets of information to create 200 different forms. Is it possible to > do this ...

I need to add a drop down menu on a form, what commands do you us.
i NEED TO PUT A DROP DOWN BUTTON ON AN EXCEL FORMS You can use Data Validation to create a dropdown list on a worksheet. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html HGHDF wrote: > i NEED TO PUT A DROP DOWN BUTTON ON AN EXCEL FORMS -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Query Based Dist Group
Hi - I'm using query based dist groups for selected containers. As an example all users in container abc are memebers of everyone@abc email group. I want to add a contact that is in a different container to the everyone@abc email group. With a regular dist group you can just add as a member. With the query based I'm having a hard time figuring out if this is possible. Do I have change the query group to a dist group? Any help is greatly appreciated. -Thanks! Could change QBDG to regular DG, or change the query to add any attribute (like sAMAccountName) of the particular user in...

report not showing results
Hello, I have created a report which displayes some information one field being a read only field, when i generate the report the field is blank, any ideas why this would be? also my account ID field just list #ERROR for each account name.? any ideas? On Aug 8, 6:22 am, Ian <I...@discussions.microsoft.com> wrote: > Hello, > > I have created a report which displayes some information one field being a > read only field, when i generate the report the field is blank, any ideas why > this would be? > > also my account ID field just list #ERROR for each account name...

sorts query while including <all> at top of list
I have a combobox whose data source is this query, which sorts the records correctly: SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1), IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0")) ; however, when I add the following (to include the <all> word at the top of the combolist...) it no longer sorts UNION Select &q...

Lifetime Planner Report
I printed the 20 pages of the Lifetime Planner and notice that several of the graphs on the later pages show blank. Does anyone know how to view the Lifetime Planner report before printing or save to a separate file for viewing and printing. >-----Original Message----- >I printed the 20 pages of the Lifetime Planner and notice >that several of the graphs on the later pages show blank. > >Does anyone know how to view the Lifetime Planner report >before printing or save to a separate file for viewing and >printing. >. > I have the same problem --- did you ge...

Report Session Expired Error
Finally got emai working, thanks to everyone in here! Now it's on to the next challenge. Every time I try to page forward in a report or drill down in one, I get a Report Session Expired message. Thanks in advance for the help, agian! T how much daya are you reporting on? does this happen on all reports or only a few? "Troy" <anonymous@discussions.microsoft.com> wrote in message news:176e01c3fbe1$2725fc00$a501280a@phx.gbl... > Finally got emai working, thanks to everyone in here! Now > it's on to the next challenge. Every time I try to page > forw...

Trying to write a query
Sub Data Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Long Cells.Clear ' Database information DBFullName = "E:\Employees\Data.accdb" ' Open the connection Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;" Cnct = Cnct & "Data Source=" & DBFullName & ";" Connection.Open ConnectionString:=Cnct 'Create RecordSet Set Recordset = New ADODB.Recordset 'Name of the table is: Hourly:Table quer...

Multiple pictures for current status display in a form
Hello, I tryed to look through several groups but haven't found a genius hint yet. Hope you can help me. I would like to create a MS-Access form that reads data from an online system, showing the status of various machines (running, pausing, actual product and so on). I would like to show on the form a symbol for each machine. This symbol should be depending on additional status information. For example a red machine for a stopped machine, a green for a running... I don't want to use just a simple rectangle but a jpg or gif. The next problem is, that I have a dynamic amount of these ...

reporting services report delivery
We recently moved our 2K3 Exchange server from an older machine to a new one. After the migration our reporting services reports delivered via email no longer show the KPI graph images embedded in the reports. Only a red x is displayed. You cannont right click on the picture and get the picture to show. This happed at the same time we moved to the new serve so I am guessing I missed a setting during the move. Any suggestions where I may have missed something? CSL1973 <CSL1973@discussions.microsoft.com> wrote: >We recently moved our 2K3 Exchange server from an older machin...

Forms dictionary behaving differently locally and on network share
I have an issue where say on cash receipt window, none of the save or post buttons work and check/card number field is showing mandatory when the forms dictionary is on the network. I have similar problems reported by other users on different windows relating to forms.dic. These reports having issues have not been modified and they do not show a period prior or after their name either. When I copy the same forms dictionary locally and point to it, the form behaves normally. I have given full control of the forms dictionary share to myself. This is GP 9, SP2. Can I check on anything t...

Report Email
Hi We have designed custom sales reports for Sales Manager. Our Sales Manager would like to see all the daily activities of each sales person. We have designed 4 reports, a list of all emails sent today, a lsit of opportunities created today, a list of phone call sent today and a list of quotations created today. I have set up a schedule in SQL Reporting Srevices to have these reports emailed to our Sales Manager at 16:40 each day. At the moment we are running CRM 3.0 and we will be upgrading to CRM 4.0 within the next few weeks. Our Sales Manager has requested that he would like one ...

Named Printers report list.
Is there a list anywhere that shows what reports are affected by which Named Printers options? What do you mean by options? "Dnelson" wrote: > Is there a list anywhere that shows what reports are affected by which Named > Printers options? As in which reports are affected by which named printer operation. Like when it said Posting reports - Purchasing, exactly which reports does that effect? "Leslie Vail" wrote: > What do you mean by options? > > "Dnelson" wrote: > > > Is there a list anywhere that shows what reports are affecte...

Report rendering in Web Page, Export or Print modes
Hi, There any way to know, in a report of Reporting Services and in server side runtime (Custom Code), if it is rendering in Export or Print modes, or in a Web Page?; may be obtaining the command parameter? My problem is that I want to show several elements of the report in Export or Print modes, but not in web pages. Thanks ...

Can Business Alerts be used to run Queries to insert the data in the Tables or update Tables ?
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01CA8ED8.6826BDD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello people, I was wondering if it is possible to run a SQL script through Business = Alerts in GP. what I mean to say is that if I need to insert record from = one table to another table I can copy that script and it will run at = scheduled time ?=20 Regards, ------=_NextPart_000_0030_01CA8ED8.6826BDD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encodin...

help on composing an aging report for transactions
hello all, I am new to SSRS. I need some help on how to create an aging report in ssrs. This is what my report should look like: Month identify $ $closed $Recovered $Open 0-30day 31-60day Jan 2009 $$$ $$ $$$ $$$ $$$ $$$ I am having trouble on the days column. My user want these fields to contains the total $$ amt that was recovered in 0 - 30days and 31 - 60 days. how to do that? Please help.... Sherry From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-...

Sorting Query
I have a range of data, say A1:N150. The data in column A reads Field 1, Field 2, Field 3 etc and so on up to a possible of Field 150. However these can be in any order at the start and there may be blanks. As you know if the data was sorted by this column the it would turn out Field 1, Field 10, Field 11 etc. I got round this by entering in column N1, =If(Len(A1)=0,200,Len(A1)) , then copying this down to N150. I then sort the data by column N, then sort by Column A. This then puts the fields in the correct order, and puts the blank entries at the bottom. I am not sure if this is a perfect so...

Change "Close Opportunity" form
How can i change the "Close Opportunity"-form? We've installed a Dutch version of CRM, so I don't know how the topmenu-values are called, but here's a guess: When you are in an Opportunity and you click on Actions -> "Close Opportunity...", there will pop-up a form. And i want to change that form but i can't find it.... What do you want to change? The only thing you can control on this form (in a supported manner) is the list of values in the drop downs. -- Matt Parks MVP - Microsoft CRM "AmonRa" <AmonRa@discussions.microsoft.com...

need help with tables so form okat- multiple equipment for 1 job
Here goes. I know this should be simple not sure why I can't see this right. Here is what I want to end up with not sure the best way to set up the tables to get what I want. and/or best way to create the form(from a query) or using form wizard pulling in tables that I need. I want to END up with a Form for all possible JOBS(cleaning jobs) with equipment needed and soaps needed for each job. There are many pieces of equipment for each job - mop, bucket, etc.(can't figure out how to get this in a form without listing with a comma) Have so far - don't thinks the tables a...

How to make a filter in a report?
I know this may be a odd question considering the number of posts on filters in reports... but I can't find anywhere a description on how to create a filter (input box) in the report in the first place. I have joined the source with a query that contains "total sales" for each customer and I want to filter the report so that only sales above x dollars are shown. I presume there's a way to make a textbox / select list that can be used to enter the value (sales) and operator (< >) to filter the output (report)? Kindly, Mikael Sweden Mikael Lindqvist wrote: >...

Launching (custom) reports from the taskpad
Hi. I would like to link directly to our most commonly used reports (including filters) from the taskpad. I can't figure out whre the link is set between the report url in the html file and that particular report opening? eg. url="reportsalesdetailedsales" opens up the Sales - Detailed Sales Report (Tax-Inclusive).qrp file. Any ideas??? We use this report with different filters to include/exclude certain departments to show daily sales... I could make and save new .qrp files including the filters but is there any way to launck them directly from the taskbar like I can ...

What exactly is the purpose of a data form?
I'm studying Excel and just completed learning about data forms. Out of curiousity, what might be some types of applications/examples for using this feature? Creating purchase orders or invoices or quotes. Employment applications or reviews. Anything you would normally fill out by hand can be done with a data form. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Carter Devereaux" <CarterDevereaux@discussions.microsoft.com> wrote in message news:4F6A9B11-8EED-49F8-AD47-E252A064A97A@microsoft.com... > I'm studying Excel and just com...

Can I change the color of a form based on status?
I have a form for work orders. I track the work order number, part number, serial number, etc. I would like to add a combo box with work order status Open/Closed. I know how to do this part. I was wondering if I can link a command that will change the color of either the box or the form based on the current status of the work order. When I scroll through the work order form I rapidly know which work orders are open and closed based on the color. The parts are shipped out to various agencies for repair and sometimes it may be months before we see our parts back. Once a week I go t...