Sr.NO Name Amt.Tot Amt.Needed
1 Mark 100 24
2 Mark 100 80
3 Tom 150 12
4 Tom 150 45
5 Joe 50 23
Need to add a field which is Amount.LEft so that the table looks like this
Sr.NO Name Amt.Tot Amt.Needed Amt.Left
1 Mark 100 24 76
2 Mark 100 80 -4
3 Tom 150 12 138
4 Tom 150 45 93
5 Joe 50 23 27
The last field is Amt.Tot - Amt.Needed and this should roll over and subtract for all transactions of Mark, then restart again for Tom and then again for Joe. Let me know if there is any confusion on that.
I have a table comprised of/grouped by agency, payer source and billing date summing dollars the order/display of the data is the same as the grouping; that is, ordered by agency, payer source, and billing date. The billing date is just every Tuesday of every week. Every once in a while, there is a negative value in terms of dollars for a particular agency/payer source/billing date category. When this happens, a voucher isn't created and in billings that follow that 'rolling negative' amount must be accounted for. Is there a way in which I can produce a value on the line that adds the rolling negatives from prior weeks if they exist? An example is below:
Agency Payer Source Date of Billing Dollars Amt to Release ABC Patient 1/28/13 $500 $500 *full amt releases because no rolling negatives ABC Patient 2/04/13 $600 $600 *full amt releases because no rolling negatives ABC Patient 2/11/13 $-200 *nothing released because negative amt ABC Patient 2/19/13 $300 $100 *$300 - $200 released because a negative occurred in prior week
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code: SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num] FROM LIST_WITH_TNC;
I wanted to know if it is possible to set a rolling year based on the first recorded date for an individual. In other words, my table is updated every time an employee receives a point. The record includes the employee's name (empName), the date (dateOfOccurrence), and the point (occurrencePoint).
I would like to create a query that would be based on a rolling year from the very first record of an employee based on the first dateOfOccurrence. The first date/record of this employee would be the start of the rolling year. I am writing this in SQL in Access 2013.
(1) When I click on the combo box this month, the first value should be 2015-07 (yyyy-mm) and the last one should be 2016-06 (2) When I click on the combo box next month, the first value should be 2015-08 (yyyy-mm) and the last one should be 2016-07.
The combo box will check today's month and will automatically generate the 12 rolling months (include this month).
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.
From this table I want to create a rolling total so that the rolling gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?
E.g. Rolling sum of clients in OCT07 = No. of Clients (NOV06+DEC06+JAN07+ ... +SEP07+OCT07) Rolling sum of clients in JUN07 = No. of Clients (JUL06+AUG06+ ... +MAY07+JUN07)
The problem is that the output has to be a table/query (a complete list) with following data: - Product - Period - Gross Rev (prenst) - Clients (present) - Rolling Gross Rev (last 11 month + the present) - Rolling Clients (last 11 month + the present)
The table is to be exportet to Excell and used in a pivot.
I have tried using the Dsum function but I have only managed to calculate a running total (not based on the last 12 months). Can somebody help me here?
I have developed a new database using Access 2003, which I need to roll out across various users. Some have XP, some Windows 7, and each of them has different size screens. I guess I will have to adjust the various forms to size to a particular users screen resolutions and sizes.
Is there any way of doing this within the database itself, or do I have have to adjust each form according to the local users PC specification?
I have a query that takes a date from a selection form. Is there a way to window my data by taking this date and going back 12 months to give me a block of data covering this 12 month window.
Example: Date entered is 03/dd/06 Date window desired is 04/01/05 thru 03/14/06
Say for Material A: I need Access to see that the 1st date is 05/01/2013 and say 365 days out or 12 months (05/01/2013-04/01/2014) the average is 158.6 then 06/2013-05/2014 the average is 146.667 and so on.
Every month is not going to be listed in the results, if there is no month then assume 0.
I have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls; 12-Month Rolling cboMonth - Month Combobox txtYear - User entered year Date Range txtStartDate - User entered Start Date txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
If blnDateRange Then Me.txtQryStart = Me.txtStartDate Me.txtQryEnd = Me.txtEndDate End If
If blnMonth Then If Me.cboMonth = 12 Then EndMonth = 1 EndYear = Me.txtYear + 1 StartMonth = EndMonth StartYear = Me.txtYear Else EndMonth = Me.cboMonth + 1 EndYear = Me.txtYear StartMonth = EndMonth + 1 StartYear = Me.txtYear - 1 End If Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1 Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1) End If
Now I can use one query to accomplish both types of query; SELECT UsageID, dtUsage, dblUsage FROM tblUsage WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this? :confused:
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions. The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
I have a db, and in developing it, I've created hundreds and hundreds of queries. Now as I am learning, I see that I can create queries "on the fly", with code like:
Dim cnnl As ADODB.Connection Set cnnl = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = cnnl
Dim mySQL As String mySQL = "SELECT *" mySQL = mySQL + " FROM tblCustomers" mySQL = mySQL + " WHERE ((CustomerID = 1 ))" myRecordSet.Open mySQL
Set myRecordSet = Nothing Set cnnl = Nothing
My question is, do you think it's better to code the query on the fly like this, or to create them as permanent ACCESS objects, as I have done?
My feeling is, that the ADODB method is probably more efficient, and if I can delete those many existing queries, and create recordsets on the fly, only when needed, the db would be a LOT smaller, in terms of MB, becuse of all the objects I'll be able to delete from the Queries window. Or, are they relatively insignificant in terms of MB?
Or, should I adopt the "IF IT AIN'T BROKE DONT FIX IT" approach, because all those queries do work, and there are huge numbers of forms dependant on them.
The size IS a factor, the db is over 50MB now, and I'd like to optimize it.
Hello everyone. I have a few issues with access. I hope someone can help me. Here is my dilemma:
I currently use a database in ACCESS. In this database is a table of stored values. I currently run a single query called “Basic Search” to find certain values of the fields. I click the “!” button to execute and my values are presented to me.
Each time I want to find the value of certain fields, I have to open access, find the query, delete the last value that was in the “Basic Search” under the “criteria” field and search again.
Is it possible to create a single search web page on my client pc (not a server) that will access the database and display the results I requested? Also, is there a way to design a “Form” type page in access that I can use to run the query? All help is appreciated.
How can I get around not being able to create multiple statements in a single query? For example, I tried to do this:
DELETE * FROM UniqueDates; INSERT INTO uniqueDates ( Entry_Date ) SELECT entry_date FROM [SELECT Entry_Date FROM BarcelonaInternalActions UNION SELECT Entry_Date FROM QPTActions UNION SELECT Entry_Date FROM TokyoInternalActions UNION SELECT FORMAT(Entry_Date , "Short Date") FROM RDActions ]. AS U;
but I received an error. I am trying to clear out a database and insert new records every time I run this query. I think maybe a better option would be to append any new records into the database rather than deleted and inserting a fresh batch of records. What do you think?
Does anyone know of a way that I could search all of my queries and VBA Code for specific columns? We add and remove columns all the time and our reports and forms crash when we miss taking them all out. We have so many that it is very hard to do manually.
