How To Tackle Numerous Fields For Calculations
Mar 16, 2012
I am creating a database from an Excel Workbook. Many of the calculated cells are dependent on the values of other calculated cells.There are more than 255 cells for the one sheet so I can't use one table.I was forced to use multiple tables and place the calculation fields in their own tables for the use of their fields in a calculating query, although they won't be seen on any form or report.
What is the best way to relate these tables even though they should really be all part of one table? Should I just use a Cross Join? Will a cross join allow me to properly calculate with a field from an unrelated table? What about an Outer Join?I need to reinforce integrity so that if one set of table data is deleted, so are all the others.I have already tried to place a "Report Number" autonumber field on each table, but it doesn't seem to allow me to reinforce the integrity and gives me, "invalid field name of definition of index or relationship".
View Replies
ADVERTISEMENT
Feb 2, 2015
I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.
The code I'm using is
Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName
[Code] ......
View 4 Replies
View Related
Apr 8, 2007
In my database I have a a date field and Number field with sequential numbering with DMax which will reset to 1 when the year changes. I reset the PC clock to 2008 and checked by entering two records and it works fine. Now my problem is how do I tackle the problem of duplicate records. The DB displays No.1 record from 2007 and another No.1 record from 2008. Is there a way to ask the user to input the year and then the DB takes up only the desired year's records for data entry, search, print etc. Shall be grateful for help.
View 5 Replies
View Related
Jun 28, 2005
Hi there
I need to perform a query that works out how far in advance people book holidays. I have the arrival date and also the booking date, and I think I should be using DateDiff. However, I tried to put it in an existing query and it hasn't worked:
SELECT Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings, DateDiff("d", [Booking Date], [Arrive])
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date])
HAVING (((Year([Start Date]))=2004));
Any help most gratefully received!
View 11 Replies
View Related
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Nov 15, 2006
We have a billing scheme in which we partial-bill ceratain customers based on certain events: 30% when they sign the contract, 30% a number of days later, 30% when we ship the goods and the last 10% upon installation. This is a common process, known in many indistries as 'progress billing.'
What I am trying to do is compare the collection performance on the invoices based upon which trigger sent them. For example, I'd like to find the average and standard deviation (plus some percentiles) of the time it takes to collect invoices that are sent... when we ship, let's say.
I have a table that shows order number, invoice number, invoice date, amount and payment date. So, I can have the order number show up to 4 times with a chronology of invoice dates.
My question is this: How do I group for analysis all the FIRST invoices, the SECOND ones, etc...?
Thanks for any help! I can do this in excel... but I was given this extract of 100000+ lines, and hope to do it all in Access. Thanks again.
View 2 Replies
View Related
Jun 1, 2005
Put simply - when I do a find it displays the first account that matches it, then, when you leave the find screen and hit the "next record" key it simply displays the next account (i.e it no longer matches the search criteria).
So my question is,
How do I search records, then skip through ALL accounts that match the criteria.
example:
100 accounts in a database. 50 accounts are assigned "Mr A" and 50 "Mr B".
Mr A and Mr B sit down to work - hit find, and type in their name.
The form shows 1 account, and when mr A hits next fifty times it should not move on to mr Bs (possibly a "no more matches/records" message?).
I hope this is clear enough - and thanks in advance.
ps - I heard something about one form to search - and a subform to display results - the search criteria is static in form one, where as the subform can be skipped ("next record" pressed). But trust me - Im inexperianced :p and would need a nice newbie friendly description of such technological witchcraft.
View 1 Replies
View Related
Jan 10, 2005
I have a problem that is very tedious. I have 9 separate copies of the same databse (there is a copy of each database for every branch of my company. Each database has small custom features that show what company it belongs to) i was told to make a new feature, and in the making, i had to make 40 new queries, 12 new forms 8 new macros, 12 new macros and 6 new forms. this feature needs to go into all of the databases. As far as i know, im going to need to copy and paste each individual query,form, macro and form. Thats 72 different things i will have to copy and paste into 9 separate databases. Does access provide an easier way to go about this?
any help?
View 4 Replies
View Related
Sep 18, 2013
I have a form where i select via a combo box if a product has been inspected, if it has, additional controls are then made visible.
If Me.Inspection_Completed = "Yes" Then
Me.Date_Inspection_Comp.Visible = True
Me.Date_Inspection_Comp = Me.Dateinsp
Me.Inspector.Visible = True
Me.Qty_Inspected.Visible = True
Me.OK.Visible = True
[Code] ....
I tried to put this in a function so i could call it on load, or on current etc, but cant seem to get it to work, apparently you cannot use the me. in a function.
View 4 Replies
View Related
Jun 8, 2005
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
View 1 Replies
View Related
May 3, 2005
Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
How do i get the query to add up all of those quantities?
View 5 Replies
View Related
Oct 30, 2005
I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!
I have a db with products...
Each product has a price (to customer) and cost (to me the seller)
What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.
The data is held in a table with a record per product with the price and cost..
I want to build in another fieled per record that calculates the margin...
Thanks very much
Jessica
(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)
View 3 Replies
View Related
Nov 15, 2005
Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!
Do I use a certain query?
Hope you can help, Tobi :)
View 2 Replies
View Related
Jul 26, 2006
Hello all,
I'm after some advice. Access is not one of my strong points (even though I use it alot in work!)
Anyway, is it possible to create calculations in access similar to those in excel????
View 2 Replies
View Related
Mar 18, 2008
Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.
I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.
I'm just unsure where and how to do the calculations... any help or suggestions?
Thanks in advance!
View 14 Replies
View Related
Mar 30, 2007
Hi,
I've read plenty of advice warning against performing calculations in tables. What is the best way for me to achieve this.
I need to calculate the product of a number of fields.
Field1 * Field2 * Field3 * Field 4 = Value
Field1 and Field 2 change periodically
Field3 and Field4 change less frequently
I want to be able to perform filter queries using the resulting value
i.e return all items with a value greater than 100
Thanks in advance.
Al
View 2 Replies
View Related
Apr 13, 2008
I'm having a problem with this age formula in the query :
AGE: DateDiff("yyyy",[birth_date],Date()) & " " & " years" & " " & "and" & " " & DateDiff("m",[birth_date],Date()) Mod 12 & " " & "months"
Need your help guys in 2 probs :
1. age calculated is not right.
2. need to compute what w'd be age on the sep 1st 2008 - how do I do it?
Appreciate your help.
Thanks.
View 2 Replies
View Related
Apr 26, 2005
Hi,
I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.
In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.
How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.
I assumed it wouldnt be necessary to have a point field in the table as its deriveable.
Any help urgently needed!!!
View 1 Replies
View Related
Jul 13, 2006
This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks.
For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together.
Sorry for the long post but didn't know how to state it any simpler.
View 10 Replies
View Related
Dec 6, 2007
Hi everyone!
I am a beginner with Access and was wondering if someone would be so kind to give me some pointers
I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.
Thanks
View 2 Replies
View Related
Aug 24, 2005
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
View 6 Replies
View Related
Mar 7, 2006
Hi
I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date
Dim valuation2 As Date
Dim valuation3 As Date
Dim valuation4 As Date
Dim valuation5 As Date
Dim valuation6 As Date
Dim valuation7 As Date
Dim valuation8 As Date
Dim valuation9 As Date
Dim valuation10 As Date
Dim valuation11 As Date
Dim valuation12 As Date
Dim startdate As Double
Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date
startdate = CDbl(Me.Start_Date)
findate = CDbl(Me.Completion_Date)
'calculate evaluation periods
Dim valCount As Double
Dim valDays As Double
valCount = CDbl(Me.Valuation)
valDays = (startdate + ((findate - startdate) / valCount))
'Work on entering valuation dates
Do While valcounter < valCount
Select Case valcounter
Case "1"
Me.valuation1 = startdate
Case "2"
Me.valuation2 = startdate
Case "3"
Me.valuation3 = startdate
Case "4"
Me.valuation4 = startdate
Case "5"
Me.valuation5 = startdate
Case "6"
Me.valuation6 = startdate
Case "7"
Me.valuation7 = startdate
Case "8"
Me.valuation8 = startdate
Case "9"
Me.valuation9 = startdate
Case "10"
Me.valuation10 = startdate
Case "11"
Me.valuation11 = startdate
Case "12"
Me.valuation12 = startdate
End Select
valcounter = valcounter + 1
startdate = startdate + valDays
Loop
' this value is to test the interval period
Me.tdays = valDays
I am new to vb and so ths code is not going to be the best or anywhere near but any help would be great.
Thanks
View 1 Replies
View Related
Apr 7, 2006
Hi Everyone:
I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.
Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.
I appreciate any and all advice.
Thanks!
Darin
View 9 Replies
View Related
Jun 26, 2006
I have a form that includes two basic fields
1. USDateandTimeStarted
2. USDateandTimeFinished.
The fields include both date and time (date and time are in the same field) i.e. “6/26/2006 4:23pm”. The next field I need to calculate the duration, basically one field minus another…anything I’ve tried will only calculate the time difference will not factor in the days. For example, if it has been 52 hours (2 days, 4 hours) it will only say 4 hours. A few of what I’ve already tried are below. And I’ve also tried an elapsed time expression.
Can anyone provide any guidance.. ? Thanks!
=[USdateandtimestarted]-[USdateandtimefinished]
=DateDiff("n",USDateandtimestarted,USdateandtimefinished)
Glad tidings -
Claw
View 6 Replies
View Related
Feb 18, 2007
Hi
I am currently setting up a data base for my work and was wondering if there is any way to do the following calculation:
I have a database of automatic doors that are contantly being repaired and i want to keep a record of the accumulated costs involved with this.
I have set up a form to update each door and its accociated repair cost, the problem is when i enter the new repair cost access replaces the current cost with the new and i want it to be added to the current cost so the accumulated cost can be bulit up over time.
I hope this makes sense
Any help would be greatly appreciated!!:)
View 10 Replies
View Related
Nov 29, 2004
I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
View 5 Replies
View Related
