Can I use a Report expression for queries?

Is it possible to use the following expression that is used in reports on 
queries?

=[Sales]/Sum([Sales])*100

Or some expression to get the result.
Thank you.
0
Utf
12/18/2007 1:32:01 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
662 Views

Similar Articles

[PageSpeed] 23

No. In your query, you can refer to text boxes on a form, but not on a 
report.

That's because of the way reports are formatted (sequentially.) Forms have a 
current record, which identifies which value you want. Reports don't.

In the context of a query, there are other ways to get totals, such as 
DLookup():
    http://allenbrowne.com/casu-07.html
or a subquery:
    http://allenbrowne.com/subquery-01.html

-- 
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.

"Robbie Doo" <RobbieDoo@discussions.microsoft.com> wrote in message
news:2A643337-8701-42FA-8C13-EB71461F8EB0@microsoft.com...
> Is it possible to use the following expression that is used in reports on
> queries?
>
> =[Sales]/Sum([Sales])*100
>
> Or some expression to get the result.
> Thank you. 

0
Allen
12/18/2007 1:38:45 AM
It is possible to do something like this in a query.  It would require a 
sub-query to do so.

SELECT Sales/
   (SELECT Sum(Sales)
    FROM YourTable as Tmp) *100 as PercentCalc
FROM YourTable

Of course that works over the entire table and you are probably more 
interested in doing this for specific sets of data.  For instance for the 
year 2007 and by store

SELECT YourTable.StoreId
, Sales/
   (SELECT Sum(Sales)
    FROM YourTable as Tmp
    WHERE Tmp.SalesDate Between #1/1/2007# and #12/31/2007#
    AND Tmp.StoreID = YourTable.StoreID) *100 as PercentCalc
FROM YourTable
WHERE SalesDate Between #1/1/2007# and #12/31/2007#

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Robbie Doo" <RobbieDoo@discussions.microsoft.com> wrote in message 
news:2A643337-8701-42FA-8C13-EB71461F8EB0@microsoft.com...
> Is it possible to use the following expression that is used in reports on
> queries?
>
> =[Sales]/Sum([Sales])*100
>
> Or some expression to get the result.
> Thank you. 


0
John
12/18/2007 12:42:55 PM
Thank you guys both for your responses. The reason why I was asking this was 
to apply the result to a Chart that I would like to create. Unless you have 
another suggestion for charts because I can't get no correct results anything 
I try.

"John Spencer" wrote:

> It is possible to do something like this in a query.  It would require a 
> sub-query to do so.
> 
> SELECT Sales/
>    (SELECT Sum(Sales)
>     FROM YourTable as Tmp) *100 as PercentCalc
> FROM YourTable
> 
> Of course that works over the entire table and you are probably more 
> interested in doing this for specific sets of data.  For instance for the 
> year 2007 and by store
> 
> SELECT YourTable.StoreId
> , Sales/
>    (SELECT Sum(Sales)
>     FROM YourTable as Tmp
>     WHERE Tmp.SalesDate Between #1/1/2007# and #12/31/2007#
>     AND Tmp.StoreID = YourTable.StoreID) *100 as PercentCalc
> FROM YourTable
> WHERE SalesDate Between #1/1/2007# and #12/31/2007#
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Robbie Doo" <RobbieDoo@discussions.microsoft.com> wrote in message 
> news:2A643337-8701-42FA-8C13-EB71461F8EB0@microsoft.com...
> > Is it possible to use the following expression that is used in reports on
> > queries?
> >
> > =[Sales]/Sum([Sales])*100
> >
> > Or some expression to get the result.
> > Thank you. 
> 
> 
> 
0
Utf
12/18/2007 3:42:02 PM
Reply:

Similar Artilces:

Outlook2002
Can I format Outlook2002's email so that every time I click on Send, the new email page opens with a specific text in the Subject line? I want to set up all the computers in my office so that every time a member of staff sends an email, the company's name appears first in the subject line. This way, even if the recepient doesn't recognize the sender's name, they will see what company it's coming from and won't delete it as a spam! Many thanks. DM ...

hyperlinks in getting external data through web query
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C47E46.231F97A0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable =20 The WEB PAGE I am downloading using the WebQuery with <full Html = formatting> is something like the following=20 Symbol Name Last Trade Day's Range Volume More Info =20 ^BSESN SENSEX 3:58pm 5,233.21 5,154.32 - 5,240.29 N/A Chart, Components =20 ACC.BO A C C-A 4:00pm 261.40 255.30 - 262....

Use of the automatic cancellation of a subscription marketing
Hello, I have a base of accounts to which I send regularly documents (email.). However I know that in CRM there is a possibility of updating automatically the field "to send the documents marketing" (to put it at the value "no") when we receive a request by email of withdrawal of subscription of the customer. Do you know how that functions it because I can't make it function properly? Thank you by advance for your answers (and sorry for my bad english). Hello - I'm not sure exactly what problems you are having, but here's the basic steps. 1. you need ...

