Running Average Quote Based On Part Number
Apr 28, 2005
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
View Replies
ADVERTISEMENT
Nov 19, 2013
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
View 8 Replies
View Related
Sep 26, 2007
I want to create a running or moving average of the most recent 5.
can anyone help here?
see attached file
Mix IDTest Date 7 Day1 Avg of 5Ave 28 Day
SF227
2/1/2007 3870
2/1/2007 2160 5415
2/7/2007 3580 5505
2/7/2007 3510 4955
2/12/2007 2990 32204965
2/19/2007 2800 30085500
2/19/2007 3330 32424920
View 2 Replies
View Related
Aug 8, 2006
I am trying to create a Part Number Database and want it to start at 100000 instead of 1. Can someone tell me how I can get Access to start there?
TIA.
View 4 Replies
View Related
Nov 7, 2006
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
Thank you.
View 2 Replies
View Related
Apr 8, 2008
We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:
Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26
The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).
The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.
View 4 Replies
View Related
Jan 16, 2006
I have a data base with the field named "PartNumber" this is a 13 diget number. How can I search on the last 7 digets only
View 2 Replies
View Related
Oct 19, 2005
I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?
Please help!
View 5 Replies
View Related
Aug 26, 2013
How to query the example in the image below. My problem is:
1. If the student get a mark 49 below in any of their subject they will not be included in the ranking.
2. If all of the student mark is above 49, I need to rank the student base on their average.
View 1 Replies
View Related
Feb 24, 2007
I have a fairly simple database we are using for keeping inventory and new items get added through a form. Is there A way I can prevent a part number and its properties from being entered if that same part number has already been entered at an earlier point in time?
View 5 Replies
View Related
Jul 7, 2014
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
View 5 Replies
View Related
Nov 6, 2013
I want create a from with a "Part Number" text box .
After I enter a Part Number into the text box ,
other text box at below will automatic come out the detail about the Part Number I typed in .
Extra :
my part number is something like this : 00-00000-00 ,
how to convert to input mask ?
View 1 Replies
View Related
Oct 12, 2006
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Any suggestions please?
View 4 Replies
View Related
Mar 26, 2014
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
View 6 Replies
View Related
Jan 27, 2014
Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.
Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on
View 4 Replies
View Related
Aug 23, 2006
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
View 2 Replies
View Related
Jul 15, 2005
Hi,
I have many records in 1 form, is it possible for me to display running number on that form? I can display running number in report by putting textbox and set its control source to =1. Is it possible to do that in form?
any idea? thanx in advance..;)
View 4 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Jan 21, 2014
I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]
If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.
I'd like the format of [DPRNO] to be "dpr YY-XXX"
Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.
So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.
View 12 Replies
View Related
Jan 6, 2005
Hi
How do you run a procedure that activates at a set time?
I want to run a procedure that moves a log file to an archive directory at midnight (or at anytime after this when the PC is next turned on).
Thanks for any help...
Mat
View 1 Replies
View Related
Jul 12, 2005
I am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem.
However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
View 1 Replies
View Related
Nov 12, 2004
I am trying to select the records I want displayed by what category is chosen in the combo box.
Say there are two fields in the combo box: Red / Blue
I want the query to only display the records associated with the Red Category.
If I set the criteria in the query to [Show only Red] and type in Red when the query is run I get blank lines.
Is there an issue when choosing a category from a combo box for the query to be specific on?
My other issue is.... does the problem originate because I am trying to run a query from a table that is being filled in by typing the
information into a form? I wouldn't think so, but I am not sure.
Thanks,
ChrisB37
View 5 Replies
View Related
Oct 30, 2013
I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.
ID Duration Difference
1 30 0
2 45 10
3 15 20
4 60 10
5 15 10
What I would like to achieve is the following:
ID Duration Difference running total
1 30 0 30
2 45 10 85
3 15 20 15
4 60 10 85
5 15 10 110
So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20. I have the following:
Code:
Public Function Cont20(MyVal As Long, MyDif As Long) As Long
Static OldValue As Long
Dim NewValue As Long
If MyDif >= 20 Then
NewValue = MyVal
OldValue = 0
[Code]....
Which I put as Cont20([Duration],[Difference])
The problem seems to be on the return aspect, its not calculating correctly. I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g. In cell A7 the formula would be A6+B7+C7 and so on.
View 6 Replies
View Related
Apr 6, 2012
how I can have a combo box on a form, and once a value is picked, return a query that displays all the data based on that value. I have tried using sub forms and the after update but I cant seem to figure it out.
View 2 Replies
View Related
Nov 3, 2006
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
View 6 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
