Crosstab Query For Fiscal Year Totals

I want to write a crosstab query for FY totals that will show each month 
regardless of wether there is data or not.  Any help on this is greatly 
appreciated.

Thanks!
0
Utf
2/26/2010 6:52:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1397 Views

Similar Articles

[PageSpeed] 6

The trick is column headings. Open the query in Design View. Right click up 
where the tables go and select Properties. In there is column headings. Put 
in the expected column names in order. Something like:

"Jan","Feb","Mar" and so on.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"DevlinM" wrote:

> I want to write a crosstab query for FY totals that will show each month 
> regardless of wether there is data or not.  Any help on this is greatly 
> appreciated.
> 
> Thanks!
0
Utf
2/26/2010 7:00:01 PM
Reply:

Similar Artilces:

Beginning balance total on G/L trial balance after year-end close
We ran the G/L year-end close during which we received SQL errors. We did get the report printout and the year being closed was marked as historical. Plus, for the new year, the balance sheet accounts now have beginning balances and revenue and expense accounts have zero beginning balances. However, when we print a G/L trial balance for the new year, the total of the Begininning Balance column has an amount. Normally, the total of this column should be zero. So, when we drill down to the BBF entry, it's out of balance by the amount of the beginning balance column on the trial ba...

Linking queries on a report
Hello all, I built a number of queries that all tie to the same table. Each query sorts the data differently, but comes from the same data source. I would like to show the results of these queries on the same report, but I get and error message that the wizard can't connect the queries. How can I display multiple queries on the same report. I've tried subreports, but they don't seem to export as well. Thanks b Brennan What does "export" have to do with this? If I have multiple sets of results I want to show in one place, I create an empty "main"...

beginning balance historical year
I am entering beginning balances in General Transactions Entry window for the previous year 2003. The year is marked as a historical year and I am using the last day from the historical year as the transaction date. When printing the edit list, the Batch Total Actual is the sum of the debit and credit entries from the source document. When looking at the edit list, all the entries are printed twice, while I have entered them only once, and the total per debit and credit entries also is double. When printing a historical Trial Balance the totals are o.k. Where the double entries are coming...

how to analyse a total
I am sure I used a tool or formula a few years back for this. I have a list of say 25 items. (column A) In the next column (Column B) there is an amount against each item. eg bread �5 I am looking for something that will give me the breakdown if I have a total of say �195 as to how that total was achieved.(from what items) On Feb 27, 7:46=A0am, "SS" <nonens...@blueyonder.co.uk> wrote: > I have a list of say 25 items. (column A) > In the next column (Column B) there is an amount against > each item. eg bread 5 > I am looking for something that will give me the...

min query
Hi, How do I get the 2nd minimum value and the 3rd minimum from a table after I have done MIN query for the 1st minimum value? SELECT dbo_CON.SC, dbo_CON.SN, Min(dbo_CON.SQ) AS MinOfSQ FROM dbo_CON GROUP BY dbo_CON.SC, dbo_CON.SN HAVING (((dbo_CON.SC)<>49)); Thanks, Hi Jerry, This will give me three different SC SN records. I am looking for same SC SN records that has next min SQ. table SC SN SQ 1 100 1 1 100 2 1 100 3 1 100 ...

Report Total Broker Commissions for year
Hi: I can't seem to figure out how to get a report on my investments with a broker how much I paid them in commissions for the year. I entered the commission with each buy or sell transaction from the confirmation statement from the broker so I know the data is there. -- Betty In microsoft.public.money, Betty wrote: > > >I can't seem to figure out how to get a report on my investments with a >broker how much I paid them in commissions for the year. I entered the >commission with each buy or sell transaction from the confirmation statement >from the broker s...

make query from to 64 bit system
I have a application need to be compile for 64 bit system. how can i read data from vb.net 2005. hi Jennifer, On 09.12.2009 17:36, Jennifer wrote: > I have a application need to be compile for 64 bit system. how can i read > data from vb.net 2005. First of all, I don't see the point why you're asking this question in an Access newsgroups. Further more, you should really be more spefific... mfG --> stefan <-- ...

<> data query
I have two tables and both have a field called tag_id. The first table "my2003" has about 5000 records. The second table "monitor2003" is a subset of table "my2003" and has about 3000 records. I need help building a query that will give me the records in table "my2003" where the "tag_id" field is <> to the "tag_id" field in the "monitor2003" table. Also which type of joining of the two tables is needed? Use the query wizard to create an unmatch record query that will list all the records in my2003 but are n...

Different queries but the same?
Hi Everyone, Access 2003. I accidently double clicked on the wrong thing in a new query based on another query. In the query grid, I added the qryVentThpyWithinLastArea and double clicked on the *. It only filled one column of my grid but when I ran the query, it showed all the fields from the query. I then dragged down Area field and added my criteria. I had been taught to double click on the blue title bar and then drag all the fields to the grid then add my criteria. Both ways give me the same data. What is the differnce? I pasted the sql below so you can see what's going...

Money 2006, QFX & 2 Years Online Services
I am thinking about moving from Quicken 2002 to Mny 2006. My Banks provide QFX files. The way I've been using these is to logon to their web sites directly, download and save the qfx files, then double-click on them to open Quicken and import. I've tested opening the files with Mny06 and it seems to work ok. So, will I be able to continue doing this even after the 2 years of online services expire? I don't seem to be using any of their services with what I do. Thanks very much. Yes, you will. you just won't get the msn updates like quotes and automatic downloads. "pl...

