General :: Update Table Based On Form - Create Multiple Rows
Apr 15, 2014
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click() Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset Set MyDB = CurrentDb() Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset) MyRS.MoveFirst
I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.
EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.
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'm working to create a staffing database that houses changes to staffing week over week.
I have one primary table, the "empMaster" table, that stores the employee's name, contact information, etc. I have other individual tables for noting which employee reports to which manager, what their business title is, what group they're in, their training history, etc.
Once I've populated the empMaster table with employee information, I want a form that allows me to update each of the other tables IF there as a change. Some weeks will have a manager change, some only a business title change, some a group change, some a training change, some all of the above. The problem I run into is that I will sometimes process hundreds of changes a week, sometimes only 10-20 so I almost have to use datasheet view for mass edits. I'm relatively new to Access and I'm having a hard time getting my form to allow this level of flexibility and to update all fields needed.
When I build a form that includes more than two tables (let's say I want to update a Manager and a Business Title), the form will not work and populates nothing. I believe it's because Access wants there to be an existing record to match to across all three tables and there will not always be.
How do you create a form that is not tied to one single record source? In other words, I want to be able to select the record source that it updates. I have a bunch of tables that have the same data structure but are separated due to geographical nature among other reasons. Is there a way to do this?
I'm using Access and Excel 2007.... I know how to import an Excel spreadsheet as a table.
I have several supplier price lists in Excel. I want to keep my vendor price lists up to date.
When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.
I believe the form would look like:
Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required. Field: "OEM" (Key Field found in each table) Required. Field: "Brand" (Field found in each table) Not required. Field: "Price" (Field found in each table) Required.
OEM would be the unique key field.
If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes. Or I could enter new data in that vendor price list.
My db has several tables tb1, tb2 tb3 tb4 ..... I have link tb1 to tb2 (tb1Id to tb2)and linked tb2 to tb3 and tb4 (tb2Id to tb3 and tb4) I have created a form with several field from tb1 and tb2 and a single field for tb3 and tb4. All of the fields except one for tb1 are use to make a decision based on what they display. The only field that is updated on the form is a date conversion field from tb1. once you update this field it will auto populate a date field on tb1 with current date. you then have a choice of 4 commands to activate based on what is displayed form the rest of the form.
my problem none of the fields can be updated. can any one help
qry the form is based on
tb1 [Name] tb1 [ID] tb1 [date conversion] 'this is the only field that is manualy updated tb1 [date] 'updated base on date conversion field being updated tb2 [field] tb3 [Yes/no] tb4 [yes/no]
based on what is displayed on the form you have 4 choices of cmd buttons.
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
I have one table, I want to update multiple fields in that Table with values.Currently I have on update query for each update.So for exampleRows: Commercial Quotes, Cover Notes, Personal QuotesWill have the values: Yes, No, Yes in them.I need to update those fields, if they say Yes to Commercial Quotes, Cover Notes or Personal Quotes depending on the field obviously. And blank them out if it says No.Currently I have 30+ different queries I have to click individually one after the other to update each row. I was wondering if theres a quicker way of doing this all in one hit.I have a tiny bit of SQL knowledge.So one of the queries I've figured out I've converted to SQL but again it only updates one instance...UPDATE Enabler_Data SET Enabler_Data.[Commercial Quotes] = "Commercial Quotes" WHERE (((Enabler_Data.[Commercial Quotes]) = "YES"));I tried whacking an OR replacing the ; and repeating that statment replacing Commercial Quotes with Cover Notes but it errored out on me.This is where I get completely stuck and my meagre knowledge fails me so any help would be useful.Thanks in advanceMatt
So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .
Simply I want to update "Null status" to become "Done" according to its numbers according to this list
Code: 1234 53 546 767 2135 6657 43 34
Something like this
I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org .gov .du .pk .dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS FROM tableA WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
I worked yesterday to create a database consisting of a table, multiple querries and a form. Today I went to open it and I got a different database I worked on months ago. The properties say it was created yesterday, but it is not the same database and I can not find the database I created yesterday.
I manage the production schedule of over 150 reports. Some of which are due daily, weekly, bi-weekly & monthly. (some reports have multiple due dates for the current month.) I have the production schedule in a table with multiple DueDate columns: DueDate1, DueDate2, DueDate3, etc. Some reports are due every business day of the week. I need to:
1.) Display the due dates (without having to enter them manually) so I can perform calculations, conditional formatting, etc.
2.) Should I display a column for each due date (31 columns accross for those that are due daily?) or show a duplicate row for that report for each day the report is due?
3) is there a formula that can be written to autopopulate the duedate fields for every instance and every month? (Sales_Report due every Mon, Wed, Fri (5/13/13, 5/15/13, 5/17)
I am currently putting these dates in manually every month and it would be a pain to continue that given our reporting production schedule will only increase.
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
I am still quite new to Access and have spend the last two days looking for a solution to an issue but don't seem to find anything that fits. The problem is that I want the three year average of [NIbyA] inserted in [AvgNIbyA]. The years are stored in [FocalYear] (as Date/Time formate) and [TextYear] (as text formate) and [ID] indicates which entity the record belongs to (see below).
UniqueID ID TextYear FocalYear NIbyA AvgNIbyA 1020180-19981231 1020180 19981231
[Code]...
Example.accdb
I have been playing around with update queries but can not seem to get the syntax right. The query needs to add NIbyA for year 1998, 1999 and 2000, divide it by 3 (or use Avg() function or something of that sort) and insert the result into the row where [FocalYear] = 13/12/2000. To add to the confusion, it should obviously only add those records that belong to the same ID. Years are from 1998 to 2010 and ID's are random (this obviously means that the rows where [FocalYear] = 13/12/1998 and [FocalYear] = 13/12/1999 will not have an entry).
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
Still working on the registration and badge print system for their upcoming exhbition the company I do a bit of work for. Managed to solve most of my problems but have got stuck here.
Basically each of the badges have a barcode (code 39) on them which is in the following format... *1000000123$M*
(*'s required for the barcode to work, the numbers are the badge number and the $M is the enter command (i think))
Basically we need a form that allows us to scan the barcode (the number of the barcode is the Primary Key in the tbl_Attendees), and for this to update the "Attended" (Yes/No) field to yes. This bit I can do with a simple update query, but we need some form of confirmation, something simple like a line of text showing up on the form saying
For a project i'm working on i'm manually entering e-mail addresses, names, and surnames. I would like a username and password automatically generated based on what the corresponding fields' first name and surname is with formatting.
Example: First Name Surname Username Password John Smith johnsmith01 j.smith.01
Is there any way i can do this using input masks and validation rules? Would this require a VBA script?
