Update Qry (running Sum)

May 22, 2006

I need to update a ytd-budget field using the curmo-budget field. The table contains one record for each month. The ytd-budget for Jan would be the same as curmo-bud for Jan. Feb's ytd would be the sum of Jan + Feb...etc. Is there a way to update the ytd fields with a 'running sum' feature as found in the report options?
(... unfamiliar with VB). Thanks

View Replies


ADVERTISEMENT

Running More Than One Sql Update Query

Aug 26, 2005

I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.

I can save a little time using SQL update queries like this:

update personnel set Department = "Support Staff" where Jobtitle="Shipper"
update personnel set Department = "Maintenance" where Jobtitle="Mechanic"
update personnel set Department = "Regional" where Jobtitle="HR Manager"

I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.

What would be the best way to talk Access into running all the queries, without having to paste in every one individually?

Thanks...
...df

View 2 Replies View Related

Compare Value Before Running Update Query

Aug 31, 2006

Hi all
I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this.
Thanks in advance
Craig

View 2 Replies View Related

Update Query Is Running Too Slow

Feb 13, 2008

What would be a better way to do below query --



UPDATE (tblAFFIRMATION_REC_TOOL LEFT JOIN tbl_TZero_Spns ON tblAFFIRMATION_REC_TOOL.CptySPN = tbl_TZero_Spns.SPN) INNER JOIN Entity ON tblAFFIRMATION_REC_TOOL.ReferenceEntity = Entity.ReferenceEntity

SET tblAFFIRMATION_REC_TOOL.[Scope Reason] = IIf(IsNull(tbl_TZero_Spns!SPN) Or (Entity!Test='EM'),tblAFFIRMATION_REC_TOOL![Scope Reason],'TZero Trade')


WHERE ((([tblAFFIRMATION_REC_TOOL]![Scope Reason])="Affirmation Eligible"));




the way it right now, it's running for about half hour..
Could that be IIF statement that slows it down ?

View 1 Replies View Related

Running A Update Stock Control Query From A Button

May 20, 2005

Hi

I am a relatively new user to access and wondered if you could help me with a stock control problem.

I have created an update query (qryUpdateQuantityinStock) with the below fields:

QuantityinStock
tblProduct
Update: [QuantityinStock]-Forms!frmCustomerOrder![frmProductOrderLine Subform].Form!Quantity

ProductNumber
tblPrdocut
Criteria: [Forms]![frmCustomerOrder]![frmProductOrderLine Subform].[Form]![ProductNumber]

When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.

The above query does work if you add the following code to the beforeupdate event of the orders subform:
If Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.openquery "qryupdateQuantityinStock"
DoCmd. Setwarnings True
End If

Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.

I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.

Can you help me find a way to run the query from a button?

I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.

Any help would be greatly appreciated (sorry for the long post!). :)

Many thanks

View 1 Replies View Related

Queries :: Pop Up Messages When Running Update Query / Macro

Jan 1, 2015

I have 3 different "buttons" on my form that run individual update queries. I know these update queries are running correctly. Decided to make a macro that will allow me to run all these queries by clicking one macro button.

My question is: Is there some way to stop the pop up msg that advises I am about to run an update query and the next pop up msg that informs me of how many rows I am going to update?

These pop up boxes are starting to drive me crazy when I run the macro.

View 5 Replies View Related

Modules & VBA :: Way Of Looping Through Continuous Form Recordset And Running Update On Each Line?

Jun 11, 2014

i have set up an update query. is there a way of looping through a continuous form record set and running the update on each line?

View 2 Replies View Related

Forms :: Create A Message Box To Flash On Screen To Tell User That Update Code Is Running

Mar 6, 2014

I need to create a message box or a form or something to flash on the screen to tell the user that a piece of "Update" code is running. the update code will be updated reports from marketing returns, but the 3 branches who use the information are separate so I am creating an update form to download and update the table.

The code for the update is already working, but can take a while, so I thought a message or splash screen would be useful as the update runs on start up.

It would have another use, I have a report which is made mainly of calculated fields on an onPrint event and also takes a while to work it out, so a similar screen would be more useful than my current spinning circle and blank screen.

View 1 Replies View Related

Modules & VBA :: Running Update Query On Record And Have Form Show Updated Record

Jan 26, 2015

i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?

View 4 Replies View Related

Running Balance As Opposed To Running Total

Mar 14, 2005

Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".

I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!

Many thanks in advance.
Peter

View 2 Replies View Related

Running SQL From Another SQL

Oct 8, 2005

Hi

I want to open a DAO.Recordset from an opened DAO.Recordset.

My code is like this

Dim db As DAO.Database
Dim rs, rsMarks As DAO.Recordset
Dim strSQL, strMarks As String

Set db = CurrentDb()

strSQL = "SELECT Name, SurName, Marks from Table1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

