Hi,
I am doing a Left Join to try to look up values in a large (about 100,000 records) table. If the value isn't found, I'm using the nz function to supply a value.
This query runs very slowly (takes about 2 minutes). I can understand why... I suppose that for every value it's trying to look up , it has to loop through all 100,000 records before it decides that it's not there.
So, I am just looking for ideas on how to make this run faster.
I do have indexes on all my join fields and criteria fields.
Thanks for any suggestions.
Here's the SQL:
SELECT VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0)) AS ZOpSeq, Sum(VM1a.DefectQty) AS SumOfDefectQty
FROM VM1a LEFT JOIN BOMOutRefDesOnly ON (VM1a.RefDes = BOMOutRefDesOnly.RefDesOnly) AND (VM1a.BOM1 = BOMOutRefDesOnly.PCAItemNo)
GROUP BY VM1a.row, VM1a.Column, VM1a.Noun, VM1a.Rev, VM1a.RefDes, VM1a.repcode, VM1a.Cell, VM1a.InspPoint, VM1a.DefectType, CLng(nz([FirstOfTruncatedOpSeq],0));
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have Table 1 with one field called Field 1 - values A, B, C Table 2 as follows
Field 1.....Field 2.......Field 3 A.............100 C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3 FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field3="XXX";
All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.
The left join I am using is as follows:
SELECT EMPLOYEES.EMPLOYEE_ID, WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED FROM EMPLOYEES LEFT JOIN WEEKLY_TIMESHEETS ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID AND WEEKLY_TIMESHEETS.WEEK_NO = 46;
The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.
I'm kind of a newbie to Access but am learning :D I've found myself stuck with a join statement
I have 2 tables: tblFleet & tblService
I need to find the most recent service date from tblService for each entry under tblFleet....
This is my code thus far, but it shows all the records for both tables where the fleet_Num field is equal....
SELECT tblFleet.Fleet_Num, tblFleet.In_Yard, tblService.SrvDate FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num ORDER BY tblService.SrvDate DESC;
SELECT tblEmployees.EmployeeID, tblEmployees.LicenceNo, tblTimeSheet.ShiftDate, tblEmployees.FirstName, tblEmployees.LastName FROM tblEmployees LEFT JOIN tblTimeSheet ON tblEmployees.EmployeeID = tblTimeSheet.EmployeeID WHERE (((tblTimeSheet.EmployeeID) Is Null));
errrr nope it doesnt work.. and that is direct from the wizard...
I have a work around but it is inelegant not to mention not workable..
what i need to do is filter on a shift date as i need to see what employees are not rostered on particular dates..
what i have had to do is make a query selecting base data from timesheet and filter the date at that point... make another query to do the left join.. again not elegant... to make a report i would somehow have to programmatically adjust the filter on the date.. too hard basket and will be too slow when i have a copious amount of data in 12 months... anyone know what the hell is wrong?
I have a loan db that I want to write a query to reflect all active loans with or without a current month's payments. In other words, I want it to select all active loans even if there was no activety during the month. I have 1 query that pulls out all active loans; qryActiveLoans. I then create a left join (2) with that query Grouping on the Loan# and summing the Principal and Interest fields where the payment Date is between #10/01/07# and #10/31/07#. I need to group on loan the number because some loans have more than one transaction in the month.
The problem is that the query does not select the loans that didn't have any payments during the month. I thought that a left join (2) will pull out ALL the records from the qryActiveLoans query. I've tried adding the word ALL after the SELECT statement but that does not work. I've also tried various forms of nz without success. This is probably a common problem but I could not find it in my searches of past postings.
I am trying to do a left join in ASP but getting error message saying "Syntax error in From clause" The query runs fine if I have a record for a country in all the tables. But I have certain countries that do not have a matching record in currency table. For those countries I want the information from the other tables. Following is the query:
SQL = "SELECT Country.Country_Name, Country.Continent," & _ "Country.GNI_per_capita, Inc_Cons_Distribution.Top_10_percent," & _ "Inc_Cons_Distribution.Gini_Index, Population.Total_Pop, Population.Urban," & _ "Population.Rural, Population.Under_14, Population.Below_Poverty," & _ "Currency.Curr_Name, Currency.Exchange_Rate FROM (([Country] LEFT JOIN Population ON " & _ "Country.Country_Name=Population.Country_Name) LEFT JOIN Inc_Cons_Distribution ON " & _ "Country.Country_Name=Inc_Cons_Distribution.Country _Name) LEFT JOIN Currency ON " & _ "Country.Curr_ID=Currency.Curr_ID where country_ID in(" & countryList & ")"
SELECT * FROM sys LEFT JOIN [local] ON [sys].[PK_]=[local].[SYS_PK_]
In the MS Access it is going well but when i try to connect to database from my application through ODBC, and I run query driver returns recordset with right records count but with EOF=BOF=TRUE
Error appears only when in the left table is record with no pair in the right table
I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.
SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`) WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;
I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.
I have been running a query to find out the name and addresses of some of our customers but some data keeps coming in to the results that i dont want to see.
The data is in the Name column so I have built a second table and put the names of the items i do not wish to see in the second table using a left outer join and selecting null or not null. This works great if i know all the combinations of the name such as smith i.e d smith david smith, sam smith dd smith etc.
I would like to enter Smith into the table and have my query return everything except with the word smith in.
I can code this into the query with not like *smith* but that means everytime i want to go into it i would have to enter the query not the front end
I am trying to build a query that shows me the activity of my customers. I have a database that amongst other things holds two tables which i am trying to query.
tbl 1 - Customers tbl 2 - Enquiries
What i eventually want to do is create a report that shows which customers have made enquiries and which ones haven't on a weekly and monthly basis.
the fields brought out of tbl1 are just the companies name, and sales person responsible for the account. Now to get all the customers to show i have learnt that you can do a left join on the query which will show all the records in the left table (tbl1) which is great and i can then limit the customer accounts that show by adding query criteria to the sales rep field from tbl1. The problem that i am having is when i try to limit the time period i.e weekly, monthly. when i enter criteria in the EnquiryDate field from tbl2 the query stops showing all the customers and only shows those that match the date criteria. Can anyone tell me how to prevent this from happening.
I'm using Access 03 and have a query that checks for the existence of a value from another table. This works well but what I'm not able to figure out is how to then get a count of the times that entry is found in the table.
In other words...
I have a table called 'jcaScore' in this table are unique text values in a column named 'Tier3'
My query needs to check the 'analyze' table, 'Tier3' column and COUNT the number of times each of those unique Tier3 values from the jcaScore table are present and place that count in the 'jcaScore'.gapscore field.
I've almost got it but I can't figure out the last argument.
UPDATE scoreJCA LEFT JOIN analyze ON scoreJCA.[Tier3] = analyze.Tier3 SET scoreJCA.gapcount = ???
There are a total of 11 records in the tblSystemAlarms table. The desired result is a return of all 11 records for a particular ReportID, even if there is not a record in tblSystemAlarmStatus. The actual result is a return of only the number of records that contain a matching ReportID. How do I include the other records?
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'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.
I need to do a report that takes data from many different tables (like 10).
To do this I have a big query with many LEFT JOIN to link all the tables together. I have made this query last year and it worked well and efficiently until now.
But I have to add something there, and the problem comes with it :
One of the tables is a currency exchange rate table, with the exchange rate of USD to 50 different other currencies and for each day since Jan 1st 2011.
But in this table there are some dates when a currency does not have an exchange rate (example : it was a holiday in that country, so there is no official exchange rate with this currency on that day).
However, I need to have rates even if they are not present in the table.
I need to get the nearest rate, for example if the rate of EUR on 31st December is not available, I can take the one of the 30th, or 29th or January 1st...
Ideally I would like to do the following :
Add a new LEFT JOIN to my already big query that would look like :
LEFT JOIN CurrencyExchangeRate ON ABS(RateDate - Instrument.Date) < 3
But to take only the closest rate.
I tried with a subquery :
LEFT JOIN (SELECT TOP 1 * FROM CurrencyExchangeRate WHERE ForeignCurrency = Instrument.Currency1 AND ABS(RateDate - Instrument.Date) < 3) AS MRate ON MRate.ForeignCurrency = Instrument.Currency1)
But the subquery does not accept Instrument.Currency1 because it is not part of the table CurrencyExchangeRate
I have a solution that should work, it is to search for this rate in a subquery in the select clause instead of doing a left join. But the tables are quite big (hundreds of thousands of entries) and my work machine is not really 'fast' so it would take hours and hours (when I tried I stopped after 3 hours).
I know there is the solution to make a macro to "fill" the missing rates with the rate of the previous day... But I would prefer not to go to this extent.
I have a table with Part No. and their correlating descriptions. I have a second table with a Product Number and its assosciated Part No. I want to view all of my Part No. with descriptions and IF there is a match to the Part No on the second table, I want to see the associated Product Number. Below is the layout and SQL on my test tables.
Test_Items1 (My Table) Part No = 1317667223606 Desc1 = ABC123 Part No = 1317667223614 Desc1 = ABC456
SQL (Which isn't working for me) SELECT Test_Items1.[Part No], Test_Items1.[Item SX Descrip1], Test_Products1.[Product Number] FROM Test_Items1 LEFT JOIN Test_Products1 ON Test_Items1.[Part No] LIKE "*" & Test_Products1.[Referenced Item] & "*";
The Desired Results from my above scenario would be this Part No = 1317667223606 Desc1 = ABC123 Product Number = 14_01 Part No = 1317667223614 Desc1 = ABC456 Product Number = Null/Blank
The Part No from Test_Items1 may not always be the first string from the Test_Products1.Referenced Item. It may appear anywhere withing that group of Part Nos. The customer has them separated by spaces.
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long ULCABC123 ULCABC124 ULCABC125 PLTABC123 PLTABC124
Table2 will have data in a column that is 6 characters long ULCABC PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode) Query1 - Test
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6) GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code: SELECT Left([ORDERDET],6) AS NEWORDDET FROM Table1;
Query3
Code: SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
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 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 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.
* In this MDB, the Relationships (1-n) are: tblCARS.COD = tblACESSORIES-CARS.CODCAR tblACESSORIES.COD = tblACESSORIES-CARS.CODACESSORIE
Table Data's ######### tblCARS have 2 records in COD and CARNAME fields: 1, Ferrari 2, Hummer
tblACESSORIES have 3 records in COD and ACESSORIENAME fields: 1, Light 2, Mp3 Radio 3, GPS
tblACESSORIES-CARS have only one record in COD, CODCAR and CODACESSORIE fields: 1, 1, 2
* This mean that: Ferrari have a MP3 radio... ok...
Now, i would like to construct a SQL that show me what ACESSORIES still havent found on tblACESSORIES-CAR, in this case would be 'Light' and 'GPS' to FERRARI. For Hummer, the SQL should show all three acessories: Lighr, GPS and Mp3 Radio because none of these are recorded in tblACESSORIES-CAR for Hummer.
The Microsoft Access 2003 have a query-wizard that search for 'no-coincident', I already try it that only show me WHAT acessories still not recorded at tblACESSORIES-CAR. But this query do not show what acessories are 'missing' for FERRARI for example! If I specify CODCAR it dont show anything more!! Why?
I need (by my asp code) list for the car (can be Ferrari or Hummer, for example) what acessories I still can add! How to 'fix' it??
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code: SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID) INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID ) ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
I have a growing Access database in a multi-user environment over a 10/100Mb network. The database is all in one file at the moment, on a shared directory of our XP-Pro 'Server', and the workstations have a mapped drive to it and are W98SE machines. All the machines are 1.2Ghz Fujitsu Siemens machines.
It is still under development, but is also in constant use, and I therefore have to develop on a copy, then get everyone out so I can copy in the changes. I would love it to be a client/server setup and split the db to Tables only backend on the server and progams on the client, but when I tried, the result was a dramatic slow-down in the system...it became unusable.
I do have a budget for this, and could get a proper 'server' or maybe an Ethernet Disk, but what is the best config for speed and admin purposes. Anyone doing something similar??
I noticed something strange in access 2000: sometimes it takes a long time to calculate a report and other times it goes rapidly. I don't see any process taking a lot of CPU %.
When i do the same thing in Access2003, it goes rapidly every time.
I have a number of queries which build two or three Union queries which looks at 35,000 records, and when you open the Union queries or run reports it takes forever for them to open.
I have indexed all the tables which have the common fields to see if this speeds it up the queries, but there still slow. The table they look at are in two different tables which are linked into a front end where the queries are!
