I seem to be having some trouble with my unit price. Its not round up correctly.
I currently have this :
Extended Price: CCur(Nz([Quantity]+[QuantityToFollow],0)*[UnitPrice]*(1-[Discount]))
Which comes to £45.74, but the actually price should be £45.72. Its some how rounding up. I think. My current unit price is £7.62 however Access seems to think it is £7.6241.
I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.
Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price
Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?
Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:
1 (hr) / 12 (pay periods) = 8.33, when it should be .0833
0.5 / 12 = 4.166 when it should be .0416
What is right is everything else, starting with 1.5 hrs
1.5 / 12 = 0.125
What do I need to do to make all of the values divide correctly?
I have two tables. Table1 has two monetary value fields (both set with auto decimal places) and table2 has one monetary field. Then I have an append query which appends all data from table1 to table2, except for the monetary fields which are added up and then appended into table2's single monetary field (which also has decimal places set to auto). The formula I use for this is as follows:
Total Value: [Gross Value]+[Adj'mt Value]
Problem is, the total figure appended to table2 is rounded off to the nearest integer, whilst still displaying decimal places (45.00, 201.00 etc). Is there some sort of variable to set in the query to stop it from rounding off the result of this formula?
Any help would be appreciated.
Thanks.
How to set a Default value "0" in Query Extended Price Calculated field ?
I have attached the screenshot with explanation, how to changes the formula.
I have used below following functions but there is no workout.
Extended Price: CCur([Qty]*[BPrice])
Extended Price: CCur(Nz([Qty]*[BPrice],0))
Extended Price: Nz([Qty]*[BPrice],0)
Extended Price: ([Qty]*[BPrice])
Extended Price: IIf(IsError(([Qty]*[BPrice])),0,([Qty]*[BPrice]))
I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...
View 10 Replies View RelatedI have a database that has raw data input to 2 decimal places. There are multiple queries set up to sum the total cost by facility and box type. When the queries are run, the output is to 4 decimal places. I need to round the cost to 2 decimal places. (Right now, it is formatted to show 2 decimal places, but when you click on the number, there can be up to 4 numbers after the decimal.
I have tried to round the numbers within the queries and I get errors asking for parameters. I've also tried to round the numbers in the append queries where I move the final numbers to a summary table. When I do that, I get an error message that the database can't find the table I selected.
I'm not sure how or where it's best to add the rounding function so that my final output is in the format I need.
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies View RelatedAny way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:
Code:
PARTID | Price | Date
--------------+-------------+---------
111223344 | 5 | 3/1/2015
111223344 | 7 | 3/2/2015
111223344 | 8 | 3/4/2015
111223344 | 10 | 11/22/2014
111223344 | 20 | 10/1/2014
111223355 | 5 | 2/5/2015
111223355 | 6 | 2/1/2015
to:
What I want:
Code:
PARTID | avgPrice | MinDate
--------------+----------------+-------------
111223344 | 10 | 10/1/2014
111223355 | 5.5 | 2/1/2015
I know the Round function exists in MSAccess, but what I need is one that will always round up, and never round down!
Easy enough in Excel, but I can't find one in Access?
I have a query expression that calculates the number of years between 2 dates using DateDiff. Here is the equation as it is:
BudgetedHQPCalc: Round(DateDiff("yyyy",[DateHired],[DateTerminated]),1)
What I would like this to do, is if the difference is, for example, 1.4, I want the number rounded down, if its 1.6 I want it rounded up, and if its 1.5, i want to use alternate rounding (1.5 down 2, 2.5 up to 3).
Is there an easy way to do this?
I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.
Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))
I get an error something like "wrong number of arguments or expression on comma.
I atrying to make a query that shows the price for a product, based on two parameters.
Parameter one is a product code.
Parameter two is a date. This date falls between two dates.
I have one list (table) where is product code and invoice date.
The second list (table) I have, contains product code, and price valid from date, and price valid to date columns. This price valid to date is often not filled, and the price I still valid as we speak. If the date is filled there is often a new entry with an updated price. But sometimes, even if there is a new entry in the table, the date 'valid to date' is sometimes also not filled.
I would like Access to show me the valid price for the specific product. What criteria should I give in the macro, in order that Access shows what I want?
For illustration purposes, a small overview of my table:
Product code, Price, valid from, valid to
AAAA, 12000, 01.01.2012, 31.12.2012
BBBB, 16600, 01.01.2012, 12.06.2013
AAAA, 13500, 01.01.2013, 28.08.2013
AAAA, 11500, 29.08.2013,
BBBB, 17600, 13.06.2013,
Product, invoice date, price according to price list
AAAA, 02.05.2012, ????
AAAA, 01.08.2012, ????
BBBB, 10.06.2013, ????
AAAA, 31.10.2013, ????
AAAA, 16.11.2013, ????
If you happen to know how this search can be performed in Excel, I am of course also happy to read that. (But my index, or Vlookup functions, give only the first possible result in the table. As I do not know how to give in the date parameter.)
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
View 5 Replies View RelatedI have a very basic query with the following calculated field in it:
ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
I have tried search through some forums and but still not able to get desire results.
Table Customer Agreed Transport Rate
Customer > Eff Date > Route > Rate
A > 1 June 2014 > AAA > MYR 99
A > 15 Aug 2014 > AAA > MYR 88
A > 1 June 2014 > BBB > MYR 77
A > 1 Sept 2014 > BBB > MYR 66
B > 5 June 2014 > CCC > MYR 88
B > 20 Oct 2014 > DDD > MYR 100
C > 1 Oct 2014 > EEE > MYR 222
If there is order from Customer A for Route AAA loading on 2 July 2014, I can easy to get answer "MYR 99".
How if I wish to make Route as primary key? (as I understand, Primary key must be unique and no repeat).
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
I'm trying to get this query to work:
Code:
SELECT Avg(Round([Final 205].[LastOfSumOfRehLOS],2)) AS AvgInpLOS, Avg(Round([LastOfSumOfWaitLOS],1)) AS AvgWaitTime, Count([Final 205].PHN) AS Observations, [Final 205].LastOfIntensity AS Intensity_Level
FROM [Final 205]
GROUP BY [Final 205].LastOfIntensity;
It doesn't found the result to two decimal places.
Hey guys, i have an access project for my computer class, and considering i dont know much about access, i think i did ok, but i want to make sure everything is the way it is suppose to and do it myself than paying someone else to do it for me.(its some pretty interesting stuff actually) so can you gurus, let me know if what the email from my teacher said is what i have done? i think its just the relationships that might not be done right if anything. i attached the pdf file that says what i must do, and the access file that i made. thanks for all your help in advance guys,
Quote: Originally Posted by Kush's Prof there is nothing to make the relationships work.
you are simply confusing the operational aspect of the database adn the design aspect of it.
When you program an interface to use it then you worry about usade of the relationships.
Setting them up at the design level where you are now, will simply ensure that the relationship is not violated when it is programmed. That is it.
Access Project (rared)
After being a member on this site for a while now I would like to get some opinions on "cost of application development" I know the forum covers a wide geographic area and the members cover a wide spectrum of degrees of expertise on Access and application development. So the question is this..What would your rates be and how would you determine your charges for developing Access applications for a business?PS... Please include your location if not in your profile.
View 13 Replies View RelatedHi
I have a problem which hope will have a simple solution, which has been
driving me mad all day
I have a client who has two prices for the same service depending on speed
of turnaround
These prices are entered into the Client table seperately using the CDetails form.
I then wish to use this information using a combo box for selection within
another form called SDetails.However I can only get the combo box within
SDetails to put one of the two prices rather than either of the two choices,
because of the bound column within the combo.
The combo when pulled down looks something like this
Price 1 Price 2 Client Name
I would prefer for
Price 1
Price 2
so that I have the choice of the price I want to choose.
Having spent time and many brain cells, I have ground myself to a stop.
HELP!!!!!! if only for the sake of my sanity
I am creating a db that handles orders.
My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for 50 whereas to Burger King I am selling them for 35).
Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.
I have setup a relationship between the two tables based on product name
How to I go about looking up the different prices depending on which customer I have placed an order for?
can it be done in a table or do I need to create a query to sort it all out.
I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...
Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1
Thanks for any ideas....
Hi
I am trying to get a blank ribbon to be in place when a user opens my application. I have an Adp linked to SQL Server. If I create a table in SQL Server named ribbons and have the app read the data in that table in the AutoExec macro (this runs the code that reads the data) then assign the new ribbon to Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value it works and the blank ribbon is displayed. The problem is that this is no use because if the adp is not connected to the database on startup it causes an error as no connection has been established.
Instead I have put exactly the same xml file into a module but this does not have the same affect even though I call the same Application.LoadCustomUI method. I havent really done anything different except change how the xml is found. The problem with this method is it just pops the new ribbon into the list of available ribbons so then the user would have to choose the ribbon, shut down and restart, that is no use.
Has anybody got any idea what can be done about this? I will post the code below.
Thank you for any advice on what can be done.
Paul
First method (which works but is no good for distributing the app)
Autoexec calls the following
Public Function LoadRibbons()
Dim RS As Recordset
Set RS = CurrentProject.Connection.Execute("SELECT Ribbons.RibbonName, Ribbons.RibbonXML FROM RIBBONS")
If Not RS.BOF Then
Application.LoadCustomUI RS("RibbonName").Value, RS("RibbonXml").Value
End If
RS.Close
Set RS = Nothing
End Function
but using the following and calling it in autoexec just puts the ribbon in the list and doesnt use it
Function CreateRibbon()
Dim xml As String
xml = _
"<customUI xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & vbCrLf & _
" <ribbon startFromScratch=""true"">" & vbCrLf & _
" <officeMenu>" & vbCrLf & _
" <button idMso=""FileCompactAndRepairDatabase"" visible=""false""/>" & vbCrLf & _
" <button idMso=""FileOpenDatabase"" visible=""false""/>" & vbCrLf & _
" <button idMso=""FileNewDatabase"" visible=""false""/>" & vbCrLf & _
" <splitButton idMso=""FileSaveAsMenuAccess"" visible=""false""/>" & vbCrLf & _
" </officeMenu>" & vbCrLf & _
" </ribbon>" & vbCrLf & _
"</customUI>"
Application.LoadCustomUI "BlankRibbon", xml
End Function
Hello,
I am rather new to Access and looking for some help with my table. I have a report that I do, that I used to do in Excel and was wondering if it was setup right or if there was a better way of doing it.
From looking around I noticed that people usually have mulitple tables and such, while mine is all in one. I see there is a table analyzer but am not sure if I should use that, or what the importance of having it split up.
I have attached my table in XML if anyone wants to share opinions on how they would set it up or what they would do.
Is there a bad thing to having it all in one table?
Thanks for any insight or help.
Hi guys,
I'm trying to create a simlpe database that is use to store time worked for clients (see attached).
If you run the form frmClientEntry i have two issues.
1 - you will see that the title is automatically selected from the combo box. However is i try and enter the form I'm told 'You cannot add or change a record because a related record is required in table 'tblTitle'. If I select one of the entries I get rid of this. I would like the default to be selected...
2 - There is no entry in the hourly rate column field even though there is an entry in the table. How do I get my hourly rate to show? Ideally this will only be a single text box and not a comboBox as the hourly rate will be the same for every client.
If this is a table design issue please let me know and I'll post there.
Thanks