Modules & VBA :: Calculate Median Value For Group Of Transactions Within The Same Industry?
Oct 15, 2013
I am using the code below to evaluate values for each transaction to determine the median:
Function MedianF(pTable As String, pfield As String, pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Inputs: ? medianF("Values", "Industry") <enter>
'Output: results are currently incorrect
'*******************************************
Dim rs As Recordset
Dim strSQL As String
[code]....
The code generates median results based on the value but not the correct ones, as I check results in Excel using the Median function. I need to see the median for the transactions within the same industry and I do not know how to do that . Below are some sample transactions including the Industry, Value, mymedian (which the current results based on my code) and the expected median result (which I calculated in Excel based solely on the transactions listed here). Note that mymedian results listed in the 3rd column are determined using the complete data set (over 2000 transactions). I only included a few sample transactions below to show that I am currently getting 2 different median results: 44.62 and 50.82. Also be aware that I do not want to evaluate any zero value transactions, which I believe I am handling in the strSQL line above where I indicate <>0 . I need to see a Median result for each industry and the result should be the same for all transactions within the same industry.
Industry Value mymedian Expected Median
Aerospace 18.01 50.82 40.45
Aerospace 53.02 50.82 40.45
Aerospace 109.18 50.82 40.45
Aerospace 61.54 44.62 40.45
[code]....
This is how I am passing the values from the query to the module, passing the value of Period2 if Period 1 = 0, else passing Period1:
I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.
Example (fields in the query) Expr1 Expr2:Median(Expr1)
I've gotten the medianf fuction that is pasted on these boards working in the attached db example. The problem I am running into now is the medianf function (correctly) takes the median for an entire table at once. My dataset example looks like:
typenum t11 t12 t13 t16 t16 t1100 t21 t22 t2200
I'm trying to end up with median t1 = 4.5, t2 = 2. With the way that function works, it sets them both to 3 which is the median of the entire set. I have the query saved in here already setup so far. Any ideas on getting the grouping to work properly?
Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
Newto Access, new to the board. Have searched for median queries and have not found what I am looking for.
I want to incorporate a MEDIAN calculation into a querey. I have 6400+ records, which I need to be grouped by 61 industries. I have suceessuflly grouped and got the MAX, MIN, AVG, etc... and I would now like to add into this grouped query the MEDIAN calculation. Can someone here help a brother out?
I am trying to run queries to determine the median values for different age groups of peoples each with different illnesses. At the moment the database asigns an age group for each person whos age falls into the module determined age brackets, under 16, 16 - 40, 41-50 etc.
For each age group and diagnosis (bronchiectasis, sarcoidosis or pulmonary fibrosis), I need an age distribution chart including the min, max, mean and median.. min max mean are all simple enough and using MS VB median module I can individually calculate medians for each of these. The problem comes when trying to combine these all in one data set.
One solution I found was to combine two reports so I have queries with min, max, mean and then the medians set up beside them. The problem tho with this is that when a recordset returns 0 results (i.e there are no people under 16 with sarcoidosis), the query linked to the report does not return the value of 0 but as they dont exist it does not include it at all. The median fields however return blanks and cos of this the line spacing becomes messed up and the report becomes incoherrant and incorrect.
I think the solution will have something to do with querying, though so far I have not been able to work it out :confused:
I have to write a PHP web front-end to a client-server MS Access applicatin, and I think I've hit a stumbling block.
I use ADOdb as my db abstraction layer, and it has transaction functionality built in. I've tried it with Access, and while according to the output of the code the transaction has worked, I see clearly that it has not worked.
If I insert an intentionally malformed statement as part of the transaction, none of the other queries are rolled back upon the failure of said transaction.
I know this is an Access forum, not a PHP forums, but I've gotten zero response to this post on PHP forums.
Does anyone know if there's a way I can implement transactions for Access via PHP?
I have one table, called tblTransactions, which contains records on security market transactions. For each buy and sell order, respectively it contains one dataset with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following SQL query code gives me then the composition of my security portfolio as per any arbitrarily chosen date (here 1/10/2007):
SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Quantity]) AS TotalQuantity FROM tblTransactions WHERE [tblTransactions].[Date]<=#1/10/2007# GROUP BY [tblTransactions].[Ticker] HAVING sum(tblTransactions.Quantity) >0 ORDER BY [tblTransactions].[Ticker];
For instance, for some trades like the following...
However, I would like to add a column to this query, which gives me the number of trades for each particular stock. For instance, the query result based on the sample data above should look like follows as per 01/20/07:
I am trying to run a transaction in my databases and I’m having problems with rollback. I have an append query and a delete query that I’m trying to run Just a test I begin the transaction, run the queries and then rollback the transaction. For some reason the rollback isn’t working. Please look at my code and let me know what I’m doing wrong. Are transactions not available in DAO? Also, how do you tell if all records were updated – How do you know when to Rollback?
Sub ArchiveRecords() Dim db As DAO.Database Dim qdfArchive As QueryDef Dim qdfDel As QueryDef Set db = CurrentDb Set qdfArchive = db.QueryDefs("qry0201ArchiveLayoffs") Dim cnn As Connection Set cnn = CurrentProject.Connection Set qdfDel = db.QueryDefs("qry0202DelArchiveLayoffs") cnn.BeginTrans qdfArchive.Parameters(0) = 1 qdfArchive.ExecuteqdfDel.Parameters(0) = 1 qdfDel.Execute cnn.RollbackTrans End Sub
I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.
I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.
I do not want to use record by record VB because it is slow and I have a million+ records.
Can anybody help ?
Transactions Table (input)
Account Period Amount 1011100 1015 100
Account Summary (desired output)
Account Period Opening Movement Closing 101 10100100 10121000100 10131000100 10141000100 1015100100200
I have to make a form with multiple sections. Each section has n questions to which corresponds a numerical value from 1 to 4.
Example:
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 2
I have to choose the most recourrent number like the Excel MODA function then in this example is 2.But if there are more then one recurrence with the same times i must choose the highest one.
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 4
In the example above number 2 and 4 recur the same number of times and i must choose the number 4 because is the higher.
There is not a MODA function in Access, even if Excel, with the second example returns the most recurrent but the lower value 2.
I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.
I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)
I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.
Does anyone know of a sample database that I can download to learn some of this from?
I need to create distinct transactions for each invoice, so that if the static data changes, such as rates of charge, it will not automatically update any history, where a historic rate has been charged.My current thinking is that I create a table and effectively archive all invoices that have been created, as they are produced.
i would want my form to calculate the age of a contact by using the inputted birthday, is this possible? and as long as the system clock is right, it should update too? or i'm wrong?
I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;
First hundred should be calculated with 100.00 X 5.79 = 579.00 Second 200.00 X 8.11 = 1622.00 Third 93.00 X 12.33 = 1146.69
Total should be displayed in the amount field = 3347.69
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.
I have this code for calculating the age from birthdate but i want if there is years no months or days appear
Public Function CalcAge(Birthdate As Date) As String Dim intYears As Integer, intMonths As Integer, intDays As Integer intMonths = DateDiff("m", Birthdate, Date) intDays = DateDiff("d", DateAdd("m", intMonths, Birthdate), Date)
I am trying to get a module set-up that will add a number of workdays (no weekends, no holidays) to a date that is mentioned in a form and put the resulting deadline date in another field on the same form (date or remaining days to deadline would be even better).
Now it gets a little tricky, the module will need to select different amounts of workdays to be added to the date in the form depending on what status is selected (different statusses have different amounts of days).
I'll probably need: List of holidays List of days required per status Start date (to which the days can be added).
Example:
Status = "DQ" which has 2 days to work with. Start date in form = "21/03/2014". Result = 25/03/2014 or preferably "2 days remaining".
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code: Option Compare Database Private Sub cmdCal_Click() Dim sql As String Dim rs As Recordset Dim qdef As DAO.QueryDef Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
