Automate The Running Of A Parameter Query

Jun 12, 2006

Automate the run of a parameter query

I have a parameter query in ACCESS that generates an ACCESS report for 250 facilities. (Each facility has their own ID of course.) Every other month I need to run this report on all 250 different facilities. Then I give each facility their report by dropping it into an electronic folder.

This is a lot of work. Is there a way that I can automate the generation of these reports? Perhaps somehow pass the ID numbers?

I need each report to continue to be generated as a separate file.

View Replies


ADVERTISEMENT

Running A Query From A For W/Parameter

Nov 4, 2006

Hi All,

I have created a query that I want to run from a form (thus far no problem). Where I do have a problem is that I want to take the contents of a variable and use it in the query. I don't want the input box asking for the value.

I hope that I have made myself clear.

Cheers,
Aaron

View 12 Replies View Related

General :: Detect Running From Scheduler With Only A / X Parameter

Sep 16, 2013

Without putting a /CMD parameter value in the command line, is there any way to detect in VBA that the current state of the application is that it is executing from a scheduled task ?

What's happening is that my Outlook email is failing because a Namespace Logon method cannot be invoked when there is no user logged in. I must jump over that statement when running from a scheduler.

View 2 Replies View Related

How To Automate An Update Query?

Dec 29, 2004

Is it possible to automate a update query to run whenever a table has a new record added? If so, how is it done?

Thank you.

View 2 Replies View Related

Queries :: Way To Automate / Improve Update Of Query

Apr 13, 2013

I have 2 queries to check if there is any "double quote" character in any of 12 month columns in a month table of 125K records. I use 2 queries since the maximun criteria in a query is only 8. So I use 8 criteria in the 1st query then use 4 criteria to check the remaining months in the 2nd query. The month table is refreshed and created every month. The 12 month columns are changing from month to month since they are -13 month to -1 month when the month table is created. For example, if the month table is refreshed and created in May 2013, then the 12 month columns are "May 2012", "Jun 2012" .... and "Apr 2013". If the month table is created in Jun 2013, then the 12-month columns are "Jun 2012", "Aug 2012" .... and "May 2013". The end user has little knowledge of Access Databse so he seems confusing how to update the 2 queries on new month table.I am trying to see if there is a way to use 12 criteria in just one query so the end user only deals with one query's update.If there is a way to automate/improve the update of the queries/query, then it would be the best.

View 9 Replies View Related

Modules & VBA :: Union Query - Automate Date Field

Mar 18, 2014

The statement below is a snippet from a union query and is repeated 6 times within the SQL Statement for various reasons, I have to manually change this every month, again is there i tiny bit of code I can insert to replace the following so that it automatically runs the previous months data.

WHERE ArrivalDateTime >= 'February 1 2014'

View 5 Replies View Related

Queries :: Automate Outlook Task To Export Common Query Every Day

Jun 29, 2013

I've created an outlook task to export a common query I do every day, is there a way to automate it so it will happen automatically without me pressing Run?

I use office 2007 on a company laptop with tight network restrictions (so no Windows Task Scheduler)..

View 1 Replies View Related

Reports :: Automate Printing From A Command Button With Selected Data From A Query?

Jul 15, 2013

How can I automate different printers for different reports on the same MS Access 2007 database, without having to select the printer each time?

There are two reports that are printed on the same database. Previously, I had automated form buttons to print the reports, without having to select the printer each time. This was about 8 years ago, but I don't remember how I did this. Also, I don't know SQL. Nothing against code, but I did not know how to program, and just MS Access 2007 access itself.

View 6 Replies View Related

Capturing Parameter From Parameter Query

Jul 12, 2005

I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.

View 2 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

Parameter Query Asking For Parameter More Than Once!

Nov 9, 2006

Hi,

I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!

Here is my code (abbreviated slightly):

SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);

I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.

So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!

Help! :confused:

P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.

View 2 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 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 Sum Query

Jan 7, 2008

Hi

I have a query that shows part numbers with a total qty ordered for a specified time period. This first part of the query is fine and is made into a table.

I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list. I have tried the following formula and the table has been indexed in the descending order it would need to be in. The IndexAlias has also been created and made into a fixed table prior to running the 'Running Sum' query.


RunTot: DSum("qty_ordered","Core Stock","[Index]<=" & [IndexAlias] & "")


I have taken this formula from the microsoft link below, (Method 2), and adapted it to my query. However, although I have a decent understanding of Access I do not understand the '&' in this formula. Removing this and the double quotes on the end returns an error and leaving them in returns zeros all the way down the 'RunTot' field. If anyone can help I would appreciate it as I am stuck on this.


http://support.microsoft.com/kb/290136


many thanks


:)

View 14 Replies View Related

Running Sum In A Query

May 15, 2006

hey there,
i am trying to create running sum in a query. i found the function for a report but not i a query

From MS Access Help: ("You can number the items in your report by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1. Then, you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All")

