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.
I want to insert data into a table where a symbol does not already exist for that date. I am not sure what i am doing anymore :( So please help me verify that this won't insert duplicates? Am i using the correct logic? :confused:
"INSERT INTO StocksData (Symbol, [Security Name], [Market Category], " & _ "[Reg SHO Threshold Flag], FileDate) " & _ "SELECT DISTINCT Symbol, [Security Name], [Market Category], " & _ "[Reg SHO Threshold Flag], '" & fileDate & "'" & _ "FROM " & sTableName & _ " WHERE ((Symbol Not In (select Symbol from StocksData)) AND (" & _ fileDate & " Not In (select FileDate from StocksData)));"
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
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 know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
i have a report that opens from a query that pulls from 8 different tables using the date as the primary key for each table. I want to be able to open my query without having to input a blank record for each date in each table. Basically I don't use every table everyday. Sometimes I just use 3-4 tables and others I will use all 8.
I run a query that I export out to a tab delimited file. This text file is used for another application. The problem I am having is if I select "skip field" in my text "export specification" it still exports out that piece of data, even though I have selected "Skip Field". The data type on this field is a number and field size is integer.
I thought I read in another access newsgroup that this is a known bug? Is that true? Is there a fix? I am anxious to fix this because, I have 90 query's that I export to text and I would hate to have to go to each query and select "Not Show" that field.
Currently, I have a button on my form that users can click on to generate a report based on the info from the form. Once the report has opened, they publish the form with MS Word for further edit if needed.
How do I set up the button so that it opens the report then automatically publishes the report with MS Word?
My form is always open and runs a timer event which runs a series of functions and procedures. I don't want to run a certain procedure on every timer cycle, but rather, every other timer cycle.
Any thoughts on how best to accomplish this. I'm having a mental block!
I have a form which displays the values of a table Vehicles. It has a control at the bottom of the form that allows you to toggle through the records, or enter a specific number of a record for it to jump to. It starts at 1.
I want it to skip the first 3 records in the table, and start at number 4. Is there a simple way to do that?
Is there a way to delete the borders from a form? You know the ones from the bottom and right hand side which have the buttons on for you to skip between records?
Also the one on the left which has the arrow in the top corner.
Put simply - when I do a find it displays the first account that matches it, then, when you leave the find screen and hit the "next record" key it simply displays the next account (i.e it no longer matches the search criteria).
So my question is, How do I search records, then skip through ALL accounts that match the criteria.
example: 100 accounts in a database. 50 accounts are assigned "Mr A" and 50 "Mr B". Mr A and Mr B sit down to work - hit find, and type in their name. The form shows 1 account, and when mr A hits next fifty times it should not move on to mr Bs (possibly a "no more matches/records" message?).
I hope this is clear enough - and thanks in advance.
ps - I heard something about one form to search - and a subform to display results - the search criteria is static in form one, where as the subform can be skipped ("next record" pressed). But trust me - Im inexperianced :p and would need a nice newbie friendly description of such technological witchcraft.
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Many Regions, Many Towers, Many Countries and all..
Has to create a worksheet for Each Region-Tower and Paste the countries' records.
Private Sub Input_Click() Dim Mainrset As Recordset Dim Temp As Variant Set Mainrset = CurrentDb.OpenRecordset("Query_Form") Mainrset.MoveLast Mainrset.MoveFirst
[Code] .....
The above code is not correct as some Tower/Process are not associated with some countries. Usually What I do is to loop through all the records and look for the changes in the field. Is that the only way?
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results. after item 2, do an export. etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt, [a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.
i've been working on a sample database today that someone has written some vb code into, which enables the database to merge with some dialler software. basically it's just a hang up and dial button on a form for surveying people.
i've done ok extracting the code for the dial and hangup buttons, referencing the external file and getting my test database to interface with the dialler but it currently allows multiple users to edit the same record, which i need to stop it doing.
the guy that's done the coding says he's more of a vb programmer and not much use in access (he's definitely better than me!) but i should be able to insert some code that will skip to the next record if the record is being worked on with someone else.
i think the piece of code that i need is
Forms("Form1").RecordLocks = 2
the only problem is i don't know what else to type with it or where in the code to insert it.