trim in query
Hi All I am trying to trim a leading space at the start of each field in my "Customers" table which is an Exel spreadsheet imported using "TransferSpreadsheet" After researching the internet I have been able to get this to work using, for example: CustomerName: Trim([F3]) in the Field line of a select query ("CustomersTrimQry"). My promlem is that when I append the data from "CustomersTrimQry" to the "Customers" table the space returns. Prior to using trim I ran 3 queries from the original "CustomersImport" table, "C...

Database SQL query
Sorry for this post here. If someone knows a group I can go to for this question, please let me know. I have a program in MFC that accesses and updates an Access database. The problem is I can't figure out how to add a field which contains a word with a single apostrophe ie (St. Vincent's). I'm using the CDaoDatabase and CDaoRecordset classes. I understand that DAO is interpreting the apostrophe as some type of terminator but there must be a way around the issue. MS Access has information on how to do it in Access VB but that doesn't seem to work in MFC. Thanks Ughh ...

MS query and Firebird
I am accessing a Firebird data base with MS Query into Excel. I am trying to change the column heading (column name) but as I writte a "Newname" in Column Heading MS Query dont accept. I try to change directlly the SQL statement with "AS" clause and without "AS" clause but MS Query dont accept. I dont know if the format of select statement is different in Firebird data base or is a problem with MS Query. JC. ...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Require Tax Detail Totals to Match the Pre-Tax Amount in SOP and .
Please incorporate the Require Tax Detail Totals to Match the Pre-Tax Amount functionality into POP and SOP. I created a CR/PR on this and dev informed me this was a product suggestion. This is very important functionality for European clients in terms of producing correct VAT returns. ...

Project Manager 2007 queries
I am considering purchasing Proect Manager for my my Planner, we are a small office who a familiar with the basics but have a few queries which are a little more detailed, I hope someone may able to advise on:- 1. Reporting - What are the options for importing and exporting documents and the number of format types, are they clear and easy to learn and use ? 2. Graphs - Is graphical data readily available, are there differing options of graphs and are the tools for producing them clear and easy to use ? 3. Sand pits - Are there areas where 'offline' working is availabl...

UDF XLA and SQL queries
Newbie question. Apologies in advance if it's too simplistic or in the wrong forum. I want to add an UDF to Excel. This UDF queries a SQL database. Say the function name is 'MyFunc'; I want to be able to have (in a cell) =MyFunc("ABC") return some data from the SQL database (for example here, say the Product Description for product "ABC"). The user needs to select the database the first time he uses the workbook. The UDF is called often in the worksheets of a given workbook so I would prefer to open the connection once at the beginning and...

Parse field in query
I have a table called MasterData with a field called Originator. The field data looks like this... Lou Santana Steven Sentry/Miami/PU/WC/US CN=Kathy T. Buon/OU=Miami/OU=BU/O=WC/C=US Terry J Leif I need it to look like this... Lou Santana Steven Sentry Kathy T. Buon Terry J Leif How can this be parsed?? -- deb What a mess that you're stuck with! Below will look for the first / and return the string before it in a query. If there isn't a / it returns the entire string. Of course this doesn't help with the CN= in CN=Kathy T. Buon. Parsed: IIf(InStr([Originator],"/")...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

Calculate the total overlapping time of multiple tasks, excluding non-working times
I am developing a tracking calculator for an overall process. Within the overall process, there are 3 steps which are completed. These steps can be completed independently from one another, but can also (sometimes) overlap. I am trying to determine the total time for the overall process (from beginning to end), without counting the duplicated times where the steps are running parallel to one another, or times when some steps may be completed and are waiting for another to start. I have outlined the basic design of the worksheet below, with times in mm/dd/yyyy hh:mm format. S...

Need help getting query to reference a field in a subform
I am trying to run a query using a specific field in a subform as the criteria. The query is displaying one combo box from the Form, as well as several fields from its own subform. My Form name is: FormGrantManagement My Subform name is: TableMOA subform My Subform's subform is: TableCostReports The field in the subform I want to reference is: MOAID I have found other help topics that lead me to think that a criteria along the lines of [Forms]![FormGrantManagement]![TableMOA subform]! [Form]![MOAID] is what I need to use, and I have tried multiple variations of this but witho...

Save Query Results as table to another Access Database
I have an access database that users use to pull reports (stored procs in sql server) and it paste the data on 4 sheets in excel. Occasionally the reports are too big for excel. I'd like to develop a similar database that saves the results of the 4 stored procs as tables in a new Access database. right now I have it returning the results as a query. How do I save the results in another database? Thanks, -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 Hi Billy The following SQL statement should do it for you: SELECT * INTO [TableName] I...

Fixed Asset Change Years
Hi, We have recently changed our Financial Periods so that for 2009 it will run from July 08 - Dec 09. It was previously set up as July 08 - June 09. The year 2010 and subsequent years will be from Jan - Dec. We have rebuilt our Fixed Asset calendar and have an issue regarding depreciation. Having run projections up until December 2009 it depreciates each month correctly up until December where it backs out the depreciation for months July - November. Does anyone know why it would do that? Thanks, Andy ...

Investment report for totals of activities
I can create a report Performance By Account, but it shows the Buys to include all Buys AND Add Shares. The Short Sells are already there, so that one's ok. Is there a way to separate them so I can see each of the totals separately? Another way I found was to create an Investment Transaction report and it sorts in all 3 activities, but won't give me a total of each. I'm not referring to the column "Total", but I want a total of ALL the Buys, ALL the Short Sells and ALL of the Add Shares. One combined total of each activity. IS there a way of doing this? Tracy ...