Hi.. I am a beginner using access and there is probably an easy solution for this which i dont know about!
I have made a query where i calculate the tax deductions for a payroll system. my problem is that different workers are to be taxed differently given maritial status and number of kids as well as net income.
I have managed to calculate everything with the kids and wifes but now i need to sort the table after how much they earn to deduct taxes. I have a "first stage NetPay field" now, where i can see the Gross pay minus allowances for wifes and kids.
Now, if that value(first stage netpay) is under 8840 no more deductions are to occur. If the value is between 8840 and 10000 i have to deduct 15% of the excess above 8840
If the value is between 10000 and 20000 i have to deduct a further 174+ 25% of excess over 10000
If the value is above 20000 i have to deduct 2674+ 31% of excess over 20000
So far what i have done is make a new coloumn, where i take "first stage netpay field" and subtract 8840. If the value is negative i thought i could show only the positive values by enterin >0 in the criteria field for the query, this however will make no posts show up..
(I tried entering Abs() in the Field coloumn where i made the expression, and this worked. )
So my question is: is there a function like Abs() i could use that would convert negative values into 0?
Or do any of you guys have a better idea of how to solve this?
I am trying to to find a way to show all the positive values with nagative, in order words, if the value is 10,000 is should be followed by corresponding -10,000 so that the user could investigate if the entry is a reversible GL.
I'm doing a bank-rec database, so on the one hand I'm comparing positive debits, to negative credits. How do I query to get, say, "Bank1 -$1000, Bank2 $1000"
I have created an Access 2010 Database for tracking inventory. As parts move to each location, quantities automatically subtract from the previous location. I now realized that some parts are destroyed.
The problem is the if I simply enter a negative value to indicated that a part was destroyed at one location, it appears as a positive value in the previous location. Is there a way to make negative values not affect a specific query?
I need to create a query in Microsoft Access to enter 1 or 0 in a field based on whether numbers in another field is positive or negative. Here are the details.
1 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a positive number 0 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a negative number
Hi guys, I need urgent help on a simple question. I hope you will be able to help me. I have a table CustomerMaster which stores customer number and name. (Customermaster) I have another table which stores the product details for customer. (customerProduct) I have another table which shall store order details. (CustomerOrders) On the form, the user selects a customernumber, the system then displays the customer name. The user then picks up the product ordered by customer (picked from combo box). On the combobox on recordsource I have given a select query which fetches the records matching the customer selected from table Now I want the Product Price, tax and duty of the product to be displayed for the selected product. Somehow, I have to again make that as a combobox and then select the price (although there is only one record... Can anyone tell me how to do this? Regards K
My Combo Box is tied to a table with many entries. When I click on it to display the table values it takes several seconds to display. Is there any way I can "pre-load" the Combo Box (such as on form load, etc.) so when I click on it it displays more quickly? Or is there another way to speed it up?
I'm sorry if this is obvious, but I have asked local professionals and scoured this site for answers before posting...
I have a database that I'm using to store and analyze language samples (basically, the mistakes that students make when trying to speak in another language). For each bit of language, I want to be able to say what domain the mistake belongs to - e.g. the 'clause', 'phrase', or 'word' domain. I have a table containing the five domains. I also have a table for each of these domains, containing the specific types of mistakes relevant to that domain. It is conceivable, but unlikely, that I will ever add to the domain table, but highly likely that I will add types of mistakes in the five related tables.
As I analyze bits of language, I will be appending each analysis (record) to a table as shown in the attached image. So far so good. The part that I cannot fathom, though, is how to display (let alone perform calculations on) this data: While the ErrorDomainID is a foreign key to the ErrorDomain table, the ErrorUnitID could refer to one of five tables, so that an ErrorUnitID of 2, for example, could refer to one thing if the Domain is 1 but quite another if the domain is 1 or 5 or whatever.
I've tried all kinds of coding and SQL workarounds, but I have a feeling I'm just missing something very basic here. If anyone could offer some guidance, I'd be extremely grateful!
I had a text box in a contacts DB form for the State field (Named txtState). It worked fine. I decided to replace it with a combo box. Now not only will it not display the existing values, but to rub salt in the wound it won't allow me to select from the drop down list. The error message that says that the field is too small for the value. I tried changing each of the yes/no properties one at a time but none of them change the outcome.
I have created a table (data entered/selected via a form) which includes a drop down box, for 'Ethnicity' to pick a value from another table (ie used Lookup wizard to for options from another table).
The row source looks like this:
SELECT [Lookup_Tbl_ethnicity].[Code], [Lookup_Tbl_ethnicity].[Category], [Lookup_Tbl_ethnicity].[Sub-category] FROM Lookup_Tbl_ethnicity ORDER BY [Code];
Setting the ORDER BY [Code] displays the options in the drop down box in the correct sensible order.
The 'Bound Column' is column 3 - Sub-category. I assumed that this would be the 'value' stored, searched and displayed... but the table (and related form) display the Code.
how to change this so that the Sub-category is displayed?
I have a subform that is displayed as a datasheet, and the source of this data is from another database. Essentially, it shows someone's name and status, but the status is displayed as a number. The people using the database don't know what this value means, so I was asked to display the label instead.
The values still need to remain in the database where the data resides, but can I use a lookup table or some other mechanism so that the label is displayed in the database the users are viewing? How exactly do I do this?
i.e...the data below resides in Database A but is displayed in Database B: Col1 | Col2 | Col3 John | Smith | 2 Jane | Doe | 1
And in Database B only, it should show as: Col1 | Col2 | Col3 John | Smith | Inactive Jane | Doe | Active
Note that the users of Database B will not be updating Col3 data, they will just be viewing it.
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.
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
I'm trying to sort and filter a continuous form. I want to sort by descending and ascending, but I also want to have a combo box for filtering. For example, there a combo box for sorting with Value List as the RowSourceType, the list would be "Ascending" and "Descending", and there should be another combo box to choose the field to be sorted with RowSourceType as Field List from a query.
My field names are: "LastName", "FirstName", "MiddleName". But I want them to appear as "Last Name:, "First Name", "Middle Name". I want to be able to change the display of the field names within my combo box which should come from my query. How is that possible? I already tried to put caption in the properties but it didn't work.
I don't know if it's possible, but is there any way of forcing the random autonumber generator in tables to be a positive number only?
The autonumber as I've searched on the forum can be a positive or negative number and I'd like to use the random autonumber feature as a primary key for account numbers for my database, but not using the negative numbers as they will look like odd account numbers with the leading negative.
I could possibly remove the leading negative, but there's then the chance that you'll have duplicates.
I have a calculated field which sums the quantity field for all transactions by the part#, lot# and location. (A part number could be in several different locations and lots and are treated sperately - if they are in the same location with the same lot number they are summed).
To add quantity the user enters the data using a form. To issue (subtract) the user uses a minus number (eg -20). The calculated field sums all these and outputs the current total.
How can I make sure this is always positive.
Ie. When the user does an issue the qty they specify will be less than or equal to the current total according to the sum calculation.
I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:
The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
I have a report which is counting and totalling numbers, but in the numbers show as negatives - I need them to show as positives.I managed to change the total in one counting field to positive but I've forgotten how - or where I put the code....
I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select.
I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck.
I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.
This is the formula that I am using in my query:
PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100
