Queries :: More Than 1 Inner Join Slows Query Too Much
Jul 30, 2015
I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.
To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.
Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.
If I run the query:
Code:
SELECT VI.*
FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;
It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.
Now if I run:
SELECT VI.*
FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku)
INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.
I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.
Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.
If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:
Code: SELECT [MainTbl].Location, Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod], Count([Tbl1].[Agent Name]) AS [NoCompleted], Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]
I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id 1 MISC 2013-10 2 MISC 2013-10A MISC 2013-10 3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
I have two tables with a one to many relationship. The tables are linked by the INDEX column.
EXAMPLE:
Code: TABLE_1 INDEX NAME 1 Name_A 2 Name_B 3 Name_C
TABLE 2 INDEX NUM_INDEX STATUS 1 1 REJECTED 1 2 REJECTED 1 3 OPEN 2 1 CLOSED 3 1 REJECTED 3 2 OPEN
I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).
Example: Name_A, OPEN Name_B, CLOSED Name_C, OPEN
SQL that I have now.
Code: SELECT A.FIN_Finding_Number, B.Max_Index FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index WHERE (((A.FIN_Finding_Index)=34));
This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
I managed to produce a working Query with AllenBrowne's ConcatRelated function.What i'm unable to do is a new query with left join on the result of the query using the ConcatRelated function.I get error 3075: syntax error (missing operator) in query expression (free translation - MS ACCESS not English version)
If I do the normal join then it works fine, but I only get rows for which there are related concatenated values. But I do also need those rows where there are no concatenated values' fields.
I have 2 tables that are joined by a many to many table:
tblProductInfo - ProductID
tblProductLinerMM
- PLProductID (FK to [tblProductInfo].[ProductID]) - PLLinerID (FK to [tblLiner].[LinerID])
tblLiner
- LinerID
I have a range of products that each use 2 liners. An inner liner and an outer liner. I need to add 2 records per product to the tblProductLinerMM table.
for example
tblProductInfo has the following records:
- 2138557 - 2378954 - 4387657
tblLiner has 2 liners in particular that relate to these products:
- L5475 - L5468
I need to create the following records in tblProductLinerMM preferably with the use of a query :
I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".
The value being selected is a StoreID, This ID is matched against the AssetRegister to find all group names, The GroupID and GroupName are then retrieved from the AssetGroup table, then finally the list is grouped on the Group ID and Group Name.
I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.
Here is the SQL query:
Code: SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN (SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID) INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName
And here is the inline query applied to the RowSource:
Code: AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _ & "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _ & "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName" AssetGroup.Requery
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity from( SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center FROM [Open Items] )a left outer JOIN [Open Items] AS [Open Items_1] ON (a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity) Where ([Open Items_1].MonthEndDate=#8/31/2014#)
I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination" 2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
I am fairly new to Acces 2010.I have two seperate tables hat I need to use to compare data. As you can see table A and table B have some of the same item numbers but they also have different item numbers that are not other table. Also some of the item numbers are duplicated in each table but that is okay because the cost of the item is different. Both tables contain item numbers for the products. I want all of Table A item numbers including the item numbers that are in table B. But I also want Table B item numbers except for the item numbers that are also in Table A. In the real raw data file some of the item number fields are blank but the other fields have values. How should I query these tables so that I achieve the correct results?
Table A Item Num Costof Item Supplier Sales Tax Purchase Month 1234 $1.00 Walmart $2.00 Dec 2013 2222 $4.00 Walmart $1.00 Dec 2013 2222 $2.00 Walmart $1.00 Dec 2013 1276 $3.00 Sams club $1.50 Dec 2013 7898 $5.00 Texaco $5.00 Dec 2013 4567 $3.50 Food Lion $1.00 Dec 2013
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:
I have three queries that I would like to make into one query, but I'm having trouble. How do I combine the three queries into one? The three queries are below. The qryGetMaxChapter3 is the one that I will use, i.e. it is built on qryGetMaxChapter2, and qryGetMaxChapter2 is built on qryGetMaxChapter1.
qryGetMaxChapter1 SELECT tblCourt.CourtID, tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter FROM tblCourt WHERE (((tblCourt.CourtActionsID)=1 Or (tblCourt.CourtActionsID)=2));
qryGetMaxChapter2 SELECT tblCourt.ID_Number, Max(tblCourt.ActionDate) AS MaxOfActionDate FROM tblCourt INNER JOIN qryGetMaxChapter1 ON tblCourt.CourtID = qryGetMaxChapter1.CourtID GROUP BY tblCourt.ID_Number;
qryGetMaxChapter3 SELECT tblCourt.ID_Number, tblCourt.ActionDate, tblCourt.Chapter FROM tblCourt INNER JOIN qryGetMaxChapter2 ON tblCourt.ID_Number = qryGetMaxChapter2.ID_Number WHERE (((tblCourt.ActionDate)=[MaxOfActionDate]));
Dear friends I maintain a document database wherein query is generated for individual status like approved, pending & due. approved and pending are generated through "like text*" in status filed. But for due it is separate query using >=Now() in date field. I want to join these two queries and result should be in one query as : Approved document Pending Document and from balance document using Now query - due. One report is attached which will help understand - blank yellow field requires due query.
I am having a problem trying to join two queries. The first query, which is called qry_Planned_By_Month comes from a table (tbl_planned) that captures budget planning information. I created an expression in this query called SumOfPlanned. It takes the sum of all common budget line items (BLI) and is broken down by month.
The second query is called qry_Actuals_By_Month, which comes from a table (tbl_actual) that captures the actual amount spend against each BLI. Again, I created an expression in this query called SumOfActuals. It takes the sum of all common BLIs broken down by month.
Problem: I am trying to create a third query where I can compare planned versus actual. The formulas that I am using to do this are accurate. However the result set contains duplicate data. For example, I may have three entries on the planned side (March, June, and December) and on the actual side for the same BLI, I only have data in February. It displays the February data three times (because it is in the same row as the March, June, and December data.
Is there anything I can do? Thanks very much in advance. URL
An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?
I currently have two tables each containing information that I need to complete a query, however the results of suming this information is incorrect.
Below is the structure of the table, one contains more fileds than the other however the joins are on the Date and the Name fields. I have added a few dummy results to show the format
Table 1 "Date" "Name" "Times Logged In" 10/01/2013 Chris 1 11/01/2013 James 3
Table 2 "Date" "Region" "Manager Name" "Name" "Times Logged Out" 10/01/2013 Scotland Cindy Chris 1 11/01/2013 Wales Robert James 2
Query Fields
"Data" "Region" "Name" "SUM Times Logged In" "SUM Times Logged Out"
I can see what the issue is, if someone hasnt logged out there would be no entry in the table so the result query would show "Times Logged In" as X and a blank value at "Times Logged Out." However if there is a value at "Times Logged Out", "Times Logged In" is displayed again and causes the SUM calculations to be incorrect.
how do I overcome this issue though? I can do two spereate queries on the tables to produce results for Times Logged In and Times Logged Out but cant seem to create one query to display both these results in one table.
What I want is IF the date in Table 2 = Date in Table 1 AND IF Name in Table 2 = Name in Table 1 THEN take the value from Logged In and place that in a column and the value from Logged Out and place that in a column.
I have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?
I'm having problems doing a left join with 3 fields. I know I don't need the 3 fields for this example to align the data but, I deal with much more than this and need this functionality.
The query object I'm looking into is named 'left join' and the sql is as follows:
SELECT Act_Base.CustID, Act_Base.Chipset_ID, Act_Base.Product, Act_Base.Qtr, Act_Base.Qty AS Shipments, SE_Base.Qty AS SE FROM Act_Base LEFT JOIN SE_Base ON (Act_Base.Product = SE_Base.Product) AND (Act_Base.Qtr = SE_Base.Qtr) AND (Act_Base.CustID = SE_Base.CustID);
I'm expecting to see 150 units in the SE.Qty field.