I have a query statement:
Final Tax Free Amount: IIf(Left([Final Tax Code],1)="k",Right([Final Tax Code],Len([Final Tax Code])-1)*-10-9,Val([Final Tax Code])*10+9)
If I wanted to include: If final tax code = BR or NT then = 0. How can I add this to this statement?
thanks
Hey everyone,
I have a form that acts as a survey asking 11 questions total. Each field in my table is called material1, material2, ect. I have a total field in my table that I would like to be the sum and was told that I have to do this through a query. My only problem is when i query it only gives me the sum of Question 1, 2 ect and does total them all. Can someone nudge me in the right direction as I am relatively new to this. i appreciate everyones help on this forum.
I have a query which returns selected rows from my table, one of the columns in the table is price.
I want to total the price into a text box on the form and run the query in the 'on current' event of the form so it recalculates depending on the record
can anyone give me a pointer please?
thanks
Hi all,
i have a table called expense that i have tied to a case table... i also want this tied to the employee table, but when i add the employeeID field to the expense table it messes up all my queries that i had previously created... the queries merely reference the expense amount that is located within the expense table... is there something that i am doing wrong? (obviously there is) but i would like to know why this is happening
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName
FROM ItemBatch
WHERE (((ItemBatch.Finished)=False)
AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
Just a quick question.
is better to use a query for a form or just link straight to table.
e.g you have tblColour, this table has ColourID & Colour.should you build a query including all fields then build a form from that query?
Thanks
I have a table with 3 fields... ID, Name, Hours
ID is NOT unique in the table
IDs Can have different names
Hours is what I will use to find the total hours
I want my query to group by ID only, and show the SumofHours
BUT... I want to show the name as well(without Grouping by it), I want my query to Show me the name that is MOST Recurring.
So lets say that ID '1' occurs four times, with the Names...
'Mark' 'John' 'David' 'John'...
Running the Query I would want to see 'John' under the name field Right Next to the now Unique ID '1'
At first I thought I could just use the Max function from the drop down menu, but that only uses the one that is alphabetically a maximum... I need it to be a maximum by count..
Any help would appreciated,
Ayyad
hi there
could somebody help in the query design in order to query table 1 (image attached), by showing the maximum weight for each component_id, but also include other details such as the date when the maximum occurred and the event_id, the result as query 2 (image attached)
if i just select component_id and weight, i can do this by selecting max weight, but when i add the date and / or the event_id columns then i get duplicates in the component_id column
any help would be greatly appreciated
Thanks for helping me out with this question. Im sure this is pretty easy to do. I have a query with a few paramaters set up... one of them runs a custom function all i want to do is this
query one
Average water flow:Avg([WaterFlow])
when run this just shows me the average water flow for the database... now if i have another criteria i want to base off the average how do i get the query to realize that. for example it would look something like this
[Average water flow]/[currentFlow]
how do i reference another criteria? hopefully this isnt to hard to understand.
hi, i have a form which shows all of the transaction that have happened in the business, a field in the form is 'logged by'. The 'logged by' field records the name of the person who completed the transaction. For monitoring the employees performance i have created a query which shows all of the transactions each employee has done, ie there is a promt for the users name to be typed in, then all there transactions are brought up. I now want to produce a report off this, but with the total of transactions the particular employee has done at the top of each page of the report. Basically i want the value it says in the record selectors at the bottom of the record, the total number of each employees transactions. I have tried doing a calculation in the query, adding the amount of auto numbers up, but that doesnt work. Not really sure where i should start from..any tips? :confused:
View 1 Replies View RelatedMy update function does not always work properly, especially when one of the fields is 0 or blank.How could i improve my function so that when addding wih 0 not to give 0 ?
My function is the following:
Public Function dummy()
StrSQL = " UPDATE (products1 INNER JOIN products ON products1.Productid = products.Productid) INNER JOIN [Order Details1] ON " & _
" products1.Productid = [Order Details1].productid SET products1.items1 = [order details1].[quantity]+[products1].[items1]"
End Function
I've a access table like below
I want to make a query which will first findout the duration of time by "Starting-Ending" and then add the result according to ID. For example below
ID Duration
1 17:05
2 14.30
3 7:00
I have a form. Is there a way when you click the record selector at the bottom of a form it will out increment the text box.
heres what id like to do:
txtnum = 21
hit record selector.
goto record 2
txtnum = 22
hit record selector
goto record 3
txtnum = 23
Is this possible? Also these are new records that the record selector is choosing. So they will be blank.
Hi all,
I have a table with the number of existing staff per department separated by their corporate level. I am able to use a query to display the number of staff per month(Mth1=ExistingStaff..so on).
Since there will be staff changes such as promotions or new staff additions, I need to cater for that. Thus, I created separate tables for each case.(tblNewStaff & tblReclassStaff) The idea is to use a form to allow the users to choose which staff to promote (reclassification in corporate level)where the user just has to specify which month the change is in. A separate form is used to add new staff.
However, I am having problems trying to figure out how to make changes to the Number of Staff per month after such changes. New staff are added as new records while reclassifications will alter the current numbers. Both has been dogging me.
Is there a way to dynamically generate this through a query? Or is there a better way to go about doing this? The numbers collected will be used to calculate incremental salary etc...
Thanks for any help! My brain is fried after trying to solve this problem the past two days...
Hey guys, I'm trying to set up a small inventory database. On my form i want the user to be able to add and subtract from the item amount by typing in the text box a number, if the number is positive then i want it to add it to the table's existing number, if the number is negative then i want it to reduce my existing number. The number in the table will be contained in a different text box on the form. I'm sure i need to do some sort of expression builder, preferably upon enter. and i'm not sure if the form would need to refresh for the number to update? Could any of you guide me to the correct path? Thanks in advance -John
PS oh after it updates i would like it to clear the text box and then redirect focus to a different box but i think that is just basic coding
I have records that go by name(cfr_Name) with a charge amount(cfr_charge). Now the charge can have up to 5 diiferent charges per name. Most likely it will be the same charge 5 times. i dont want access to add the 5 charge amounts i want it to dispaly one value. So if it is 20.00 display 20.00. But if the person appears again with 5 records and a charge of 30.00 i want it to display the persons name and 50.00? so i am adding only two values not 10. If the charge field has a value of 20.00 for 5 records the vaule is 20.00. so if one set of 5 records has a value of 20.00 and another set has 30.00 the total in query would be 50.00
View 3 Replies View RelatedI want to have a dropdown box in a form but allow additions to be made, but after they are made, instead of them not showing up in the box like usual, I want them to become a part of the list. Plz help! Thanks
View 1 Replies View Relatedi have two tables - all fields are same in two tables.
Table1.salary, Table1.Interest, Table1.Bonus
Table2.salary, Table2.Interest, Table2.Bonus
now i want to add these values and put in a thirs table.
Table3.salary=Table1.salary+Table2.salary
Table3.Interest=Table1.Interest+Table2.Interest
Table3.Bonus=Table1.Bonus+Table2.Bonus
pls help me.
can i do it with help of Query, Macro or any other method.
i have two tables - all fields are same in two tables.
Table1.salary, Table1.Interest, Table1.Bonus
Table2.salary, Table2.Interest, Table2.Bonus
now i want to add these values and put in a third table.
Table3.salary=Table1.salary+Table2.salary
Table3.Interest=Table1.Interest+Table2.Interest
Table3.Bonus=Table1.Bonus+Table2.Bonus
pls help me.
can i do it with help of Query, Macro or any other method.
Hi all,i am running a query that (as part of it) returns a total time.. using a formula i found on this site. this formula uses a start and end time and works wonders. works!IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60my problem however is an option that i need my users to have to enter a time not based on a start end time, but a "OtherTime" if u will. (ie they have 4 hours instead of writeing 1:00 - 5:00) already changed this from default value null to 0... (which i didnt wanna have to do cuz it messes up the query)Doesnt Work(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+[OtherTime]i want the total time in the query to show the 4 hours, but it is leaving it blank. i am guessing that it is because my start end times are blank(null) is there a way to add a if statement into a query expression that will bypass this? Tried, but thats invalid in a query expressionTotalTime: If IsNull [StartTime] Then =[OtherTime]Else IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60+[OtherTime] end if
View 2 Replies View RelatedHello again...
I got almost everything working on the form except for the Grand Total feature.
I tried the
=[field1]+[field2]+[field3]+[field4]+[field5]+[field6]+[field7]
and so on and so forth to the point it would add all the fields up into a grand total.
The only problem I run into is it won't add them up unless there is at least a value of $0.00 in the field. Which makes it look VERY cluttered since on average only 1/3 of the forum will be used regularly.
Is there any way to make it show the grand total even if a field is blank?
I am new to MS Access, and I am wondering if there is a way for Access to automatically send an email to a defined address everytime a record is added to a specific table? I have created an escalated issue table and I would need an employees manager to be notified automatically when a new record has been added to it.
Please help
Thanks
I have a table which has a primary key of 3 fields. Each individual fields is defined with duplicates OK but I want to block a record being added where the primary key value exists already. How can this be done - the way I have set up the database Access does not automatically stop this as I thought it might. Do I need somw vb also??
View 14 Replies View RelatedI have a split design form, where user can select on any row and column of the spreadsheet view of the form and edit the data. Now, I want to write a code to limit the data addition(Row addition) to a certain number. But I still want to be able to edit the previous records added to the form.
I used Me.AllowAdditions = False, but when I want to select the previous records, it gives error:"You cannot go to specified record".
I created an option group with 4 options when designing a form. Now I need to add 3 more options in same group, but I can not find any way of doing so. Similaralily I need to delete one option in another form.
Please help
I have a replica at work, 1 copy on the network, 1 local.. I need to add a column to the main table, getting "Operation is not supported for this type of object".... I have users that use the database but was given a change to the form/report which requires a column addition. What is the simplest way to get around this??? I'm trying not to get into major problems.
View 1 Replies View Related