strMarks = "Select Sum(Marks) As MarksSum from rs"
Set rsMarks = db.OpenRecordset(strMarks, dbOpenSnapshot)

rs.MoveFirst
Me.txtName = rs!Name
Me.txtMarks = rs!Marks
Me.txtSurName = rs!SurName

rsMarks.MoveFirst
Me.txtMarksSum = rsSur!MarksSum

rs.Close
rsSur.Close
db.Close
Set rs = Nothing
Set rsSur = Nothing
Set db = Nothing

But I got an error message “The Microsoft Jet Database engine can not find the input table or query ‘rs’. Make sure input table or query exist”

While without this second 'rsMarks' my first SQL 'rs' works well

I will be grateful if any one help me.

Regards

Rahulgty

View 7 Replies View Related

Running Sum

May 15, 2006

Is there a function for cumulative row for use in querys (calculated field) or in a code - something like "running sum" in reports?

View 2 Replies View Related

Running Sum Help

Apr 12, 2005

Forgive the Bonehead question. I know this is probably easy, and may have been answered before, but I'm under the gun and trying to get this done quickly.

I have a form that is populated from a table. Each record has a Yes/No value (called "Matched") and a $ amount (called "PaymentAmount"). When they open the form, all the "Matched" records are a No (or False) value. I want a Text Box called "Total_Matched" to keep a running total of all "PaymentAmount" when they check the "Matched" field.

Does this make sense?

I have done a query that gives a sum of PaymentAmount from the table where all values are "True", but I can't get it to requery each time they check the boxes. And I can get a Total of all values on the form (=Sum([PaymentAmount]) but I can't make it contingent on if the Matched field is true.

Any help is greatly appreciated. I'm off to search some more.

Thanks.

View 1 Replies View Related

Slow Running

Jun 24, 2005

I am using Access 2003 but it is to slow, what can I do?

View 1 Replies View Related

Running Nero Api

Jan 23, 2006

Hi Folks,

I want to automate the Nero burner from a Microsoft Office Access Database. I have the code to close the DB and then open the nero program, but i dont know how to actually run a complete burning action from the same click of a button in the DB. I'm working from a macro at the moment but open to suggestions... batch files etc

Cheers Oldtimer

View 1 Replies View Related

Is Database Running?

Aug 2, 2006

Question:
Is it possible to see if a database has stopped running (errored out, timed out, or if code builder is open) thru another database. I know how to look for the .LDB file to see if the database is open...but is it possible to know if it's broken?

