Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
Hey i have a percent field in a query which displays correctly in the datasheet view, but when make the report the percent is just a long row of numbers :confused:
anyone got any suggestions? would be greatly appreciated
I searched everywhere for a solution to my problem. I have to create a field and set its format property to Percent using code. I know how to create the field using code but how do i get to Format property? Is that possible? Thanks in advance for your answers!
i normally just go into design view in my query to change the format type, but some of my subqueries are quite large, so i'm getting 'the expression exceeds the 1,024 character limit for the query design grid.
i figured i could just multiply the subquery by 100, but the results are still calculated out to about ten decimal places.
so... how do i format a subquery directly in sql to be a percentage with two decimal places?
How can format proper display for percentage values. What happens is I chose a field as percent, but if I put in 9 meaning 9 % I get 900% as the value is multiplied by 100, but if I put in 0.09 I get 0%. I just want to put in day 9 and be saved as 9%.
Hi, in my query I am calculating a NET PRICE starting from the GROSS PRICE and the DISCOUNT % (Percent field). The Net Price shows incorrectly an amount which contains several decimals "invented" at the right end. For example, in this moment I have an item with Gross Price = 576,09 and Discount % = 10,00%. The Discount Amount shown by the query is 57,6090049743652 while the correct result should be 57,609. Therefore the Net Price is incorrect as well. Any thoughts about this ? Is there any known problem with the PERCENT fields ? Thanks and regards. Alberto
This is for the purchasing guy at my client's office. He has 9 vendors he wants to track their on time delivery performance. The data comes from the purchase order tables in his accounting software, to which I've linked via ODBC.
So far so good. I've created a query to pull only those 9 vendors from his total population, then ask for the date range to query by date for the date range (start and end date), and use an expression to calculate the difference between the date wanted and date received so he can tell if the delivery was on time or not. (ex: date wanted: 3/10/06 date received 3/13/06. 3 days difference = late delivery)
The twist is he wants another field so he can mark whether he considers the delivery late or not depending on extenuating circumstances he keeps locked up in his cranium, despite what the date difference results tell him. So, I created a combo box and he can select "Yes" or "No." If he selects YES, a value of 1 is written to the table in that field, if he selects NO, a value of 2 is written to the same field.
So, I need the query to group by vendor, count the total number of entries for that month by that vendor, count the number of "1"s and express that total as a percent of total deliveries.
To summarize, vendor A had 8 deliveries, 5 were on time, meaning he had a 62.5% delivery score. Vendor B had 10 deliveries, 7 on time, meaning a 70% delivery score.
Sorry for the long winded explanation, but perhaps someone can assist. All help appreciated.
I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.
One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:
Code:
SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc FROM tbl_OSRImport WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));
This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:
In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%
Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032
I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.
I'm enrolling medical centers in a new multi-center database. There are a number of steps that must be completed before the center is enrolled in the project. I have a form that serves as a checklist. I want the textbox at the bottom of the form to display the percentage of steps completed. I guess you would have to count the number of fields on the form that are filled in, but I don't know how to do it.
ID | NAME | Progresscomplete | Release 1 | a | 20% | July 2 | b | 65% | July 3 | c | 33% | July 4 | h | 15% | Sept 5 | i | 30% | Sept 6 | r | 5% | Dec 7 | s | 50% | Dec 8 | t | 15% | Dec 9 | u | 20% | Dec
now I want to create a query that shows the Progresscomplete for each task as a percentage for a Release
so the total progress for each release is July 300% Sept 200% Dec 400%
and the current level of progress for each release is July 39.33% (118/300*100) Sept 22.5 % (45/200*100) Dec 22.5 % (90/400*100)
thus the current level of progress for each task within a release is 1 | a | 16.9% (20/118*100) | July 2 | b | 55% (65/118*100) | July 3 | c | 28% (33/118*100) | July 4 | h | 33% (15/45*100) | Sept 5 | i | 66% (30/45*100) | Sept 6 | r | 5% (5/90*100) | Dec 7 | s | 55% (50/90*100) | Dec 8 | t | 16.6% (15/90*100) | Dec 9 | u | 22% (20/90*100) | Dec
I have queries for the first two not sure about the last also not sure if i should have the base of the last output be the current level of progress for each release or the total progress for each release
I have tried this for the last output
SELECT ID, NAME, ((Progresscomplete/Count(Progresscomplete))*100) AS currentprogresslevel, Release FROM TEST_RawData GROUP BY Release;
but get an error "tried to execute a query that does not include specified expression... as part of an aggregate function.
I have a report that takes money values form several records and sums them up in various category's making a list of departments with the total amount of balance for each (positive and negative numbers). I need to categorize the positive values form the negative and calculate the totals of each and the grand final total (invoice style)
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
What I need to do is to pull four subsets of data out of this table -
1) A unique list of courses that student x is taking in semester 1 (only) of a given year 2) A unique list of courses that student x is taking in semester 2 (only) of a given year 3) A unique list of courses that student x is taking over 2 semesters (both semester 1 and semester 2 of a given year) 4) A unique list of courses that student x is taking over 2 semesters (semester 2 of a given year and semester 1 of the following year)
Obviously there should be no overlap... and that's where I get a bit stuck
I can craft a simple query that will show me the courses a student is taking in semester 1 of a given year, but I cannot figure out how to get it to exclude those courses which are also still being taken in semester 2. In the above example, the query pulls out records 2 and 4 for student 2; I only want record 4.
I think what I need is to have 2 queries -
a) pulls out all courses for student x in semester 1 for the current year b) pulls out all courses for student x in semester 2 for the current year
- and then subtract the results of query (b) from query (a) (ie remove the rows that are matched). Is there any way to do something like that?
Or is there a simple part of query syntax that I can use to make a query that just says "pull out all the courses for student x where semester=1 and there is no row for this course and this student where semester=2"?
Im trying to run a query that looks in a table to see if product a and product b were purchased and then return the id but i never get any data, and I know there are some results that should be showing up. If I remove either one of the products it returns data but not when both are requested. here's the query: SELECT [Copy Of data].id, data.purchase_product, data.purchase_product FROM data INNER JOIN [Copy Of data] ON data.email = [Copy Of data].email WHERE (((data.purchase_product)="ProdA") AND ((data.purchase_product)="ProdB"));
I have been working on customizing MS ACCESS to produce a report that involves one mathematical computation from my input data. However, I've run into a problem because I can't get the program to carry out a logic command that would work fine in MS Excel: the "IF" command. Specifically, what I'm doing involves a calculation, where one data input is subtracted from another data input, but if the difference is a negative number, I would like to substitute the value of ZERO. The closest I've come to achieving this is to enter the condition ">0" while in Query Design mode, but the problem with this approach is that if the difference is a negative number, the report does not display the data that resulted in the calculation of the negative number. In other words, I want the data to be included in the report, but I want a "Zero" to be substituted for all calculations that produce a negative number. For example, if I enter a set of data, and the two numbers that get subtracted are 40 minus 36, then the difference is positive 4, and so I want a "4" to be displayed; But if the two numbers to be subtracted are 36 minus 40, the result is negative, and so I'd want the report to display a "zero" in the calculation. Please advise me on how to set this up, as the only approach I've found resulted in all negative calculations being omitted from the report. Thanks for your help.
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
I need some help with code. I can do this in Excel, but I am not sure how to do it in Access.
Here are the fields:
PoundsPerBox PoundsPerOrder BoxesToOrder
Here is the scenario: If a customer orders custom paint for their equipment, we need to total how many pounds of Powder Coat paint it takes to paint all the units (PoundsPerOrder). When we order this from the vendor we have to order it by the box which is measured in pounds. In this example, the vendor will sell us a box of white Poweder Coat paint in increments of 55 pounds. (55, 110, 165,...)
Verbally, the logic reads like this: If PoundsPerOrder is less than or equal to PoundsPerBox, then BoxesToOrder equals 1. This logic needs to be tested for each increment value (indefinately) of PoundsPerBox.
Am I making sense? How do I accomplish this in Access? And, as a side note, I need to capture and store this value in a table for future reference.
I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.
My question therefore; is the above possible? How would I go about linking this logic with the subreport
Tried to export a report to Excel using Tools>Office Links>Analyze It With Microsoft Excel menu. The order of the fields appearing in Excel doesn't seem to match the order on the Access report layout. What is the logic on how the fields exported? Thanks.
CompanyTbl: CompanyID (PK) Name Street etc. NAICS1(this is a standard industrial code, a 6 digit number telling about their product/service) NAICS2 NAICS...6 (company can have 1-6 NAICS codes)
A layman has no clue what these NAICS code are, so each and one of them has a descriptive title, like NAICS 32330 = Canned Vegetables Manufacturing For this puropse, I designed a second table:
ProductTbl ProductTitle (PK) NAICScode
Purpose:1. Be able to query by ProductTitle and get all companies with that profile. 2. Be able to retrieve Company ID with all its NAICS code/s and corresponding ProductTitle/s. Now one company can have up to 6 NAICS codes and one NAICS code can appear under the descirption of multiple companies. So a many-to-many relationship. But how? A third table, probably....But how?
In the highlighted line of code attached I am comparing two strings one from a snapshot recordset and one from an array.
The logic fails, you can see in the watches window both values equal "DESIGN and the elseif statement should execute but it does not.
The only reason I can see for this is that the values are equal to "DESIGN and not DESIGN. Where this single quotation mark comes from I do not know. It is not visible in the tables and it does not appear when the values are printed into excel.
I am new to this forum as I recently started working in MS Access. I’m hoping someone can please help me with this query I’m trying to do using MS Access 2007.
This task is somewhat similar to a typical points/miles rewards credit card program.
The problem is as follow:
For every $2,000 of accrued purchases in a credit card, an individual will get a gift card.
So, any remaining balances over $2,000 will rollover and be added to next set of transactions, until it reaches $2,000 again. For instance, if on the first week I spent $2,500, I would qualify for my first gift card. The excess $500 would rollover and be added to the next set of transactions until they reach $2,000 again. Therefore, this would qualify me for another gift card.
So far, I’ve created two tables, one that stores accrued purchases (Promo History) and another table based on an Append Query which takes all qualified transactions from the first table and appends those records to the second table (Promo History Qualified) in order to track those qualified transactions over $2,000.
However, I’m stuck on how I would accommodate the remaining balances to be carried over. Any suggestions would be greatly appreciated!!