Using Access Reports in other EXE file
Hello I'd like to use access reports from any access version XP, 2000 or 97; because of the easy way to build such reports. Have you ever found or built any solutions permitting to reuse such access reports objects in other exe files, developped in Visual Basic, Visual Studio or even Delphi.... Thanks a lot for your suggestions Hi. Most programming languages support launching an executable, so the Snapshot Viewer executable would be a good choice for viewing reports exported from Access as Snapshot (*.SNP) files. Access would not need to be installed on the computer in order to view ...

How can I compare 2 sets of Social Security #'s and Identify dupes
I'm sure this can't be that difficult but for some reason it's just not working for me. I have 2 lists of social security numbers that I need to compare and pull out duplicates. Any help would be greatly appreciated. Thank You. Use a formula of =IF(COUNTIF(A:A,B1)<>0,"Duplicate","") in C1, and copy down -- HTH RP (remove nothere from the email address if mailing direct) "vwwolfe" <vwwolfe@discussions.microsoft.com> wrote in message news:94DDF32D-4790-40E4-87D0-6B52BF406587@microsoft.com... > I'm sure this can't be ...

Form property as query criteria
Hi. I am working on an Access 2000 db in Access 2003 (using 2000 format). In my original 2000 database, I would create properties in VBA (using Property Let and Property Get statements) for my forms and use them as criteria in queries. For example, if I had a report that should show data for a specific date range, then I would create properties of a form (call it "frmReport"), and name those properties ReportStartDate and ReportEndDate. Then, in the design of the query that the report is based on, under the criteria for a date field, I would add ">= [Forms]![frmReport].[R...

Multiple <> query criteria
I am trying to create a query to say I want everything from a table but not the following multiple reference numbers e.g. <>4000 <>3568 <>5869 <>1023 <>4759 If I do just 1 not criteria it works, as soon as i try to put more in it does not work. I have tried putting each one in on a seperate row and tried <>4000 or <>3568 or 5869 etc but neither works Any ideas? You should be using AND instead of OR. You want records where the value is NOT 4000 and the same Value Is NOT 3568 etc. You can also use this expression which is equiv...

monthly/weekly reports
This is probably really easy to do but I can't seem to figure it out. I have a report that displays records from a table. Each record has a corresponding date. Right now, the report shows every record in the table, going back more than a year. How can I make it so the report just shows the records from the previous week or month or whatever dates I choose. And then how do I use this same report over and over for each month or week? Basically I just want to hide certain records on the report (ones that were created before a specific date). Thanks You can base the 'record s...

Can't Open Money
I was using MS Money 2002 this weekend and everything was working fine. I tried to open it later in the weekend, and the program will not open. I hear my hard drive make some noise, but then nothing happens. Everything else on my machine is working fine. Does anyone know how I can fix this? Thanks, Sean In microsoft.public.money, Sean wrote: >I was using MS Money 2002 this weekend and everything was >working fine. I tried to open it later in the weekend, >and the program will not open. I hear my hard drive make >some noise, but then nothing happens. Everything e...

Can't send email 04-22-10
Can someone help me? I am only sending to a list of 20 recipients, yet I get the following error: The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail address was 'wilfred.greitens@kofc.org '. Subject 'Catholic Financ...

How can I use conditional formating between two sheets in one work
If employee number 5263 found in sheet 2, show the raw in main data on sheet 1 in red, for instance. ok this is how i did it use a helper column and a vlookup formula back at sheet 2 =IF(VLOOKUP($G$11,Sheet2!$A$2:$A$8,1,FALSE)=A8,"found","") so if employees name is found on sheet 2 this formula returns found on the same row as the emplyees name conditional formatting for this row is =c8="found" i had to change the formatting formula for each row,hopefully there is an easy way! -- paul paul.shepherd@nospamparadise.net.nz remove nospam for email addy! "...

Commission Report #2
I must be missing something but is there any way to generate a list of commissioned items. Not sales of commissioned items just a list of the items that offer a commission. This is a multi-part message in MIME format. ------=_NextPart_000_0591_01C71312.99942370 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0592_01C71312.99942370" ------=_NextPart_001_0592_01C71312.99942370 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Tom, Try this one attached. --=20 Jeff=20 Check Point Software =3D=3D=3D=3D=3D=3D=3D=...

Macros
I'm trying to create a macro to open all the Excel files in a folder and copy/paste the second line of each worksheet in a separate file already created. Could somebody help me? I don't know a lot about macros. Thanks! The code below should do the trick for you. To put the code into your workbook, open it up and press [Alt]+[F11] to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert --> Module. Then copy the code below and paste it into the code module presented to you. Close the VBE and save the workbook in the SAME FOLDER with the other files...

Problem using two timezones
I'm having a problem with Outlook 2003, trying to use two time zones. The time display for both zones is correct when I initially set up the two zones; but if I swap the time zones, the first zone displays an hour less than it should. If I swap back, they're correct. What's going on and how do I fix it? does the second zone have the correct daylight time setting? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ O...

