But I am getting an error "Missing semicolon( at the end of SQL statement" and placing a semicolon at the end isn't helping either. I can insert a single row so column type or primary key isn't a problem.
Here's the table structure,
rvp - number (pk)
rvp_name - text
controller_id - number
I want to copy data from one table to another: INSERT INTO Table1 (codeid) SELECT codeid FROM Table2 WHERE a=7
it works, but the problem occurs when diplicate entries occur in codeid of Table1 (because is is set to No Duplicates).
As a result no rows are inserted. It's OK, cause that's the way it should be, but I was wondering if there is a way to tell MS Access to skip insertion of values that will cause duplication. So the rows that are ok will be inserted and the ones that cause duplicates will be skipped.
I just don't want to go through the loop and insert row-by-row.
hi i have a main form called "frmMain" that i use for users to add new studentsNumbers txtStudentsNumbers.bound to tblStudents also this form has a combo box called cmbDepartments coming from tblDepartments.
Then a subform which right now it has a combobox called cmbSubDepartments (coming from tblSubDepartments) , this combobox has a rowsource to update everytime someone selects a dept in the main form so it shows the subdepartments belonging to the departments. Each department has a a set of Subdepartments, also has two texboxes called txtTuitionA, and txtTuitionB. so in total i have three tables each with the pk id.
what i need is that when a user enters a student number in the main form, select the department where they want to add the student to, then the subform gets automotically populated with all the subdepartments so then the user is forced to enter TuitionA and tuition B to each subdept for that particular students
i tried this...
(in the main form)
Private Sub cmbDepartment_AfterUpdate() Dim intQty As Integer Dim i As Integer
If MsgBox("Are you sure you want to add a new tuition plans for this student?", vbYesNo) = vbYes Then Me.txtStudentID = DMax("StudentID", "tblStudents") + 1 'get New student number 'Add the new row into Main Table tblStudents
intQty= me.cmbSubDepartments.itemdata -1
For i = 1 To intQty - 1 strSQL = "INSERT INTO tblStudent (StudentID, SubDepartmentID) SELECT StudentID, SubDepartmentID FROM tblSubDepartments WHERE SubDepartmentID = " & Forms!frmStudentsTuitions_subform!cmbSubDepartment s & ";" CurrentDb.Execute strSQL
I use this vba code to insert data in access database 2007. It's working for one row, but when I try to use for more rows and columns give me "Type mismatch".
Code: Sub Simple_SQL_Insert_Data() Dim cn As ADODB.Connection '* Connection String Dim oCm As ADODB.Command '* Command Object Dim oWS As Worksheet
I have a database that needs to post records that are joined from three different sources into a table. The query is done, and I get about 1,489 records out in 4 different states.
What I need to do is make a table with these records. Furthermore, it must be separated by state, whereas if there are less than 1000 records for each state, it must insert blank lines until it reaches then next thousandth (sp?) row (i.e. 1001, 2001, etc.), and then start posting the next state.
For example, AZ has 420 records. There has to be 580 blank lines before the query can start posting the next state, CA. At row 1001, CA starts posting, but there are only 200 records for CA so there must be another 800 blank lines before moving on to CO at row 2001, etc. etc.
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
I was given a task by my boss to create an appraisal system for our department here in one of the local schools in the Philippines.
Anyway, what I created first a form from which all subjects being offered will be displayed. I did this by having a main form (bind to personal info table) and a subform that displays all the subjects (bind to subjects table). Then, the user will simply click on the appropriate rows bearings the subejct that the students wish to enrolled and when the save button is click, such will be saved into the enrol table.
My questions is, what control will I use to allow me to select multiple rows? and how will I be able to save selected rows into the enrol table?
I have a table of products and a table of ordersEach product requires multiple processes to completeCan I make a query that will lookup the products in the orders table and show a list of all the processes that need to be done to complete all the products on order?The bit I dont get is how can a query return multiple rows for each product?
Could somebody please help me! I am new to access and trying to create a database that brings together information from a number of different sources, into one large database. I need to import a large amount of data from another access table and I would like to just cut and paste it into my table. BUT, I can’t figure out how to add multiple rows at once to my existing table. The data that I am importing contains about 6,000 rows and 4 columns. Can anybody tell me how I can do this without having to manually create 6,000 rows, one at a time? I really do not want to do that. Thank you for your help.
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..." * on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.
This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.
I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.
**Account Number and Sub ID fields in both tables are not unquie.
Data Table: (this table has alot more data but this gives you the idea.) Account_NUM - Sub_ID - Data1 1234 | 3 | 123 N Inc. 1234 | 3 | 666 DRN 4567 | 4 | 543 S Way. 7890 | 5 | zzz ABC
Notes Table: (Yes the notes field is a MEMO field) Account_Num - Sub_ID - Notes 1234 | 3 | notes1 1234 | 3 | notes2 1234 | 3 | notes3
The output I am trying to Get: Account_Num - Sub_ID - Data1 - Notes 1234 | 3 | 123 N Inc. | notes1 notes2 notes3 1234 | 3 | 666 DRN | notes1 notes2 notes3 4567 | 4 | 543 S Way. | NULL 7890 | 5 | zzz ABC | NULL
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number Smith |O101 Brown |O102 Smith |O103 Green |O104 Brown |O105
I am trying to achieve below:
Customer Name |Order Number Smith |O101, O103 Brown |O102, O105 Green |O104
I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT 292020 M 5/11/2012 ($33.95) 292020 11 5/14/2012 $33.95 292020 A 5/30/2012 ($33.95)
HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.
I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.
What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3 V.I. Capital Appreciation Fund.................5 V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote V.I. Capital Appreciation Fund.................3, 5 ,1
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
I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
I have three tables, with the following data (fields separated by "-" here): Unit - Customer Number - Customer Name - Type of A/R - Total A/R Unit - Customer Number - Customer Name - Type of A/R - Total Overdue Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names: Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this: Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
