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 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 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 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 am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example: *"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
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.
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?
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
What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.
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 new to access 2010. I have a table called "Forecasts", and I wish to update the timestamp with todays date, whenever the user makes a change to the qty of goods forecasted.
I have been researching online for the solution to the correct format to datetime, but it doesnt seem to work.
Code: Dim todayDate As Date todayDate = Date Dim sqlString As String sqlString = "UPDATE [Forecasts] " & _
[Code] .....
The messagebox shows:
UPDATE [Forecast] SET Branch_Plant=123, Item_Number_Short='222', Description='AAA', UOM='EA', Estimated_Cost=123, Requesting_Business_Unit='AAA', End_Customer='CCC', Project ='Secret', Timestamp=#26/12/2013# WHERE ID =24
Then I hit a syntax error. Whats wrong with sqlString?
[1] AvailableCars consists of fields as Zone (eg. ZoneA, ZoneB, ZoneC) , CarCategory (eg. CarAA1, CarAB1, CarAA2 ... and so on), DailyRates, and Valid DateBounds (FromDate, ToDate).
[2] CarPics consists of CarCategory and Carpicture (Path of the car image).
Now I want to Bring Only those cars from the AvailableCars table and CarPics which satisfy the criteria of falling between Valid Date Bounds...I am using join as the following :
SELECT AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY, AVAILABLECARS.DAILYRATES, AVAILABLECARS.FROMDATE, AVAILABLECARS.TODATE, CARPICS.CARPICTURE FROM CARPICS JOIN AVAILABLECARS.CARCATEGORY = CARPICS.CARCATEGORY WHERE AVAILABLECARS.ZONECODE = "ZONEA2XY" AND AVAILABLECARS.FROMDATE BETWEEN "DATEVAR1" AND "DATEVAR2" ORDERBY AVAILABLECARS.ZONE, AVAILABLECARS.CARCATEGORY
Using above query, I am not able to get the desired results. :o
Can Any body help me in solving this problem ? :confused:
I need to write a query which retrieves all the records from first table and those matching from second table and also satisfies a criteria.
I had
Table 1 F1 F2 F3
Table 2 F4 F5 F6 F7
I had to get all the records from Table1 and also those matching with Table2 So i did left join and getting all the records. And I also need to check whether F3 in Table1 is in between F6 and F7 of Table2.
So I wrote query like this
SELECT Table1.F1, Table1.F2, Table2.F5 FROM Table1 LEFT JOIN Table2 ON (Table1.F1 = Table2.F4) WHERE Table1.F3 BETWEEN Table2.F6 AND Table2.F7;
I am getting only those records which are matching where condition. I need to get all the records including which match the criteria and also those which don't match.
I am trying to create a query which shows only the newest note for every prospect. I have the code below, but I get a 'Syntax error in JOIN Operation' when I try to run the query and TBLNotes is highlighted.
SELECT TBLProspect.CompanyName,TBLNotes.Note, TBLNotes.NoteDate FROM TBLNotes AS a INNER JOIN TBLProspect ON TBLProspect.LeadID=TBLNotes.LeadID WHERE ((((Select Count(*) from tblNotes where LeadID=a.LeadID and NoteDate>=a.NoteDate)) In (1))) ORDER BY a.LeadID, a.NoteDate DESC;
I hope someone out there can help me, I'm not an SQL experianced person, I only know enough to make problems. Anyway I believe my problem below is probably simple to someone that knows SQL very good. I have a link to a sample Access database if anyone is interested in helping me solve this.
The Sample Database (http://shadesoffire.net/files/db1.mdb)
Have a problem and can't quite seem to get it solved. I have simplified the whole thing to make it easier to understand.
I want a report that shows me the Product Category and Sum from work orders that were > $100.
I'm not quite sure how to do this, I've tried several methods of Joining and Sub queries but nothing really works. All I get in my results is product categories > 100. See I would expect the product category to be on the report even it it were $55 if it came from a work order containing total costs > 100.
I have manually supplied the results that I want, any help would be greatly appreciated. My real problem deals with larger results and numbers but these are the basics.
Linked is an Access database with one of my queries that show all work orders > 100. I would expect all those product categories in my summary report.
Redesign of the database is not an option.
These work orders are all > 100, the total on all reports should be $511
WorkOrderNo Total Cost 00001 $120.00 00003 $268.00 00005 $123.00
These product categories are associated with the above work orders and this is the result I am looking for.
Hi all, this is my first post to the board. I’ve been searching through the forums but haven’t been able to put all the pieces together on my problem. First some background on my application: I have 3 tables ISSUES, UPDATES, and PRTS. Each record in ISSUES has several records in UPDATES, and may have a single record in PRTS. The issues and updates tables have 3 “clones” (everything except the table name prefix is the same) that are used by other departments. All of the clones use the same PRTS table. I am trying to put a query into SQL (I first built it using the query builder & an example from Microsoft for a query to find the last record) that will return each issue, along with the last update associated with the issue. My intent is to switch the table names using code depending on the user’s selection. I know how to switch “ElectricISSUES” to “PowerISSUES” below, but I do not know how to switch the source for the “qryLastUpdate”. Any help would be greatly appreciated.
issueSQL = "SELECT ElectricISSUES.Index, ElectricISSUES.Status, ElectricISSUES.Description, ElectricISSUES.Driver, ElectricISSUES.Champion, ElectricISSUES.PRTS, ElectricISSUES.SubDate, ElectricISSUES.DateClosed, ElectricISSUES.Diamond, Date()-[Subdate] AS DaysOld, qryLastUpdate.Update, qryLastUpdate.Udate, qryPRTS.Step, qryPRTS.[Next Step], qryPRTS.Designee, qryPRTS.[Ewo#], qryPRTS.BreakPointDate " & _ "FROM (qryLastUpdate RIGHT JOIN ElectricISSUES ON qryLastUpdate.UIndex = ElectricISSUES.Index) LEFT JOIN qryPRTS ON ElectricISSUES.PRTS = qryPRTS.[Prob#] " & _ "WHERE (((ElectricISSUES.Status) = 'closed')) ORDER BY ElectricISSUES.Index;" db.OpenRecordset (issueSQL)
Hi all How are you? I have a small question.... I have a db with 2 tables (Contacts, Products). Contacts table has 6 columns (CustomerID, CustomerName, Bread, Tea, CD, Pencil) Products table has 2 columns (ProductID, ProductName). I want a query that show this result: Customer NameProduct Name JohnTea JohnCD MarkBread MarkCD MarkPencil JaneTea TonyBread TonyPencil
How can I do that?? Kindly, find attached the data base. Sorry for bothering you.
Head is battered with this join query. I have 2 tables which I want to join, one being individual data and one being budget data. I want to Sum the budget Cost And Budget admissions dependant on the joins.
SELECT FyldeCoastActivity.Speciality AS [Specialty Code], dbo_Specialty.SPECTEXT AS [Specialty Desc], FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type], Count(FyldeCoastActivity.Activity) AS CountOfSUSActivty, Sum(FyldeCoastActivity.BasicSpellCost0708) AS BasicCost, Sum([PBC month 8 budget].[Budget Admissions]) AS [SumOfBudget Admissions], Sum([PBC month 8 budget].[Budget Cost]) AS [SumOfBudget Cost], [BasicCost]-[SumOfBudget Cost] AS [Cost Difference], [BasicCost]/[SumOfBudget Cost]*100 AS [Cost %] FROM ((FyldeCoastActivity LEFT JOIN dbo_Specialty ON FyldeCoastActivity.Speciality = dbo_Specialty.SPECCD) LEFT JOIN [Al Providers] ON FyldeCoastActivity.Provider_Code = [Al Providers].Trust_Code) LEFT JOIN [PBC month 8 budget] ON (FyldeCoastActivity.ProviderCode1 = [PBC month 8 budget].[Provider Code]) AND (FyldeCoastActivity.Speciality = [PBC month 8 budget].[Specialty code]) AND (FyldeCoastActivity.GP_Practice = [PBC month 8 budget].[Pract code]) AND (FyldeCoastActivity.[Activity Type] = [PBC month 8 budget].PODSUS) WHERE (((FyldeCoastActivity.[Financial Month])<=8) AND ((FyldeCoastActivity.ExcludeReason)=0 Or (FyldeCoastActivity.ExcludeReason)=99 Or (FyldeCoastActivity.ExcludeReason) Is Null)) GROUP BY FyldeCoastActivity.Speciality, dbo_Specialty.SPECTEXT, FyldeCoastActivity.GP_Practice, FyldeCoastActivity.[Activity Type] HAVING (((FyldeCoastActivity.GP_Practice)="p81129") AND ((Sum(FyldeCoastActivity.BasicSpellCost0708)) Not Like 0)) ORDER BY FyldeCoastActivity.Speciality, FyldeCoastActivity.[Activity Type];
The query is coming back with Very high figures which leads me to believe that the joins are not working and just summing all the data in the budget table.
If I were to do a right join on four different attributes E.g. a = a b = b etc etc and the criteria would be Attendance is null. I am getting the results back which I want but the columns which I have joins on are blank? Any ideas why?
