Queries :: Compare A Field In Another Table And Update Accordingly
Jun 25, 2015
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.
Hi I'm using MS Office 2000, I have an Access database table that contain many fields, namely I need to update FieldA from an Excel Spreadsheet according to the specified primary key of CustomerID. Is there a way to write automate process to import the records from an excel spreadsheet and then place those records in FieldA of the Access Database according to the corresponding CustomerID? The reason I ask is that these two fields are not exactly in the right order in my spreadsheet and my table and to do it manually will take lots of effort.
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 running into an issue when trying to compare data stored within the same table.
tblParcels - holds data relating to an individual parcel of land; unique identifier is ParcelID. No repeating data.
tblValues - holds data relating to the value(s) of each parcel; new values entered annually; unique identifier is TaxID; has field to link to tblParcels. One parcel can have multiple entries for each year tracked.
I have two unbound text boxes on frmMainForm where users can enter Tax Years to compare (txtYear1 and txtYear2). Theory being that users will be able to select any two tax years and compare the parcel values.The report (rptMaster) is built using three queries and is used to show the change in value between txtYear1 and txtYear2:
qryYear1 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear1.
qryYear2 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear2.
qryCompare - joins the results from qryYear1 and qryYear2, along with other common fields, and populates rptMaster.
My problem lies when a parcel does not have any values for one or both of the years entered into the text boxes. I completely understand why, qryCompare can't show any records if there are no results coming from either qryYear1 or qryYear2. What I am not getting is how to work around this issue.I would like for the report to show "$0.00" for the value if there is not a value for that Parcel in tblValues for the year entered.
tblValues example data: TaxID ParcelID TaxYear Value 1 5 2011 100,000 2 5 2012 200,000 3 6 2012 75,000
In the example above, when comparing 2012 values to 2011 values, my report shows the values for ParcelID 5 but not for ParcelID 6 as it does not have a 2011 value (and won't since information about that parcel didn't exist in 2011).
I have a form that users put in information and then it updates to a table. It works fine but i would like to add something. Right now there is a text box called item that the user puts in the item number. The problem is that sometimes they put in a wrong number. What i would like to do is have it when they hit the update button to compare that item number to a table that lists all of them. so if the text box doesnt equal one of the numbers in the table dbo_item and the field itm_num, i want it to give them a message box with an error.
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'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table: ProductName Chemical ChemicalAbstract PhysicalState NFPAHealth NFPAFlammability NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
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.
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)?
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 need to campare two tables. if there are new records from table A would like to add it to table B. Also if there are any changes in a field of record i would like to see the change in table B. thank you Mark
Hi all I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this. Thanks in advance Craig
Have devloped a database to log all support issues for an application I work with.
I periodically update the database by importing from another database
for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS
I then import data from tbl_import which also has these fields. The following query works fine:
UPDATE tbl_support SET tbl_support.STATUS = tbl_import.STATUS WHERE tbl_support.STATUS <> tbl_import.STATUS AND tbl_support.IR_NO = tbl_import.IR_NO
However I also want to do this at the same time:
UPDATE tbl_support SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION AND tbl_support.IR_NO = tbl_import.IR_NO
i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5
Import on monday Product ShippedQty Date 123____ 0 ________01/01/2013
Import on Tuesday Product ShippedQty Date 123____ 1 ________01/01/2013
I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)
I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g. if Name = Anna B Company = Corp&Gov then Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if Name = Anna B Company = Dealer then Customer = Dealer
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
I have a query it correctly displays output as i require. i want to update/store the output query to a table named ustate. my query is as under
Code: SELECT Auth.nit, UC+UL+UC AS Aut, (select count(NO) from Pers where nit=Auth.nit and rOrd>4) AS Present, (SELECT COUNT(no) FROM pers WHERE nitFrom = auth.nit) AS DIn, (SELECT COUNT(no) FROM pers WHERE nitTo = auth.nit) AS DOut, Present-Dout+Din AS Held FROM Auth WHERE (((Auth.Type)='tata')) ORDER BY Auth.nit;
Here is what I am trying to do: I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Any easiest way to implement this? (create a new table (which is duplicate), and then compare them?
