Queries :: Append Query - Automatically Update Tables Depending On Value Of Yes / No Field
Feb 25, 2014
I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.
So, the best way I saw to go about is to set up an append query and then create a macro that runs it
So my tables have the values FirstName, LastName and isValid (more but keeping it short)
So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it
I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.
I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.
Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?
I have a form (Access 2010) that we use to keep track of client data. When I enter a number equal to 6 or above in one field (Client Rating and this field is actually formatted as text because it could be 6 or 6a, b, c or 7 or 7a, b, c, etc), I want anything =>6 to automatically check a box in another field which is the Watch Indicator box (a yes/no box), how would I do this?
I tried to create an expression like the below on the Watch Indicator check box and it does not work - not sure why - it did not say it was incorrect, I think I may have it in the wrong place?
I have an option button for a field [Fully Paid] (Yes/No) in my LoanT and i want to know if it's possible for my database to automatically choose an option depending on a calculated field
(Calculated field from another query) = [Outstanding Balance]
Say... if [outstanding balance] is = 0 or negative (refundable), then Yes should be On, if not it should remain No/off so that i wouldn't have to go over every record and manually choose the option
The option button is very useful when i want to display loans which are Paid and/or still active. How do I go about this?
In access Im working with two tables, this is my setup
tableA.documentnr tableA.revison
tableB.documentnr tableB.revision
Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).
Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)
Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.
I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables.
I'm after a piece of code which works as described below.
I have a save button on a form
I have a append query ready to run.
When the save button is clicked, I want the code to see if a checkbox is true or not and if its true I want the append query to run if its not then I want the form to save and nothing else.
I receive the data from engineering team lets name it as MAster database( in DB2 database - Read only ) and then i want to assign the data to each member and staus will be updated by them.So my Idea is upload the data from Engineering team (MAster database) to my (Local database), i think of using Append query if there is any changes in engineeiring data after i append the data then i can use the Update query to modify the data.
But once i assign the job to every member from my Localdatabase, i want it to stored in a data base with their name because it is a huge volume data and it will go for years.The bottleneck is If i use the Append query here, the changes from Masterdatabase to Locadatabase will not reflect in Team members database? is any solution for this or i have to use the Update query in Localdatabase and also in Team memers database?
I wonder if anyone can help me out on a db i have inherited. Basically I need to add a field to a table and backfill the field with a number depending on a date field.
The new field (CallID) needs to record the order in which calls are received on a certain date (CallDate in the table). So for example the first call on 1/3/05 CallID=1, the second on 1/3/05 CallID=2. The following day CallID restarts at CallID=1.
So I'm looking for an update query which will set CallID for the several hundred existing records in the database. If anyone can help with a pointer or too, or example SQL, I would be very grateful
tblTreatment contains Cost for different AppointmentTypes.
The above two tables are linked as 1-M relationship (1 Treatment can have Many Appointments)
Entering data in the tblAppointment - AppointmentType and AppointmentCost are the fields in tblAppointment. AppointmentType a drop down box, so that a specific AppointmentType can be selected.
Problem: Now depending on the AppointmentType selected the AppointmentCost field should automatically display the correct Cost.
I realize there are several threads already dedicated to this particular topic but my situation is slightly different... First, my tables are set up like so:
There are 3 racks to a room and a total of 90 rooms. A room can hold up to 3 students. A list box holds values that I have typed in (the room numbers). I went with typing them in rather than pulling them from a query because of the way my tables are set up. If I pull from a query as the tables stand, room numbers show up 3 times, once for each rack. I want to be able to fill in text boxes with information on students assigned to the room I choose from my list box (FName, LName, etc...) I'm kind of at a loss here on how to go about getting the info that I want. Using the column property to populate text fields is probably going to be much easier but a query is needed (I think) and using a query causes room numbers to show up 3 times in my list box... Anyone want to throw me a bone?? Thanks! :)
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
I want to set a table field's default value to whatever is displayed in a certain field on a certain form at the time.In other words, say I have a database with a table called TABLE1, and two fields called NAME and SCHEDULENUMBER. I have a form called CreateSchedule with a SCHEDULE NUMBERCONTROL form and a NAME form, and I can enter names onto it, and it records to the proper SCHEDULENUMBER. So if I pull up SCHEDULENUMBER 4, and add three names, when I go back into TABLE1, I can see those three new names, and each one has the SCHEDULENUMBER set to 4.
What I'm trying to do is write an APPEND QUERY to copy a list of names from a different table, and paste them into TABLE1. The problem is that the other table doesn't have a SCHEDULENUMBER field. What I want to do is put a button on the CreateSchedule form that runs an APPEND QUERY, and sets the SCHEDULENUMBER to whatever value is displayed on CreateSchedule's SCHEDULENUMBERCONTROL field.
I tried setting a default value in TABLE1's field properties for that SCHEDULENUMBERCONTROL field, but I keep getting error messages. I just want TABLE1, whenever I add a new record (regardless of how I add the record: manually typing it or clicking the append query button) to look at the form CreateSchedule, and set it's own SCHEDULENUMBER field to whatever is displayed in CreateSchedule's SCHEDULENUMBERCONTROL form.
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
I have have a "master table" with Analyst ID and Analyst fields among other fields needed. Analyst ID is a number and Analyst will be the name of someone that corresponds to that number. I have a separate table that defines who is assigned to that particular ID.
1 Kim 2 Sarah 3 Beth
I have a form for this master table that shows Analyst ID and Analyst. I would like the default value of the Analyst field to be the name that corresponds to the Analyst ID number for the record. I would also like this same field to be a drop down on the form so that my users can change it as necessary. IE. If the record shows Analyst ID =1, the value for Analyst will show "Kim" unless changed to another analyst manually per the drop down.
I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).
Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:
White Black Grey
VW
BMW
Audi
And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:
I am currently handling an insurance operation. I have 5 Sales Executives (SE) who receives certain percentage of commission for each sold insurance policy. SE receives their respective commission on a pro-rata basis. Meaning, if they give 4 equal monthly payment scheme to their clients, they will also receive their commission -- 4 times.
Example: SE Commission for one sold policy is: 100.00. (Granted SE gives 4-month-term, SE will be receiving 25.00 monthly, upon cleared payment)
On my 2 tables lies the following fields: [Table1] SECom1 SECom2 SECom3 SECom4
Just wondering if someone can point me in the right direction so that i can solve my problem?
Basically what i have is a select query that carries out some calculations based on data entered. These calculations are expressions as i am sure you guys know. what i want to do is put the value from the expression/calculation into my table in the correct fields made for these values. However i have tried everything i can think of to get this data into the tables fields but to no avail.
example. Expr1: [field1]*[field2] the answer created by [Expr1] is the value i want to be placed in [field3]
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last', IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-')) AS IncidentYear, Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days FROM (tbl_2c_Acc LEFT JOIN tbl_Value_Proposition ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No]) LEFT JOIN tbl_Vehicle_Type ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No] GROUP BY IncidentYear;
I'm fairly new to using Access for any serious purposes. I'm trying to replicate some database functionality I setup in Salesforce and just trying to clear few core concepts in Access.
I'm messing around in the Contact Management template and I'm wondering how I can make it so when so when a yes/no checkbox field (called "Active") is ticked a date/time field (called "Last Date") is automatically set to today's date + 60 days.
In Salesforce I would create a Workflow Action triggered by an if Active = true statement, with a Field Update something like TODAY() + 60.
I can tell I need to create an After Insert/After Update Data Macro but can't quite figure out what to put in.
Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.
Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.
I have created a simple login form that is based on User Name and Password.the table contains UserName, First Name, Last Name, Password and User Role(dev, admin...ect.)How can I change who can access different parts of the database?I want all the Development tools only accessable to People who have "dev" as a "User Role".Admin can only access the forms related to data entry and managers can view all forms but not the tables, querie and tools...ect.
