Queries :: Update A Field If Find A Match In Another Table
Jun 29, 2015
I have a table Billing_Temp that I need one field updated if I find a match in another table Random_Temp. I runt the query and it prompts for "Enter parameter value: Random_Temp.peopleID... what could be going on? Both tables have a field called peopleID and always Billing_temp has many more records than Random_temp:
UPDATE Billing_Temp SET Billing_Temp.audited = -1
WHERE (([Billing_Temp].[peopleID]=[Random_Temp].[peopleID]));
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria: ID - primary key DiscountID - only when the DiscountID = 92 Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria: ID - primary key code = text field with a code like "einstein01", "einstein02" Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers; SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME FROM CPL_ACCOUNT WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE]))) ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.
I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".
When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.
My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.
How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
I have 2 tables in access. One is a table with the us state abbreviations.
I have another table, one of the fields is an address field, e.g.
SOQUEL CA 95073 SOUTHAVEN, WA 98671 SOUTHBURY, CT 06488 SPENCER IA 51301 SPOKANE, WA 99201 SPRINGFE VA 22150
I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:
Address field/ Abbreviation field SOQUEL, CA 95073/ CA SOUTHAVEN, WA 98671/ WA SOUTHBURY, CT 06488/ CT SPENCER, IA 51301/ IA SPOKANE, WA 99201/ WA SPRINGFE, VA 22150/ VA
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.
I will explain what I mean with a hypothetical situation.
So, let's say I have two tables: WindowT1, CustomerT2.
WindowT1 has fields: WinID (the pk), WindowName. CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.
So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.
My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.
Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)?
I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
My new table "tblRRP" Could contain Year int, Week int, CountryCode nvarchar (2), ProductId nvarchar (15), RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.
What I did was:
1. Read tbCOMPANY each row using for loop 2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT 3. If tbCOMPANY.noMatch, it will insert into tbRESULT 4. else it will update the matched row in the tbRESULT
Its been taking like over 30 minutes to process this and still ongoing and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this?
Code: Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("Complaint", dbOpenDynaset)
[Code] ....
I find the record, and then try to update a field. The drop down menu field gets updated after it is initially changed from blank to a value, but it never changes after that though the re!C02 field has the correct newer data when I change it again.
I have built a nice database that has a form to enter data which logs in product received, there is a combo box on the same form that is linked through the query builder to auto populate the names from the contacts info table (the receivers of the product received) the contacts info table also contains information that is specific to each name such as locations.
As of now I have created a command button that brings me to the form that shows the information fields I need specific to a name, once I get that I have another command button to bring me back to the main form. How to create an additional list box on the main data entry form so as when the name is entered the new list box or text box (which is best?) will auto populate the information I need on one form instead of going back and forth.
Example:
Requester Name [ auto populate name ] currently linked to contacts info table (working)
(New field) Preferred Location [ need to auto populate location ] from the contacts info table (how do you pull locations specific to a contact name from the same table?
I am putting the finishing touches on a DB and have come up with a a problem which I cannot solve. I have code on my data entry form that concatenated the days and times of up to five days. That is : [Day1] & " " & "@" & " " & Format([Time1],"h:nn AM/PM") & " " & ";" and so forth for five days. It has a complicated (for me) nested IIF statement and it works fine on the form. But I have now transferred the data from an old DOS DB and I do not want to go through almost 5K records just to update three fields.When I tried to do it in an update query, it said that the string was too long for the update. My update is:
how to update the table if a field is blank without clicking through all 5K records? I have tried simple concatenation, but I usually get a lot of extra @ in the field.
I have a created a query which I want to update a table. The field is called DTP. I have the Workingdays2 module in place as well as the Holidays Table. The dates (date only) are called Time in and Transaction date. I want the table to update records only if the entry in Completed is Yes. I have attached screenshot of what I have in query. I am no good whatsoever at VBA so I used the Query design view. It shows how many records will be updated (which is a valid number) and shows me which field will be updated. However when I run the query and then opend the Table the DTP field is empty.
My update statement is shown below. What this is to do is change the PGTIN record in PP TBL to the GTIN that is in the UPC TBL where the UPC Code on the form is the same as the UPC Code in the UPC TBL. I am getting the error message "can't find field..." I have double and triple checked the field names but cannot find my error. Both PGTIN and UPC Code are text fields.
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
Using sql or access query I would like to create an expression that aggregates the first field and I would like to see all records grouped by the relationship with another field. Let me show an example.
My query shows:
field1 field2 apple a apple b banana a carrot a carrot b dog b elephant b
I would like my query to now display a third field and group field :
field1 field3 apple both banana a carrot both dog b elephant b
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
I have a table called Attendance were information about the Student and the class they are attending is collected.
Another table called Student, this hold information about the student. I am using a Form called Attendance to input my information. What I am trying to achieve is:
BeforeUpdate on a text box:
When you input Student_ID it looks at the Student Table to see if the Student ID is valid before the information is updated to the Attendance Table.
I've inherited a database with many queries (most based on a single table). There is a requirement to change the name of a few fields in the table. Is there a way to find ALL queries/reports that are utilizing the fields that need to be changed so I know what queries need to be modified?
This is probably dead simple, but I am brain dead today.
I have two tables: Requests with fields (ID, Cust, Amount, Ref, Date)
and
Actuals with fiels (ID, Cust, Amount, Ref, Date)
Now, if Requests.Ref is null, then update Requests.Ref = Actuals.Ref and Requests.Date = Actuals.Date if and only if there is only one record in Requests and only one record in Actuals where Requests.Cust = Actuals.Cust and Requests.Amount = Actuals.Amount.
Currently, I am just doing an inner join between the two tables, but if there are two requests with a given cust/Amount, but only one Actual, then both Requests will get the same Actual Ref and Date.
