I'm needing to total 8 records with the same id number, but different amounts in the the field named 'amounts'. For instant, if the amount field had:
23
23
25
67
45
23
88
97
I need a formula to total the above figures so it can equal 391.
I kinda new to access but What I need to happen is I have a Bill Table, and I have a search query, you type in the customers last name and the different services they request come up. I have it do a report, at the bottom I want the Cost field to add up and display total cost. any help would be apreciated...
thanks,
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
I have an access table call "Department" and two fields calls "DSec" and "DCode"
like this
DSec --------DCode
50 --------70.1587
60 ---------80.6987
80 ---------60.8521
70 ---------50.1512I like to make a query so that
DSec -------DCode
50 --------50.1512
60 --------60.8521
80 -------80.6987
70 -------70.1587Please Help
Thanks
All,
I have inheirited a database which I am attempting to query via an SQL statement:
"SELECT * FROM MYTABLE ORDER BY SheetNumber;"
This query works fine under Access but when executed thru ADO 2.5 / Microsoft.Jet.OLEDB.4.0 from VB6 it reports the following error ("2147217833"):
"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
First things first, I'm not trying to add/insert/paste any data (see SQL statement above). Secondly, when I remove "ORDER BY SheetNumber" from the SQL statement the error does not occur. I need to retain the ability to sort and would be grateful for any help/suggestions.
For your reference SheetNumber is defined as a 20 char Text field in Access.
Thanks in advance,
Rob Black.
My DVD library database I have a memo field which has the description of the movies.
In some cases, like my tv show records, I have a lot of data because of the episodes and episode descriptions.
I'm currently displaying this data using a split form with the datasheet on the left
I must use a small font in the memo field in order to display all data.
I could use a larger font and simply scroll but I'm looking for an alternative.
I've even thought of creating a hyperlink filed and point to the movie on IMDB's web site. But when I click the link nothing happens even though I copied and pasted the link into the field.
I currently have a drop down box with four options in there and the options are:
Low
Minimum
Medium
High
However when i change the options in the table to:
Low
Moderate
Significant
High
it comes up with the error message: the field is too small the accept the amount of data you attemtpted to add. Try inserting or pasting less data.
I do not understand what is wrong with the second option of words? The maximum for characters is set to 255 so i am not sure why it is coming up with that error message.
I have the following query, which runs perfectly:
SELECT
[qry_BMO_District_Recap - RHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - RHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - RHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - RHS - Stage 2].AREANAME, [qry_BMO_District_Recap - RHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - RHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - LHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
When I try to reverse the above, as shown
SELECT
[qry_BMO_District_Recap - LHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - LHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - LHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - LHS - Stage 2].AREANAME, [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - LHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - RHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
I get the error message "The field is too small to accept the amount of data you are trying to add. try inserting or pasting less data." I'm not attempting to run an insert or update query.
Access Help lives up to expectations by just repeating the error message, and the posts I can find on the forum which mention this error all seem to include something about a maximum of 254 characters in a field. None of mine are anywhere near that amount.
Does anyone have any idea what could be causing this?
Hi guys,
I have a prob with summing up my subtotal figure in my query. Here goes:
I have an Orderdetails Table with : Qty,Unit Price,Discount.
That makes my total in query "Total:[qty]*[unit price]*(1-[discount]). However, I can't use the SUM to sum up my "totals" as subtotal. How am I going to remedy this problem? THanks
Hi there hope you can help :)
I have created a form with a subform and has the following information displayed:
(Form)
Tenant ID Mobile Telephone Number
Title Credit Reference
Surname
First Name
(Subform)
Date Due Amount Paid On
(Form) Total Of Arrears -
I need text box at the bottom of the form to caculate each tenants arrears.
To show if the tennant has any arrears there will be no date shown in the [Paid On] box (If there is a date in this box it means the tenant has paid and therefore no arrears) so the [Amount] for that tenant owes will be totaled up in the box at the bottom of the page [Total of Arrears]. Also a tenant might miss a payment on one week and a pay another rent payment on time. Please could you show how to do this in access and not by VB code.
I've attached my database - well thanks for looking and hope you can help
HeadintheClouds
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
View 1 Replies View RelatedNot exactly sure if a query is what I need in this situation or if it is what I need how to get there.
In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case
So clv1 might be done by User A
Then clv2 might be done by User B
But the next case it might be switched.
I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range
Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.
Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?
Well the db example is imb so it cannot be attached
Thanks for any help.
I have a continuous subform (frmLabor) that has an unbound box in the footer. This unbound box totals all the numbers in the Total1 field using DSum. However, when I change the record in the main form (frmCustomers), that number still stays there, and when I add records (service hours) to the subform under a new record (customer) in the outer form, those numbers sum. I understand what's wrong, I just don't know how to fix it. How do I get it to calculate ONLY the records (service hours) in the subform per record (customer) in the outer form?
-Jason
I have a form that has 7 text boxes, each of which contains a time value (hh:mm)
Two of them are to be permanently totaled into one text box (lets call it Rate1), and the other five are to be totaled into another text box (lets call it Rate2) - BUT, if the value of a combo above each one of the five text boxes is set to 'BH', I want the value to be totaled in with the Rate1 totals.
I did think about doing an After Update event on each of the five textboxes where the value of the totals text box is updated by doing the current value + the value in the textbox (and the totals text box that is updated is dependent on the value of the combo) - but that idea landed flat on its face upon the realisation that if a user entered a value, then decided to change that value, the old value would not be deleted from the total.
is this even possible and if yes how do you write a report (or query for the report) to do:
201030 = 1 spring semester
201050 = 1 summer semester
201070 = 1 fall semester
so then attendance from 201030 till 201230 = 7 semesters
and also going the other way so 201230 - 201030 = 7 semesters
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
we have a calculated field in an append query Line_Cost which eventually creates a purchase order.
We have to have a minimum order value of £1500.00, so, we need to total the values before running the append query.
I have a theoretical solution to making a basic form for an Access Database, but how can I create a form from the VB IDE?
The toolbox cannot be selected from the dropdown menu.
I have 3 small tables in the database and am trying to create a form which will enable me to update a total on one of them, but as I say I can't even make the basic form, let alone program it.
Please advise.
Hello all,
I am a new registration chair person for a small convention for 200-250 persons.
I would like to set up a database that I can access to print out address labels and name tags, by selected specific cells or sections.
Is there a way to do this with a Microsoft Database or Spreadsheet programs?
Any help would be appreciated
Thanks
Leneda
Hi people...:)
I create a form with 2 buttoms
The first running in VBA and the second In MACRo
Why the MACRO buttom don't work?
Thanx in ADVANCE...
Johann
I am just beginning to design a small data base, where end users will mainly use an Access Form to search and view data.
The (lab) data will be arriving to our group occasionally over the next 2 years, and will be put into an Excel file (formatted the same, each time). The new data could be appended on the end of a master file...so I could link just one file to a table in Access. I need to build a Form, based on this data.
But, I am not sure if I should link the file from the beginning (since I've read that I cannot change data types or field sizes, if I link)....
Maybe I should import one existing file, manipulate the table parameters as needed, then design my Form....then link later?????? :confused:
what would you do in this situation?
thanks!
I am very new to Access, I do work for different companies inputting data.
I usually use excel and then I have to retype the information into another database.
One of my companies is a school. I have to print certificates when the class is complete, but what I'd like to know is,
is there a way I can take the names of the students and using Access just have the names appear on the certificates without having to retype all of the names individually?
I would appreciate any information you can provide for me, this sure would save lots of time.
I hope I explained my question clear enough.
Tim
(this is a repost )
I will skip the basic intro of "Hi im new to access and i dont know.."
anyways, what I am trying to search for and I dont know what Im looking for is this.
Q1) I would like to be able to open a zip file, read the NFO/txt file from within and import certain areas of the NFO file,the NFO file of course is layed out with ascii art but there is a predetermined area where certain info is obtained. for example: I would like to capture/import the URL, testers name, date avaliable, zip file name.THEN the text imported would be written to the database. It would be nice to figure out how you go about importing certain lines but im not sure how to ask the question and find the answer the right way. ie...(ascii art found here http://www.ascii-art.de/)
-= I did search import text and get text and didnt find what I was looking for=-
Q2) How do I write to an ascii file/nfo/txt using access, there is a template or a layout that is pre done,and i need certain fields filled out, just like they above layout.
thanks for all your help, you guys are awsome, and keep up the good work!
I am looking to basically update the hyperlink base every time the database is opened to be the folder that the database is currently in.
We are trying to make the database and supporting information portable, so that people can copy the entire folder anywhere on their computer and all of the links will still work. I can't put all of the info in the database because we are already pushing the size limit.
I would like to keep it from getting terribly complicated, as others will have to update this database with more info in the future and make the links themselves.
If there is a better or different way of getting the same result without going through the hyperlink base, that is fine.
Any help would be appreciated.
Thanks.
I want to create a new data base from an existing one in Microsoft Access. I want to use all the fields but not the data. Can I do this? I also want to keep the form that I created. Can anyone give me suggestions? Thank You!
View 1 Replies View Related