The reason I ask is that I have a database on a pc (can't be put on a network) that updates/refreshes every 5 minutes. Once in awhile someone will do something and cause the database to halt (code builder window opens). Currently the way I check to see if the database is running is to VNC onto the pc from my pc and see if it's running. Does this make since?

DT

View 2 Replies View Related

I've Got The RUNNING SUM Blues!!!

Feb 12, 2007

Hello,

I have a report which is similar to a bank statement. I have a transaction date and then either a credit or debit depending on whether the customer made a payment or if I am invoicing them.


I have created an additional field in the report which calculates AmountDue - TotalPaymentAmount and called this field Balance.
I then attributed a running sum based on this Balance field.
This works great.................BUT!!!!!
If a customer has 100 transactions the report will be well over 10 pages long.
Is there a way I can limit the report to just perhaps the most recent 20 transactions or 20 days?

I tried and failed in the report's query retrieve the payments which occur >datenow(-20) (basically in the last 20 days). This worked ok in the sense that only the last 20 days worth of transactions appeared in the report......BUT!......the first running sum of the report does not carry over from the previous transaction..it starts from 0!!!!

Please help me get over the RUNNING SUM blues!
Rob

View 4 Replies View Related

Running A Sub From Switchboard

Dec 11, 2007

Hi there!I am having a fair amount of trouble figuring this out, and I was hoping someone might know what I was doing wrong. Someone set up a switchboard (I think using the Switchboard wizard) in this Access DB. I've since taken on the project of automating a database process and I want to use a switchboard item so it's a one-click, otherwise mostly automatic process. I have added the switchboard item, and when I click on it I get a msgbox displaying with my test text, so I think that is set up OK.Right now, I am trying to have a click on the button launch a custom sub (that I still have to write). Right now all I have in that sub is another call to a Msgbox with other testing text.Following is the relevant VBA code - not all of it, but I think this is all that will be involved. runDeletions() is located in its own module "Module3":The Sub:Public Sub runDeletions() MsgBox "testing - runDeletions ran successfully" End Sub This is the code in the switchboard form that deals with the arguments taken - and that will call the above function. Code within Private Function HandleButtonClick(intBtn As Integer): '...have the argument set to 8...Const conCmdRunCode = 8 '... so this Case will run:Case conCmdRunCode ' optional, just tells me the value of the argument being used' MsgBox rs![Argument] 'actually is what tries to call the Sub Application.Run rs![Argument] I've found the Switchboard Items table, and added in the following record. It hasSwitchboardID as: 3 (this button is on its own page)ItemNumber as: 1Itemtext as: Run Pending DeletionsCommand as: 8Argument as: Module3.runDeletions()I'm pretty sure my Argument is wrong. I've tried a few things but can't figure it out. What could I use as my Argument so my sub runDeletions in my module Module3 will run when this button is clicked?Thanks!PS - I've also posted this question here: http://www.vbaexpress.com/forum/showthread.php?t=16611 and any differences in code are intentional, as I keep trying different things (but so far no luck)...

View 3 Replies View Related

Outlook Running?

Jan 27, 2008

Hi,

I'm sending an e-mail each time a spesific report is made. If the user has'nt opened Outlook, it is placed in Outlook's Outbox, and is sent first when the user opens Outlook. Can I somehow start Outlook using VBA so the email gets sent when the report is run?

Also, The user is prompted with a warning message that an application tries to send an email. The user has to allow this for the mail to be sent. I know this is a security matter so no applicatopn can send emails without the user knowing. Is there someway to do this without the warning?

I'm using WXP and A2K3

Thanks in advance.

View 5 Replies View Related

Running Minimised

Feb 26, 2008

Hi Chaps

Probably one of those numpy questions but does anyone know how to run a database minimised?

I have a scheduled task that needs to run every 30 minutes which it does beautifully, however the database window pops up over whatever I'm doing at the time.

Not a huge problem but if anyone has a solution?

Extra info: The scheduled task calls a .bat file that runs the database and transfers data to a web site.

Thanks

Paul

View 4 Replies View Related

Running Sum In Query Possible?

Sep 16, 2005

Hi!

Sorry I am to bother you. But I was wondering whether it is possible to create a running sum in a query (from each previous record just a simple add up)?

I know how to do in report (it is described in the help function) but for a query I cannot find anything.

Thx and rgds,
Lobhaan

View 2 Replies View Related

Running Sum Query

Feb 16, 2006

have a table, Loans.

Fields:
ID (key)
Loan Number
Principal Balance.

I have another table, Transaction, that's based off the Loan Number field on the loans table.

Fields:
LoanNumber
Payment Type (2 choices, payment and advance)
Amount

I have a user form where users can specify the payment type, and amount, based on a certain loan number.

I'm attempting to do this:

User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.

If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.


Here's what I have so far.

The userform updates the transactions table with the information correctly.

My next step is to update the principal balance.

I'm thinking I could use some sort of update query, but I don't know where to begin.

HELP!!

If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand. :o :cool:

View 1 Replies View Related

Running Queries

Jul 7, 2006

Hi,

I did 2 ways:

If I created a query named qry1,
then, I created another query (qry2) which include qry1.
Is that meaning running 2 queries?
Firstly, run qry1 result, then run qry2.

Please let me know, thanks.

View 3 Replies View Related

Running Query With Like In Ado

Jul 29, 2006

Well Shut down my computer and call me a newbie.

I'm trying to run an append query with two parameters and a like "value*" in the where clause. When I run it in ADO it returns no values.

When I run it in DAO is works just fine.

WHen I delete the like condition it works just fine.

When I change the like condition to and abolute condition, e.g. "Cancel" it works just fine.

BUT WHEN I PUT Like "Can*" back it is returns no records.

Now I've been working with Access for more years than I will admit, but I have never seen anything like this.

I assume Like is valid SQL

SO SOMEONE PUT ME OUT OF MY MISERY. Am I barking up the wrong dog, or is "Like 'XXX*" not usable when executing queries in ADO.

SAVE ME !!!!! SAVE ME!!!!

View 10 Replies View Related

Running Totals

Apr 17, 2007

Hi,

Am attaching a db related to my question

I have 2 tables "Summary Table" and "Select1Summary"


"Summary Table" shows projects, departments and the available hours for each department

eg.

In "Summary Table" for 71043-40 Project, there are 1808 hours available at the beginning

"Select1Summary" shows Project, department, Month and the hours each department will be booking

eg.

In "Select1Summary" Table(Actually its a summary query, but am putting it as a table)

for 71043-40 Project, Department 11 on 30/04/2007 will be booking 171 hours (ie in April dept. 11 books 171 hours for project 71043-40)

for 71043-40 Project, Department 11 on 31/05/2007 will be booking 135 hours

for 71043-40 Project, Department 11 on 30/06/2007 will be booking 108 hours

What i need to show is running totals in a new query like


for 71043-40 Project , Department 11 on 30/04/2007 Available hours = 1637 (ie 1808-171)
for 71043-40 Project, Department 11 on 31/05/2007 Available hours = 1502 (ie 1637-135)
for 71043-40 Project, Department 11 on 30/06/2007 Available hours = 1394 (ie 1502-108)

Kind Regards,

Charley

View 3 Replies View Related

Running Average Of 5

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







Copyrights 2005-15 www.BigResource.com, All rights reserved