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
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 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 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
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 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
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
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 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 Relatedhello guys
i have values in two unbound textboxes and now i would like to assign the sum to a bound textbox. Also need AfterUpdate event too.
Can any expert help?
Thank you
I created a form letter as an Access 2007 report. I want the greeting to read, "Dear [first_name]," e.g., "Dear Alan,". The [first_name] field is bound to said field in a query. When I just use the [first_name] field, I get "Dear Alan" with no comma. I've tried to add the comma various ways:
"=Trim[first_name] & ",", "=[first_name] & ,", "=Trim([first_name] & ","), etc., etc.
Any addition to the basic field produces the "#Type!" error. I also created labels from the same query using
"=Trim([first_name] & " " & [last_name])",
and that works fine.
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.
In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.
The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.
It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.
The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..