my overall goal is to number each Employees assignments that they have. That is Employee1 may be associated to 2 assignments, thus resulting in employee1, (assignment number) 1, employee1, (assignment number) 2, and Employee2 may have 4 assignments, employee2, (assignmetn number)1, employee2, (assignmetn number)2,employee2, (assignmetn number)3, employee2, (assignmetn number)4.

any ideas.

View 6 Replies View Related

Running Total In Query

Mar 12, 2007

Does anyon ehave any experience of running totals in an access query.
I'm reporting the data through excel not access reports so need a query not a report solution..

I have a table which looks:

RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000

What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.

Any helpo really appreciated.

Simon

View 1 Replies View Related

Running Totals Query Help

Jun 23, 2005

Hi - Once again I'm asking for your assistance. Ideally, below is how the form (and eventually a report) would look; the first three fields (ID, Date & Vol) I currently have in a table (tblMaster):

ID Date Volume YTDTotal Q1Total CurrQTDTotal Q2Total
A1 Jun-05 10,000 77,000 40,000 37,000 37,000
A1 May-05 15,000 67,000 40,000 27,000
A1 Apr-05 12,000 52,000 40,000 12,000
A1 ------ ------ 40,000 40,000

(Sorry, I couldn't get the above data to line up right :confused: )

How would I build a query such that I could show the above in a form/subform (and eventually a report)? Help is greatly appreciated.

View 1 Replies View Related

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

Running Query With Macros

Dec 8, 2005

hi guys!

Is there a way to run a query with macros? I don't want to open it. I just want to run it, generate a report which will then be sent via email. How do I go about it?

Thx a mill!

cheers:D

View 1 Replies View Related

Running A Query With A Lot Of Records

Mar 9, 2006

I am running a query that has over 14000 records. What I tell the query to is read two other queries and tell me which record does not match with the other one. My problem is, that it takes over 10 minutes to do.
I have already compressed the file. I do not have a primary key (trust me on it, I can not do that). Are there any other ways to resolve this?

View 1 Replies View Related

Running Balance In A Query

Mar 13, 2006

I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:

new column
Invoice #: 1345652 50.00 50.00
Invoice #: 1345652 65.00 115.00

I have the same invoice multiple times, but the transaction number is different. so I need to SUM all the invoices that are the same.


Please help!!!!!!!

View 5 Replies View Related

DSUM / Running Sum In Query

Sep 5, 2006

I have been trying to get this all night. This was my last attempt. I know it is all wrong and messed up but after 10 hrs I go for help. There is no date field to work with.

Expr2: DSum("[Amount]","[List]","[Item]=" & [Item] & "and [FinItem]>0")+0

But it ended up like this.

Item ------Location-------Amount--Expr2----Expr3
210090------030005 -------267470--error ----859103.74417
220170------ GYM -------409000--error ----859103.74417
220170------ 060010-------83297 ---error ----859103.74417
220170------ 050010-------17762 --error ----859103.74417
3429B -------060010-------0.74417--error ----859103.74417
3429B -------060010-------3 ----error ----859103.74417
7342900-----060010--------5369 ---error ----859103.74417
7342900-----060050--------76202 --error ----859103.74417


I need it to look like this.Where it will do a running sum based on the "Item" Groups.

Item---------Location------Amount----Expr2------Expr3
210090------ 030005-------267470---267470---859103.74417
220170------ GYM --------409000---409000---859103.74417
220170------060010-------83297----492297---859103.74417
220170------050010-------17762----510059---859103.74417
3429B-------060010------- 0.74417---0.74417---859103.74417
3429B-------060010------- 3-------3.74417---859103.74417
7342900-----060010-------5369----5369-----859103.74417
7342900-----060050-------76202---81571----859103.74417





Please Help.

View 3 Replies View Related

Running Count In Query

Dec 24, 2006

I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.

Example

Count Location Function
1 100 1111
1 105 1111
2 105 1121
3 105 1131
1 110 1151
2 110 1151

I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.

Thanks

Loki

View 5 Replies View Related

Automatically Running Query

Jan 23, 2007

Behind a command button, i have 3 queries being run. 2 of these queries are append queries, copying the specified record into another table (criteria set to ID of the main form) and the other query is to delete the record from the current table once it has been copied. This is then followed by another delete (not query)

When each of these are run, boxes pop up asking if you're sure you want to do it. I was wondering if it would be possibly to bring up my own box to ask if the user is sure they want to continue, and when they click yes, all the questions that the query asks about wanting to do it, are automatically chosen as yes.

I.E there is only one option box, yes and no, and no others appear.

Is this possible, if so, how?

Thanks
Aidy

View 4 Replies View Related

Running Query About Date

May 9, 2007

Hello,
Say for example I have a database containing the date of birth of people in the format of YYYYMMDD (a string/text field) and another field about the age of the person (a numerical field) and another field is, say, the date they had their birthday last year. I would like to check whether the year in the date of birth field and age adds together is the year in the date they had the last birthday field (or off by no more than 2 years), and give me the list of the people who are not. Is it possible to do that by writing a query or I have to use a macro and write an SQL statement for it? In both case, how should that be done?

Thanks a lot!

View 9 Replies View Related







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