Hi to all!
I face the following problem: I cannot add amounts per line!
I have one table consisting of many different columns (fields), some of them having amounts. I want to update this table by doing the following:
let's say I have the following records:
1/6/2006 A 12
1/5/2006 B 20
15/6/2006 C 21
22/6/2006 D 17
25/6/2006 E 30.
I want to use a filter (e.g. the date being between 1/6/2006-30/6/2006), sort the records descending according to the amounts and then add in another field of the table A, B or C, according to whether the total amount including the specific record is above 70% of the total or above 90% of the total. In the specific example I want the following result:
25/6/2006 E 30 A
15/6/2006 C 21 A
22/6/2006 D 17 B
1/6/2006 A 12 C
Their total amount is 30+21+17+12=80 and the percentage of each record is: E:0.375 , C:0.2625 , D:0.2125 and A:0.15. Their adding percentage is
E:0.375 , E+C:0.6375 , E+C+D:0.85 and E+C+D+A:1.
How can I do that? I calculate the percentage of each record in a query, but how can I calculate their adding percentage, so that I can use an if-clause in an appending query?
Please help!!
Thanks in advance for your time
I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%
I need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.
In this case, I want to calculate a total for an order. If the LineTaxExempt field is checked (meaning tax exempt), the total is figured by the ([Qty] field X [Price] field)+the [ShippingHandling] field.
If the LineTaxExempt is not checked, then the above total, including shipping, is multiplied by 6% sales tax or 1.06. I then want this value rounded up to 2 decimal places. This control is a running total of the order.
This works most of the time, but I have run into a few rounding errors.
For example:
With exempt checked
1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me $34.35 instead of $34.34
With tax exempt checked (and multiple lines)
2(Qty)X141.02(Price) + 0.00(ShippingHandling) 10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a grand total of $323.25 instead of $323.24
It's frustrating because it seems like it works part of the time and rounds wrong the other part of the time. By "rounds wrong", I mean I set it wrong to round.
Is there a better formula to use in a form or query to round up to 2 decimal places other than the =-Int(-Sum([Fieldname]) * 100) / 100 method that will give me the right results?
Any help/examples would be greatly appreciated on how to modify this to give me the correct result each time.
Here are my field types:
QTY ------- Field: Double Format: Standard Decimal Places: 2
Price ---------- Format: Currency Decimal Places: 4 (some unit prices are 4 digits--Example $1.2525)
I have a form based on a query. This form has a combobox with 4 names in it. When a person is selected form the combo box, other fields automatically populate IE: room number, address, etc. Another field is a currency box which also populates automatically.
This is what I wish to happen: When I select a person from the combo box, can it show the TOTAL amount received for THAT PERSON in a separate text box?
Hi, it's me again, the guy who works with the Amusement ride industry. I'm trying to set up a way to ogranize all our saftey bulletins that we get.
I've already looked into indexing but the major problem is that the user will need to be able to search on multiple criteria (for example the Tilt A Whirl was made by three different manufacturers). Also I can't download that nifty google tool bar because we're running win 2000 without service pac 3.
Access seemed like a great alernative, but I have about 300 or so files I need to link. Is there a faster way of linking these files than going through and doing them one by one?
I have a pre-made index the guy before me made in exel. Most all the files are in PDF format if it helps at all. Thanks.
i have 5 fields facid custid empid amount invdate.
i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year) the date range totals, last yeardate range current ytd, last ytd
i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query. i've used dsum() but not quite getting the results i should. i think one of the problems is the user has the option of filtering the data based on the 3 fields.
anyways if you can help i'd greatly appreciate it..
possible to have an update query to only update 20 rows with a new data in the fields? And, how do I pass the information that I want to update only data from a certain date.If I have the word Cat and want to update it to Dog, but only with the dates 12-01-2014, where do I need to put the date part (also possible to do this for dates that are greater than 12-01-2014).
I've run into a situation with our Access Database where sometimes when we import information into it from an Excel sheet the dollar amounts get rounded out. For example, the amount of $726.68 shows as that in the database but when you click on the field/cell it's in it shows as $726.6799. It doesn't do this for every field which is weird. The data from the excel sheet is not roudned out either, it shows the amount as $726.68 so it appears to be something funny going on with Access. Under the formatting area, the data type is set as currency and format is currency.
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
I've a quick question. In Access, I have a form that allows user to add new record into a table. Is there anyway of finding whether the new record has successfully been inserted or added?
So i think it may be wise to have a message to notify us of whether it's inserted successfully or not.
Can someone please tell me why I might be adding a record everytime I open my database. I have to forms that work from querys if I open the main form all is well , if I open the other oone first and then the main form I find a record has been added. Most annoying! :(
I have a table of vacinations and need to add a new record every time an animal is vacinated. I have created a form with all the fields in the table in it. But when I open it it takes me to the first record. I have had to add a button to the form to add a new record. This button then takes me to a blank form where i can input data. This is very messy and not very user friendly. Any suggestions on how i can tidy this up would be appreciated greatly.
I have looked on here for hours and cannot find anything, perhaps someone can help. I have just converted from 97 to 2003 and now I cannot add new records to my forms. I have checked that the allowAddtions property is set to Yes but the New record icon and menu option is grayed out. I have looked through all the form settings and I can't see what it might be.
I have a form that has a list of textboxes linked to table properties, and a subform linked to the same table. I used the command button wizard to create an add record command, but when i click on it it comes up with:
You cant go to the specified record You may be at the end of a record set
i have checked the properties on the forms and querys and set all data entry to yes What should i do?
I have a form that has a list box with events in it. On the same form I have two combo boxes, each with the same information; officials names. I added a subform to form1 and I want this form to store and display the information that is chosen(officials and events). How do I make this happen? It seems easy, but I just can't wrap my finger around it.
When adding a new record via a form, how do you make the 'new' record become a 'proper' record? I.e., add the record to the table? I know it is added when you exit that particular record on the form but I would like to update the table with the details of the new record without exiting the record on the form. I had always assumed this was done with the save command but this does not work.
I'll start with the general question ... I am using code to grab a file name from a local drive and waat to create a record with the detail of the file. what code will enable me to grab the file name from a loop function and stuff the value in a table. i have tried
[code] DoCmd.GoToRecord , , acNewRec
it does not add the file name store in a variable MyName in the table tblFiles in the field txtFileName i have designated nor does the code create a new record. what am I missing - is there a good reference for this?
I have 2 tables and a query to show the details of both tables on a form for an attendance database. The idea is that whenever someone is absent they have a record created.
tbl[Staff] has the following fields StaffID,Name,Team,Job Title tbl[Absence] has the following fields StaffID, abStartDate,abEndDate,abIssue
I can set up the query to find the member of staff by name but I want to be able to use this form to add new records to the Absence table for each individual. eg I use the query to search for "Joe Bloggs" and the form shows the details from staff table and then use the form to add details to the Absence table.
I have two related tables - one is study information and one is person information. For each study, a person is assigned. On my form, I have a combo box to choose a person to add to a study. This works fine when the person exists already. To add a new person, I have an "add" command button that brings up a form to add a new person to the table. Problem is when I close the form, I'd have to find the person in the combo box to add them to the study. Is there a way to add a "person record" to the person table and when I return to the study form, have that new person already associated with the study? I don't allow users to edit the person info from the study form (the same person may be associated with different studies). Any ideas to help? Thank you.
