I am using VBA to run multiple queries in access. So far I have the docmd.openquery "various queries" thing set up fine, but naturally there is a catch.
The queries I am setting up are append queries to take a big file and disperse it over multiple (13) tables. This requires me typing in a 4 digit YYMM to append them and it happens for each query. Therefore even though I am running them all in a row I still need to type in "1306" 13 times to key the june 2013 entries. I was wondering if there was anyway for vba to automatically key this or if there is anyway for me to adjust my vba or queries so that I only have to key it one time.
Public Sub QueryRunner()
DoCmd.OpenQuery ("Query1")
DoCmd.OpenQuery ("Query2")
DoCmd.OpenQuery ("Query3")
' and onward to query 13
End Sub
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
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?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
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.
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution Query 2: Total Distribution etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
Is there anyway to join 15 queries into 1 (all have the same data from a different source but need merged into 1 large query) rather than doing 12 unmatched queries?
Let's say I have 5 records in the first query, 5 records in the 2nd, and 3 records in the third - I want to end up with 1 query that has 13 records.
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
Help. I am relatively new in working with Queries. I am taking over someone else's database. I would like to do the following" take test results and combine into one query. Here are my variables :q# question, response(numeric),expr (response to text description such as agree, don't know, disagree. ) for questions 1-5(response) are : 1, 2, 3. I use my expr(IIF) to give me my text description: agree, don't know, disagree. Say my 2nd query is questions 6&7 only which response numbers are 1,2 and expr(text desc):Yes or No. My 3rd query is for q# 8,9,10 which are 1, 2, 3 and expr:some, all or none
I would like to accomplish the following:
How do I combine into one big Query
I thought I read somewhere that I could link my query to run one after the other to provide the final result into one table. I hope this is detailed enough
I have a search menu where the user can choose a search option:
1. Search by Date Filed 2. Search by Paid Date 3. Search by Invoice No. 4. Search by Client Name
etc....
I have a query built for each of these options, 14 in all. Is there a way that I could have the user enter more than one option, and link those queries together? For example, they could enter 1 and 4 to search for Client Name on a particular filing date.
I would rather not have to write a query for each and every option...
In a form, the user specifies critera which triggers Query_1. Query_1 finds and lists the primary keys (multiple records) from Table_1 where the specified criteria are true. For this argument, let us say 10 records are found.
One of the fields in Table_2 stores the PKs of Table_1 as foreign keys in a 1-many relationship. Say, there exist 30 records in Table_2 corresponding to the 10 records in Query_1.
I would like to run a query which will isolate these 30 records from Table_2 based on the 10 records in Query_1.
How does such a query work? (somehow I feel that it is something obvious but is missing me!)
I have two textboxes where each contains hh:mm:ss values and I have to get the sum of these two. Currenlty, I was able to split them up and converted hours to minutes and seconds to minutes. However, I am facing a challenge converting minutes back to hh:mm:ss. converting from hours to minutes and sum up the minutes
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?
Hey y'all, my question this morning is concerning doing a multiple field query in Access. I have a lot of experience with both SQL and Access, but have never had to do something like this.
I have a form with a text field for every row in a table (so there are 15 text boxes), and I want the user to be able to type in any data they know for any number of fields, from 0-15 rows. So I want each text box to be matched on (Like '*'&[data]&'*'). My problem is that if I say "where x=x AND y=y" and so on and so forth, it brings up all fields, because there are wildcards on each end of an empty string. I also have problems with just doing OR for each field, because it only matches on one field, even if they entered data into 4 fields. I'm so confused!!!! Did this make any sense? Any Ideas? :confused:
I'm used to doing a data analysis but never had much cause to use Access previously so I'm trying to quickly pick up the essentials for a specific work project:
I need to extract a subset of data from an Access 2000 database and then perform some category operations on that subset - all interactively. So 2 questions please:
1. I'd prefer to do this with two successive queries (just so the logic is plainer to me) the second operating on the results of the first. But I can't immediately see how to submit the results of one query to a second query interactively.
2. If I were to combine both queries into one can I safely assume that the columns will be processed from left to right (as shown in the query designer)? To explain: I want first of all to select a subset of the data and then to do some stats (ie as a 'total' field) on the subset. But I need to ensure that the stats are done on the subset and not the full original table.
I have created a query so that two fields have a range in them i.e.
One Field called "Price" There are two text boxes on the seach form called "price1" and "price2" The query reads
Between [price1] And [price2]
A second field called "Date Ordered" There are two text boxes on the search form called "date1" and "date2" Field called "Date Ordered" Between [date1] And date2]
I have used the AND operator to join the first and second range. The user will fill in the criteria from a form with text boxes. This form is unbound but as soon as they click on the Search button, it will open up the query with the parameters in as above.
However, if the user were to type in a price but not a date the query shows no results. Results are only shown if a price and a date are inputted.
The OR operator will work but it is not what we need. We need to show results if a price is entered but not a date and vice versa. The fields are coming from one table.
Please can anyone advise? The database is a library management system so the price and date are the price of any books and the date that the book was ordered.
Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.
EX: SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down] FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end])) GROUP BY tblDownTime.dtDate, tblLine.lineName HAVING (tblLine.lineName)="[B]name of line");
criteria being name of line. Choices being line 1 or line 2
What I am looking for is one query to somehow generate the info on the two different lines in two different reports.
Is there a way or do I actually have to write the different reports?
I have a several parameterized queries that I would like to run using the same information, but don't want to make the user enter the same parameters over and over again. What I envision is the use clicking a button to run a macro that will run each query and then open a report with the results of the query. This report will the output from for queries that require the user to enter the start and end date. Is there anyway, for me to have the user enter those dates one time and for each query to call that same information? I was thinking I could have the user enter the start and stop date into unbound fields on a form and then click the button, but I don't know how to call that information into the query parameter box. Any suggestions/examples would be appreciated. Thanks in advance for your assistance.
I have a database which contains around 60 queries that I need to turn into reports. The problem is, the wizard won't work because all of the queries contain the same fields. I would greatly appreciate feedback on making this process dynamic. Thanks in advance!
I have a registration form where a person can select if they will attend any or all of 3 events. I want to create a report that will say x registered for event 1, y for event 2, and z for event 3.
I have 3 queries which seperate the people by whether they are attending event 1,2,3. In these queries I have CountofID field so that I can sum it.
When i create a query that Sums the CountofID field for each one, it multiplies the values. I played around unsuccessfully with crosstab queries, but to no avail.
