Running A Query Using Macros
Sep 23, 2004I want to run a existing query residing in my Access database using macros (MS Access 97)
Can someone suggest me the steps to go ahead ?
ADVERTISEMENT
Running Query With Macros
Dec 8, 2005hi 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
General :: Create Query Or Report With Macros Or VBA?
Mar 21, 2014I want to create either a Macro or VBA that will automatically create a report or a query.
View 4 Replies View RelatedBig Ol' Access Problem (Forms, Macros, Importing, Query, Etc
Mar 31, 2008Okay, so I just got a big project dumped into my lap to rebuild a database that was lost. This DB would import data from a couple excel spreadsheets and a text file, compare them to find any records that are missing from one or the other then spit that information back out in two reports. To make things harder, the person who uses this DB isn't the most computer literate and needs to be able to do everything with the push of a button. For this project I'm using Access 2003 with a possible need for 2000 backwards compatibility
First Issue: Importing. Is there ANY way to save an import so a macro could run it without the user doing anything? The other option is linked tables, but is there any way to update the data in those links because some of it needs to be changed. Also, can linking combine two excel spreadsheets into one linked table?
Second Issue: Information updating. As I mentioned before there is some information that needs to be updated before we can do comparisons. There is a field in both record sets that could be used for comparisons except for the fact it is formated all wrong.
In the excel spreadsheet it is set up as just a string of numbers like "3125" or "55879", but in the text file they added some preceding digits to this number code. The text file adds "HM" and a number of 0s to each number so that the field is a uniform 10 digits long. Like "3125" would become "HM00003125" and "55879" would be "HM00055879". So each record needs to have this data either added to it or trimmed off. The problem here is the variable number of 0s. I created a macro that would use the Len function to test how many zeros need to be added and update the field, but it can only run on one record at a time.
What I need to do is have either a trimming or adding updater for that field that runs in the background (either through the macro button or when loading a query) to make those fields compatible. It also would be preferable to work through linked tables.
Thankfully, there isn't any sort of rush on this paticular project. If you have any advice on any part of this project, please post
Thanks in advance
Query: Access Macros, Autodeleting Rows And Updating Table Names
Oct 11, 2007I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
Running Balance As Opposed To Running Total
Mar 14, 2005Can 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
Running Sum In Query Possible?
Sep 16, 2005Hi!
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
Running Sum Query
Feb 16, 2006have 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:
Running Query With Like In Ado
Jul 29, 2006Well 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!!!!
Running Sum Query
Jan 7, 2008Hi
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
:)
Running Sum In A Query
May 15, 2006hey 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.
Help With Macros
Sep 22, 2004hI,
I have a form which has several checkboxes and several textboxes and a command button. the user selects some checkboxes inside the form. when he clicks the command button, i need to run the select query. the select query would be like "select col1,col2,col3 from tablename where check1 = (checkbox1 value) and check2 = (checkbox2 value) and check1 = (checkbox1 value) and so on. ". i have tried this query using VBA. it is working fine. the problem is i have run another query which is very long in characters(like 15 lines involving self join). when i tried to run this query inside VBA i am not able store the query inside a string. Is there a way to come around this problem? I think it would be easy if i can use macros to build this query. please suggest me the ways to do this.
Thanks,
Sridhar!!
Macros
Mar 11, 2005I inherited this database where there is very little code, most of the commands are in macros. I know nothing about macros. I am having a problem with the database. Some of the records are not printing when I print the schedule(this is a scheduling database with dates and job quantities). There is a display checkbox that gets checked when we want a job printed on the schedule. Sometimes it will not print it if we do not have a date for when the files are due or when material is due, so if we put those dates in they will show up on the schedule. Lately though I can't even get it to print some of the jobs if I put in the date when the files and materials are due and when they come in. They just don't want to show up. If anyone could help, I would greatly appreciate it!! Thank you
learnasugo
PS I am working on getting db small enough to paste. Don't know how else to make it so you can see db.
Macros
Jul 26, 2005I am trying to invoke a microsoft Word recorded macro ("Macro1") in the microsoft Access VB code. I am trying to do this after I open the Word application and the file. This is my code so far:
Dim objword As Object
Set objword = CreateObject("Word.Application") '
objword.Visible = True
objword.Documents.Open ("I:FIRST DRAW CHECKLIST.doc")
objword.Run Macro1
The bolded line is the one supposed to tell word to play the recorded macro but after it opens the document, Access gives me a message that says it can't run the macro. Can anyone tell me the correct syntax that will help my code work?
Running Total In Query
Mar 12, 2007Does 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
Running Totals Query Help
Jun 23, 2005Hi - 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.
Running More Than One Sql Update Query
Aug 26, 2005I 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
Running A Query With A Lot Of Records
Mar 9, 2006I 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?
Running Balance In A Query
Mar 13, 2006I 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!!!!!!!
DSUM / Running Sum In Query
Sep 5, 2006I 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.
Running Count In Query
Dec 24, 2006I 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
Automatically Running Query
Jan 23, 2007Behind 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
Running Query About Date
May 9, 2007Hello,
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!
Running Total Query
Aug 22, 2007Hi,
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
TotalStartDateLeftDate RunningTotal
126/03/1957
121/03/1971
127/02/1986
115/02/1988
207/03/1988
007/03/198831/05/2007
Here is my current SQL query:
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal
FROM Atest1
GROUP BY Atest1.StartDate, Atest1.LeftDate;
Can anyone help please?
Running Sum In Query Problem
Nov 4, 2007Hi Guys,
I'm doing a database for a client and I'm stuck on a graph (or rather having an overall total on the graph). I've done everything in the query but I do need a running sum for this. I've explored my books, looked on the help, checked out various past posts and I seem to be missing something.
I did find a helpful post which solved someone's problem and I have posted the code below. because everytime I try to come out of it (and I've retyped it a number of times) it keeps coming back with "Syntax error in string in query expression blah blah blah".
Code that I have is:-
SELECT Code, QC4RS, PODate, Net,
val(DSum("Net", "Costings Query", "Code"= '" &Code&"' and QC4RS =" & QC4RS & "and PODate <=# "& PODate&"#"))AS RunningSum
FROM [Costings Query];
What I want ultimately is a running sum of the Net Cost for the product code so that it gives me an overall total on the graph. It's now been over 8 hours that I've struggled with this and I'd really appreciate a fresh pair of eyes to tell me what I'm not seeing.
Thanks in advance.
Donna x :mad:
Running A Query From A Switchboard
Jan 21, 2008I have an SQL query which simply deletes any blank records from a table. I want to put it on a switchboard menu, but cannot see an option to do that. How do I do this? Perhaps create a little form and put a button on it to run the query?
Robert