Queries :: Table Of Expenses - Too Many Digits After Decimal
Jul 27, 2013
I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I am using only three fields: the quarter, expense type and amount paid. I am using - Qtr: DatePart("q",[PostDate]) - to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: "3.0026" It should not have four digits after the decimal.
Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))
The result of this was a curious information box that said: "At most one record can be returned by this subquery." The query returned nothing. I have checked the formatting in both the table and the query itself and the format is "Standard" for both. So, I can't figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal.
View Replies
ADVERTISEMENT
Dec 21, 2007
I have a table in which i have fields that have numbers with a huge number of digits after the decimal. i.e 10.2356491876545.... when i change the properties of the field to show only two digits. it does display two digits but the number is still stored in its original format. Is there anway i can chop off the unwanted digits forever so that they are not stored in the table anymore.
View 3 Replies
View Related
Apr 1, 2013
I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods
This is how it should look like.
Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc
I made the following query to get the result I wanted.
Unfortunately, the result seems to be multiplied instead of summarized.
View 3 Replies
View Related
Nov 6, 2006
Hi,
I'm having problems getting my head around the best way to design a table for an employees expenses. This is my current M_Expenses table:
ExpenseID (PK)
PayrateID
ExpenseType
Amount
PayrateID is the foreign key to a table that holds information about their rates, evertime etc.
My idea was that the user would only choose an expense if the employee were entitled to that expense - so if someone was not entitled to flight expenses the record would not be created. However my boss says that if he were to look at the report produced from this he wouldn't know whether the person putting in the data just didn't have the information at the time or it didn't apply to that employee. So I will need to let the users know that they must create a record for each.
Also the report must list all of the expenses:
Per diem expense
Accommodation
Car hire
Flight
Other expenses
They need to be listed in this order and must appear whether that particular employee is entitled to them or not. I don't see how I can do this as it is because my subreport that lists the expenses is a continuous form and they are not listed in alphabetical order. Plus only expenses that the user has chosen will appear.
The only other way I can see to do it is redesign my table as follows:
ExpenseID
PerDiem
Accommodation
CarHire
Flights
OtherExpenseType
OtherExpenseAmount
But that doesn't seem like good design?
View 1 Replies
View Related
May 15, 2013
I am trying to find the best way to compare estimated expenses to actual expenses in Access. I am working with vessel and port call information, so each port call has a series of costs associated with it. I have two tables, each with multiple fields (around 100). Each of the fields contains a cost type that goes to a particular cost code. Each row of the table is associated with one particular port call. The call is estimated right after it happens, but the invoices are not all recieved for up to three months. Once all of the invoices for this port call have been recieved, we enter the actual expenses for the call. I am trying to find the best way to analyze the costs through finding the variances for one particular port call for each code, and also for multiple port calls to see which cost item has the largest/smallest variance for further investigation into how to estimate it more accurlately. I am thinking that I may have to move into Excel to do this, but would like to stay in Access if possible.
View 4 Replies
View Related
Jun 3, 2013
I am trying to create an access db to monitor my expenses for my new Snack house.
I have created a "Main" table which will be holding all the daily expenses entries one the fields is called "Type" in which i formatted as combo box with the following values (vegetables , butchery , bakery).
Another field is called "Item" which should list the items based on the Type selection.
Knowing i have a second table in which i stored all types and their corresponding items , how can i force the filed "item" in table Main to simply display the ones i need based on the "Type" input.
View 1 Replies
View Related
Sep 17, 2014
I have an Access 2010 table which shows Latitude and Longitude. However instead of it being in a decimal format it is displayed as 4000000000. I need to add a decimal point so that it looks like 40.00000000. If I configure the Field Properties to Decimal with a scale of eight and decimal places set to eight I can manually convert all of the data to the correct format. But I would like to have this happen automatically from a query.
View 4 Replies
View Related
Feb 25, 2014
I have about ten append queries to group various financial data to one main table.
I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.
What is the better approach to have only 2 decimal places for all append data?
View 4 Replies
View Related
Sep 15, 2015
I have a 9 digit ID that is inputted on a form and stored in a table but the 0 keeps getting dropped.
I am using an input mask similar to another db but it is not working for this one
mask I am using is 000000000;0;_
So for ID's that start with 0 (EX:012345678) would display and stored as 12345678 instead...
View 2 Replies
View Related
May 8, 2013
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
View 4 Replies
View Related
Jul 14, 2005
I'm just to work with Fox Pro, and I am therefore puzzled with how to define the exact number of digits in a field in a table in Access. If I need 9 digits + 2 decimals in a number filed, how do I define that in design view?
Also, I need to know how to export a table into a text file, with a format without any spaces, and each record is divided with a new line. This an old IBM text format file.
Thanks, Torsan
View 1 Replies
View Related
Jan 5, 2014
I have a table where there is information stored about certain workorders.. like location, description, etc.
The thing is that location is descripted as "03-LZ-.." where the 03 part stands for the departement. I have another table with all the numbers and departements and now I'm looking for a way to link the two. So I have to find a way to look at the first to digits of the "location" in my workorder table and link that number with the table where numbers and departements are stored.
View 2 Replies
View Related
Jun 2, 2005
Hi there,
I am not sure the best way to do this,
I have a field with values that look like: 1.69553366385509 or 0.0061705541157596
I want these values to look like: 1.6955 or 0.0062 (4 decimal places [rounded up if possible])
Thanks for your help with this
Sue
View 5 Replies
View Related
Aug 20, 2013
I 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.
View 2 Replies
View Related
Jul 5, 2014
I am creating query that calculates time interval so that this is used to calculate pay. Employees clock in at [time_in] and clock out at [time_out]. Sometimes time_out is on the next day of time_in.So if is substract time_out - time_in I will get a negative number.I am therefore using the following formula to extract the time interval; Interval: Format([time_out]-[time_in],"Short Time")
this gives out a result such as 3:30 when in fact I would need 3.5 to calculate pay by multiplying with rate.How do I convert 3:30 to 3.5. From the searches I get that I need to multiple 3:30 by 24. But when in put done in the query Interval: Format([time_out]-[time_in],"Short Time")*24
View 8 Replies
View Related
Feb 14, 2014
I'm exporting a query that has currency values in with no decimal places but when it's viewed in excel it has 2 decimal places! I've changed the properties in the query to none, how to stop this from happening? I'm using 2003.
View 1 Replies
View Related
Dec 23, 2014
I have a query result for example = 2.19 days worked. The result of cumulated hours divided by 8 (hours in the workday). What I want to do is take the .19 and multiply it by 8 (hours in the workday) and I would have the result in actual hours which is what I need. Or of course if there is a way to display that into 2 days and 1.52 hours Rounded up to quarters would be even better. But with spending hours looking through forums I didn't find a way to just work with the decimals right of the dot. Trim, format, Len didn't work for me, they all give me funny result.
View 7 Replies
View Related
Dec 22, 2014
I want to calculate decimal hours duration taken for a job starting before and finishing after midnight.
I have something that works but it seems cumbersome.
Hour(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))
+ (Minute(Format([Job]![Start_time]-1-[Job]![Finish_Time],"Short Time"))/60)
View 11 Replies
View Related
May 1, 2015
I need to export a currency field to include the pence but without the decimal place, is this possible?
It also needs to be 11 characters wide.
I have tried a number of formats and can get the decimal point removed but I am not getting the pence displayed.
View 5 Replies
View Related
Jan 28, 2008
This is probably an easy question, but I can't figure out how to get a table to show numbers in 2 decimals points when they are inputted with 2-5 decimal points. I changed the DATA TYPE to Number and DECIMAL PLACES to 2, but when I do that, it just rounds it to a whole number. Please help, thanks!
View 6 Replies
View Related
May 9, 2013
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
View 2 Replies
View Related
Apr 23, 2013
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.
View 1 Replies
View Related
Apr 23, 2015
I'm working on a utilization database and pulling Excel data from Cognos 8. I use Access formulas to separate hours and minutes because I want to use time (.30, .45 (minutes)) instead of quarterly increments (.25, .50). I've successfully segregated hours, because I also have to turn days into hours. When I try to calculate out a column just for minutes, my formula keeps the number of days and I don't know how to fix it so the column just shows the minutes.
An example is a job has a decimal time of 2.02083333, which when done correctly is 48 hours and 30 minutes. I get the 48 hours correct, but the minutes shows as 2.30 instead of 0.30. That inflates the total job time to 50.30. This is the calculation currently in my database to pull out minutes:
Minutes: Int(([Min]60)24) & "." & Format([Min] Mod 60,"00").
How do I get rid of the '2' in the 2.30?
Access 2010
View 2 Replies
View Related
Aug 9, 2007
i want my data to have only 2 decimal places. So i change the property of the table to only allow 2 decimal places and i did the same to the report and they are still all over the place....for example a field would say 8.12233444 and another 1.17.....what do you think is the problem?
View 3 Replies
View Related
Feb 20, 2014
I'm setting up a table that has several percentage fields, for which I want to display 3 decimal places in datasheet view (and later in forms and reports). I've tried to set up the fields in a variety of ways, but either I don't get a percent format, or I get 3 decimals with 2 zeros at the end. For example, 73.913% shows up as 73.900%. This, of course, causes problems in calculations, so that for a total percent field I'm getting things like 99.900% instead of 100.000%. I've tried various combinations of formatting properties for these fields (Field Size, Format, Decimal Places, and Scale) but I have yet to hit on the right one to simply display the correct value in the format of a percent.
View 8 Replies
View Related
Oct 12, 2004
I have a database that uses the SSN as the primary key, but only the last 4 digits of the SSN need to be shown on the reports that are generated. I can't figure out how to use the Left function to extract only the last 4 digits. For example if a SSN were: 123-45-6789 I would want the field to read ***-**-6789, or just 6789 whichever is easiest. I would appreciate any help that could be given.
View 3 Replies
View Related
