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.
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
Hi,
Can I use a macro to run multiple queries in sequence?
I searched on this but did not see anything that indicated how to do this.
Any pointers would be appreciated!
Thanks,
Mark
I've got a Macro that runs well over 100 queries as well as sub-macros. I'd like to know what query the Macro is running. My best solution so far was to create a bunch of forms that open and close with the name of each query, but that's kind of sloppy. I don't think I can use the msgbox because it prompts me to press 'OK' to continue. Any thoughts?
View 2 Replies View RelatedHey guys-
I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this-
I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another...
Thanks!
I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly.
Thanks,
a_brooks
Not sure what happens.
occassionally i will run queries that have run before,
the hourglass will turn on, then turn off, and access does nothing
visible, but tack manager says its running 90% cpu. . .
any ideas on what is happening or i am doing wrong?
thanks
sportsguy
Hi,
I have created the following function in my datebase so that i could specify the date a query works from.
Option Compare Database
Function GetParmValue() As Date
GetParmValue = InputBox("Please enter the date you wish to update!?")
End Function
I have a button on a form that runs the query in question, there is also an identical query that needs to be ran using the same result from the input box but rather than it display the input box twice i wondered if there was a way i could use the result from one of the boxes with both queries!?
Here is the code for the button;
Private Sub bImport5_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Update"
DoCmd.OpenQuery "UpdateFuture"
DoCmd.OpenQuery "MoveFuture"
DoCmd.SetWarnings True
MsgBox "Files have now been updated to the specified date and moved to the Future Dated table!", vbOKOnly, "Update Complete"
End Sub
Any ideas guys?
Many thanks
Tim
I have data like so:
Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc
And need to return a running count for the number of visits per subject, so:
Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.
I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:
VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])
creating a query with a running sum (cumulative total) across two categories. I need to accumulate Wages by employee, by calendar year for every day/every job worked. I have a table containing over 33,000 records, the years span from 2009 to 2014 with multiple employees.
The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for. It needs to be a SQL statement. Although all the SQL statement I have tried simply total all Wages for every entry ever made in the column, instead of per calendar year, per employee.
Here are my fields:
Calyear = Ascending
Employee Name = Ascending
WorkDate = Ascending
ID
[code]...
There may be more than one entry per day per employee in a given year. This is so I can calculate certain payroll taxes which are based on cumulative wages amounts.
I am trying to make a query that give me an output of an aggregate percentage column, or running sum of percentage. Please see my example:
Month Sales in % Running sum percentage
Jan 5.03 % 5.03 %
Feb 2.17 % 7.20 %
Mars 1.28 % 8.48 %
I have the column Month and Sales in %, What do I have to do to get an output like the example over?
I have a form with tab set one tab called "Enter Receipt" and another that houses 2 queries called "Reconcile". My issue is when I open that form, I have an On Current Macro to go to NEW record for my Enter Receipt, but I am getting a delay while the query status bar runs the other queries. I was hoping not to have those ran until i enter the parameters and hit the run button on that "reconcile tab".
Everything else works, i just need the queries to keep from running when i load the form. my queries i moved from EDITED to NO LOCKS thinking the On Current new record may affected them, not change in delay.
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
I have a table for timesheet entry for a local building firm. I have a separate table containing employees and rates. I have created query "Qry_ Time Costs" which calculates the cost of hours worked by each employee by multiplying the hours field in the timesheet entry table by the rate field in the employees table.From "Qry_TimeCosts" I have created "Qry_TimeCosts1" in which i have included a running total field for time costs per day using the DSum function.
SELECT Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], Sum(Qry_TimeCosts.Hours) AS Hours, Sum(Qry_TimeCosts.Cost) AS Cost, DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "") AS RunTot
FROM Qry_TimeCosts
GROUP BY Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "")
HAVING (((Qry_TimeCosts.[Build Element]) Is Not Null));
This seems to be working for some projects and not for others. In particular dates 3rd 4th and 5th of June seem to be showing null fields, where all other dates have values. A few projects are showing inaccurate running totals whilst others are working fine.
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
I have a situation where I am using a maketable query to create a table and then I need to use append queries to then add additional records to the created table - some of these are just run once and some multiple times.
if possible, I do not want to hard code the query multiple times i.e.
Code:
DoCmd.OpenQuery "qryCreate_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
DoCmd.OpenQuery "qryAppend_1"
etc
So is there a way I can run the make table query and then get some sort of loop to run the append query a number of times ?
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I am attaching the database, with sample data.
I have an access query named "leaveapp" and I want a running count as below:
EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1
and i need a column in the right with running count like below
EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
I am pretty new to VBA coding and need running queries on specific tables. This is part of a multistep process, of importing data that needs to be transposed before appending to the "cleaned" database. First, users will save auto generated, Excel workbooks from a machine into a designated folder. I have code that will import these workbooks (an unknown number at a time) into Access in separate tables by workbook.
Each table will have a similar name, but different extension (ie Sheet101, Sheet102, etc.). This is where the issue arises. The files are in long form, not wide. I have a series of queries (unfortunately, they're not SQL queries, but I can convert them to be) that transform the data from long to wide. However, I do not know how to go about writing code that will run the queries ONLY on the imported tables (again an unknown number of tables with similar names), not the rest of the tables in my database. I'm guessing it involves a do loop, but I am not positive.
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
I have now a database where I made a query that will show me all clients I can charge when there is a end date and an account number mentioned. I have also in a table where I put if the client is already charged. This is a check box that gives me yes or no. Is it possible to run the query and once those selected show up, to also add a yes to the table so next time I run the query the ones charged will not be charged again.
View 5 Replies View RelatedI have a count and store data INTO a table named USTATE
When I run the query it works within 30 seconds. but when i add INTO USTATE then it takes 4-8 minutes to complete it and asks for if i want to delete the existing.
I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..
So far this thread has been very useful: [URL] ....
It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).
In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").
The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])
So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.
Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.
I have a query that produces a report for charges to the client. On the form I have a checkbox that when the client is charged it is ticked. What would be better is that when I run the query to export to excel, it should check all those checkboxes and the ones that are blank, fill them in (tick the box). Then I need to add to the query that that field should contain only blanks so it will show me only the ones that where not charged and then after producing the report check those boxes so it keep track of all the clients charged (to avoid double charge).
View 3 Replies View Related