Crosstab Query 04-06-07

I have a crosstab query that shows the products i sale with the number
i have sold for each day.

instead of showing the sales for each day i would like to show sales
dor each months.

How do i show it my month


Thanks

0
Simon
4/6/2007 10:48:33 AM
access 16762 articles. 3 followers. Follow

1 Replies
1163 Views

Similar Articles

[PageSpeed] 53

In query design view, enter this into a fresh column in the Field row:
    TheYear: Year([SaleDate])
Replace SaleDate with your actual field name.

In the next column:
    TheMonth: Month([SaleDate])

You can now group on these fields instead of on each date.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Simon" <S.Dickson@shos.co.uk> wrote in message
news:1175856513.416115.203940@n59g2000hsh.googlegroups.com...
>I have a crosstab query that shows the products i sale with the number
> i have sold for each day.
> 
> instead of showing the sales for each day i would like to show sales
> dor each months.
> 
> How do i show it my month
0
Allen
4/6/2007 11:28:07 AM
Reply:

Similar Artilces:

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...

HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...

Media Player 11-27-07
I've tried a number of times to use Windows Media Player on an Access 2007 form. I am able to get it from the ActiveX menu, and run it repeatedly from a button. But once having played something, if I go to design mode or exit the form the player disappears, and only becomes available if I restart the application. Here's the code: Private Sub Run_Media_Player(sFileName As String) Player.settings.autoStart = True Player.URL = sFileName DoEvents End Sub I'd appreciate any help I can get. Jerry natkin ...

<no subject> 02-25-07
no ...

Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

Quick Create 06-01-04
is it possible to add more items to the QUick Create menu? ...

Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...

Option Group 04-19-10
I am new to using option groups. I will have several of them that will be a value of 1-5 and want to total them up in a report. Do I need a field for each option group? Are there field names that can be used to make it easier to total, ie grp1, grp2, etc.? Thanks, Les You've told us "how", but not "what". "How" depends on "what"! If you have data with values of 1-5, and "several of them", is there a chance you are working with something like a rating scale, or a survey? If so, you may benefit (*and get more...

Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...

Switch from Quicken 04
I just switched this past weekend. From the universe of the bizarre: -Money takes over 1 minute to load. -It frequently said that it had updates to install, but never installed anything. I finally determined that when Boinc/Setiathome was running, M05 would get little CPU time. As soon as I disabled Boinc, Money loads instantly and the update download occurred as soon as the notice displayed. -Converting transactions from Quicken went fairly well except for transaction that had categories in common with money and those same categories had subcategories in Quicken. In this case no...

assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...

MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...

Countif Formula 04-19-10
A B 1 112233 Expired 2 113344 Valid 3 116655 Valid 4 117799 N/A This Formula is to calculate the number of employee in the column. =COUNTIF(A1:A4,"*"). Can I make it =COUNTIF(A1:A4,"*")- N/A. I mean I want it the formula to calculat the number of employee but if the Column B has the work N/A. include it in the calculation. Try =COUNTA(A1:A10)-COUNTIF(B1:B10,"N/A") 'If you want to count only if there is a numeric value in colA then you may try =SUMPRODUCT((ISNUMBER(A1:A100))*(B1:B100<>"N/A")) -- Jacob (MVP - Ex...

Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...

Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...

Forced Saving of Queries, Forms, Reports, etc.
So I have a strange scenario... I have a complex/large database broken into a FE and BE (multiple actually). If I open the db with the Shift bypass key... everything development wise (designing of queries, forms, reports, etc.) works like normal. However, if I open the db and allow the main form vba code to execute (which includes an append query from a network BE table) and then switch to the db container and edit queries/forms/reports or create a new one... Access will auto save any changes made (when editing) or force me to save (when creating new). I don't get the &quo...

Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...

HA design question 07
We are looking to use CCR on our Mailbox servers, but we only have one CAS server in the currently design. If we loose the HAS server can the Outlook 2007 client still connect to the mailbox? Do the Outlook 2007 clients connect directly to the Mailbox server or through the CAS server? My understanding is that Outlook will still function if you lose your only CAS. OWA, Activesync, and Autoconnect will fail though. Question: If you're building a HA environement you want at least two HT servers, right? Install your CAS role on your two HT servers and voila. "Ryan Sanders&qu...

Setup was unable to grant access to the Microsoft CRM databases 10-07-04
I keep getting this message when installing MS CRM 1.2. Anyone else had this issue? ------=_NextPart_0001_31256612 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Brian, This error can mean a few different things. Below are a couple of KB articles that discuss the error message: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;861915 https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;870105 Thank you, Tami J. Lemar Microsoft Business Solutions Support This posting is provided �AS IS� with no warranties, and confers no rights. ------...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Workflow Manager 10-26-07
We recently had to reinstall our CRM Server, the redeployment tool was very helpful, however my workflow rules don't appear to be working. Any suggestions? I currently have a rule that in the past, after the technician completed his entry the ownership of the Service activity would change to me. Now the only way that happens is if you manually apply the rule. I just went into the manager file and noticed that all rules seem to be in a waiting stage from the time the server was brought back up. "deh" wrote: > We recently had to reinstall our CRM Server, the redeployme...

Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...