Recently Used File List Problem
I have Excel 2000. I have the recently used file list set to 6. During a session of Excel, it will expand from, say, 4 files to 5 (when I click on File/Open), but after I close Excel and relaunch it, it loses the last file opened and only shows 4. This seems to happen ONLY with spreadsheets from a particular folder. Others stay across sessions. What happened, and what can I do to get it back to working properly between sessions? Thanks. ...

Edit cell by using mouse
How do I restore the function of Edit cell by using mouse - right click? Try double-click with left mouse button instead. Michelle wrote: > How do I restore the function of Edit cell by using mouse - right click? ...

Create and Use Button Menu using visual Basic in Excel
The following code is very simple. All I need to do is figure out how get the result of the userform entry from the click event. I apologize if seems to easy but I have trouble with userforms and menus getting user data. Thanks for Your Help Sub test() UserForm1.show If Me.OptionButton1 = "True" Then Debug.Print "Option One has been Selected" Else Debug.Print "This has failed miserably" End If End Sub Private Sub OptionButton1_Click() Dim Sel As String Unload Me MsgBox ("You Select Buton 1") Sel = Me.OptionButton1 End Sub Private Sub OptionButton2_...

Can this Be Done in Excel ?
I have a employee job scheduling table that I would like to populate based on criteria from 2 other tables. The scheduling table has the employee names listed in each row and across the top is the period of the day broken into 4 time periods. The critera tables are: 1. A training matrix that lists the employee names in each row and the jobs listed across the top. If an employee is trained, the intersecting cell has a "T". If they are not trained, the cell is blank. 2. An attendance table that lists the employee names in each row and if they are absent, there is an "A"...

Can Macro read line graphs?
Is there a way macro can read line graphs? I have a graph with temperature along the bottom, weight along the left side and in the the graph area there are several diagonal lines as pressure altitude. By entering the graph at Temperature going straight up into the graph to the correct pressure altitude then move left horizontally left and that will give you the answer (weight). -- WH99 Do you have formulas relating these numbers to each other? It would be straightforward to solve these for the missing variable. Trying to read values off a chart is not going to be easy. Tushar Mehta ha...

can you control how comments print in excel xp or 2003?
I noticed a user at the copier today, using the blow up function to try to enlarge the comments that he had printed out at the end of his excel worksheet. He commented that he could not change the font for the comments. I went back to my desk, and was able to replicate the issue... In short, you can format comments for on-screen viewing (and presumably when you print comments "in place"), but if you print out all the comments at the end of your document, you apparently can't control the font/size/formatting/etc. The only thing I could find that made a different to how it printed...

Which Exchange-aware Anti-Virus can I use ?
Is it not wise to use Symantec AV Corporate edition client (8.00.9374)on Exchange 2003 ? I had to rebuild our Exchange Server and I want to be sure which AV client is best. thanks chrism. WE have been using trend micro for exchange where we have to.... You do not want to be scanning the drives with a 'regular' AV client. "chrism" <chris@no_spam> wrote in message news:%23TD1hNgOFHA.544@TK2MSFTNGP10.phx.gbl... > Is it not wise to use Symantec AV Corporate edition client (8.00.9374)on > Exchange 2003 ? > > I had to rebuild our Exchange Server a...

Outlook express #72
I cannot delete sent emails in the the sent folder. I have no trouble deleting anywhere else. This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlookexpress&SLCID=US&sd=GN&id=fh;en-us;newsgroups microsoft.p...

Report website problems here
I reported the problem last week of the email not working properly. I was asked to send mycomments to the website below using the case number SRX1110134208ID. I recommend alld user to respond to this case number with their comments. I think it is silly that Microsfot doesn't maintain their own website properly and it take almost 2 weeks to fix problems that effect thousand of customers. https://support.microsoft.com/contactus/emailcontact.aspx?scid=sw;en;1389&WS=managermail Were you having email problems with Publisher? -- Mary Sauer http://msauer.mvps.org/ "Joel"...

Query Return Recordset As...
Is it possible to run a query from a form and have the query return its recordset in continuous form format rather than a datasheet? Thanks, Tim tbrogdon@gmail.com wrote: >Is it possible to run a query from a form and have the query return >its recordset in continuous form format rather than a datasheet? That doesn't make sense to me, but you can set a form's DefaulView to Continuous and use the query as its record source. Then the form; will open using the query's data and display it in continuous view. I guess if you want to be tricky about it, you could open an unb...

Reports in column format
My report page set-up is for 3 columns with zero spacing between columns. There is a box around the set of controls. I have the palette narrowed to the absolute minimum width so that there is no working space to the right of my controls. The result is a calendar-like presentation. The problem arises when I want to center text in the headers and footers. If I simply place my header text in the header section, it will appear more or less towards the left side of the page. In order to get it centered, I have to widen the palette and then center the text manually. If I do this, then...