I am running the following UPDATE statement on a table with about 1000 rows, using a self join:
UPDATE [Tax Report Co Date] INNER JOIN [Tax Report Co Date] AS [Tax Report Co Date_1]
ON [Tax Report Co Date].ID = [Tax Report Co Date_1].[ID+1]
SET [Tax Report Co Date].Co = [tax report co date_1].co, [Tax Report Co Date].[Date] = [tax report co date_1].date
WHERE ((([Tax Report Co Date].Co) Is Null) AND (([Tax Report Co Date].Date) Is Null));
The problem is that it doesn't update all of the appropriate rows "the first time". It leaves gaps of rows unchanged... almost as if it blocked itself from updating, or some sort of buffer/cache issue. If I run the UPDATE a second time it updates the remaining rows successfully.
Any ideas on why it takes multiple passes to perform this update? Can I do something differently?
I'm trying to update a table via a join with the current query:
UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];
Unfortunately, I have just discovered that the relationship is not always 1 to 1 (although it is in 99% of cases). In those where, it isn't and the destination table has, say, 3 records with the same DocNo, I would like to update only the top record.
Can this be done in the join? I don't think this will work: UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON TOP 1 baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];
I am trying to create update and append queries that deal with multiple tables. I can't seem to get it to work. Any help would be appreciated.
The update query needs to reverse this select query: SELECT tblPayment.AmountOfPmt, tblPayment.DueDate, tblPayment.DateIssued, tblPayment.CheckNumber, tblPayment.PaymentType FROM tblPayment INNER JOIN (tblLease INNER JOIN tblCustomer ON tblLease.CustomerID = tblCustomer.CustomerID) ON tblPayment.LeaseID = tblLease.LeaseID WHERE (((tblCustomer.CustomerID) Like [spCustomerID]));
The append query needs to insert CustomerID, FirstName, LastName, SecondaryCustomer, PhoneNumber, DOB, Email, Active into tblCustomer, but also DateSigned, DateEffective, DateExpire, CustomerID, LotID into tblLease.
I can't figure either of these two out. Any help would be great.
Hi, we are currently working on a project to strip down one of our SQL Server based applications to an Access 2007 based solution, and I met this problem: the query just doesn't work on the new Access mdb. Can anyone take a look and give some help? Thanks a lot.
UPDATE A SET MaxPrice= B.MaxPrice FROM Summary A INNER JOIN (SELECT Name, MAX(DailyPrice) AS MaxPrice FROM Data GROUP BY NAME) B ON a.Name = B.Name
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects SET AccessType = 1 FROM RoleObjects INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT * FROM RoleObjects INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I need help with an update query. I download several data tables from SAP that I would like to join again in Access. I have set up an updae querry but not all the records are unique. I thought I had fixed this by using a flag to update to -1 processed when running the query and set the criteria on the flag 0 to process items. But the whole querry processes before setting the flags at the end and I want the flag to be set so that it excludes processed items when each line of he query is executed. Can anyone help?
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 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'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";
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 query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
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 having a little trouble with a query I was hoping someone could help me with. Basically I have the following SQL statement
[code].....
I would like to show all records for the tblCService.CService field however it won't let me change the join to a RIGHT JOIN. I get an "ambigious outer joins" error message. As far as schema tblEList is the main table and the tblCService is a lookup table. Any ideas?
hello everyone,Please can someone tell me if it is possible to create a Join on two tables where the value from 1 field is contained within a field from the other table.eg.table1.field = "xyz"table2.field = "the xyz super thingy"This is the SQL it gave me orrigionally but this is an "= to" join which is not what i want. I tried replacing the = with a LIKE but am still getting the same results.SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno = table2.name;SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno LIKE table2.name;
I have a database with PCs and installed hardware. An external program scans all PCs, the result of the scan can be saved as Access file. Now I need a query to select from each PC the installed hardware (Monitor, Printer...) but only with the latest date. I have written a query MaxDatum to select the the lastest date. Here's my query code:
SELECT [Table1.needed fields], [Table2.neede fields]... FROM (((Table1 INNER JOIN (Table2 INNER JOIN qryMaxDatum ON (Table2.Field1 = qryMaxDatum.Field1) AND (Table2.Field2 = qryMaxDatum.MDate)) ON Telle1.Field1 = Telle2.Field1) INNER JOIN Table3 ON Table2.Field2 = Table3.Field2) INNER JOIN Table4 ON Table2.Field2 = Table4.Field2) INNER JOIN Table5 ON Table2.Field2 = Table5.Field2 ORDER BY Table1.Field1;
My problem: this query mulitply the number of data records so that I have 80 data records with exactly the same content per PC. How do I neesd to change the query to get only one data record per PC?
Now, in tblDailyTripLog.driver, the tblDriverID is stored. Same goes for Destination, Depot & Depart, the tblDepots.ID gets stored which can or will differ....
Now, when do a query, how would I show 3 different joins to the same table (ie. tblDepots) to get the name, instead of the ID for Departing, depot & Destination?
I've tried doing a Left Join and then another LEft Join, but its not working....
it has already 2 inner joins can i use one more inner join within.
strSQL = "SELECT distinct z.location_id,z.location, y.company_id,y.company_name,w.category_id,w.catego ry FROM (appointment_detail AS x INNER JOIN location_table AS z ON x.location_id = z.location_id) INNER JOIN company AS y ON x.company_id=y.company_id ( INNER JOIN category AS w ON x.category_id =w.category_id)WHERE x.expiry_date >=date() ORDER BY z.location ,y.company_name"
INNER JOIN category AS w ON x.category_id =w.category_id
now I need to loop through the whole customer table and pull up all customers where the count of the following statement is more then 1
select count(geographic) as mycount from dialcodes where [phone1]like code + % or if phone2 is not blank then the count of the following statement select count(geographic) as mycount from dialcodes where [phone2] like code + %
I want to return all records from the customer table where the phone fields don't match the beginning codes from dialcodes.
I'm trying to create a database of Publications (instruction manuals) where one publication is a modification to a parent modification. I'm storing all 'publications' in one table for better maintenance. This means that I have to use a Self-Join if I want to make one the parent, and one the child. Fundamentally this is no different than the Employee/Supervisor example I've found in many places:
http://www.databasedev.co.uk/self-join_query.html
I created a query with two copies of the same table with the 2nd one renamed for easier viewing. The query final comes up, but when I choose "ParentPub", it doesn't give me a drop down showing the other pubs so that I can choose one to be the parent.
What did I do wrong? I should be able to open up the table 'tblPubs', and get a drop down menu showing me all the pubs I can assign as a parent.
Hi, I was wondering if someone can give me a possible solution to my join below:
select tableA.name From tableA, tableB where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
