Why is this querry giving me an error in syntax??
I am doing this...
set db=Currentdb
strSQL="insert into processus id= '" & txt_id.Value & "', libelle = '" & txt_libelle.Value & "',version= '" & txt_version.Value & "',type= '" & cmb_type.Value & "',cle_tri= '" & txt_cle_tri.Value & "' "
db.execute strSQL
I think this is right, but it gives me a message saying "error in INSERT syntax"
Thanks in advance
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
Hi,
I had a query which used to work perfectly fine, then I changed one of the field names that the query was returning. And then, I rechanged the same field back to what it was and I save it. Later, whenever I run that query, it asks for a parameter with the same field name I changed even though I don't have it anymore.
Any suggestions why this is happening please!!!!
Regards,
B
:mad:
I have written 2 SQL queries which I wanted to combine using IF-Then statement, if possible.
the first one:
SELECT NewNumber as [Main_number]
FRom try_SQL
Where Type='A' OR type ='V'
The second one is:
SELECT OldNumber as [Main_number]
FRom try_SQL
Where Type<>'A' AND type <>'V'
I want to output the Main_number as a text file on my D drive.
At the moment I have to to output 2 different text files, then combine them.
can someone please help me?
Hi,
Can some one please tell me how I can use a delete query. I have the following code, but it does not work. Please let me know where I am going wrong
---------------------------------
Dim StrSQL As String
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & PLANT_CODE_txt & " ' "
DoCmd.RunSQL StrSQL
----------------------------------
In the above and below code PLANT_CODE_txt is the form field that I am taking values from to delete from the table
(or)
----------------------------------------
Dim StrSQL As String
dim pt_code as string
pt_code = me.PLANT_CODE_txt
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & pt_code & " ' "
DoCmd.RunSQL StrSQL
--------------------------------------------------------------
And if we are using multiple delete queries should we have the DoCmd.RunSQL <variable> after every delete statement or is it after all the statements
Thanks
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
What do I add to do the count?
Original Table
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90
Needed Output
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
A B 12/1/07 42 789
E F 9/1/08 78 750
I need to create a query which extracts rows with wgt >60 and also WHEN the fields ozip, dzip and shipdate are same, the query should add the wgts and then sum of wgts should be >60- for example the first and fifth rows should be extracted since sum is 62.
I have attached the output that is required for the above example. please help
Hi,
I have an indexed table (unique autonumber) in Access. I have in this table an other column with every X rows a number (X varies) and the rest of the rows below are empty. This situation repeats again with another number and again all the fields under it are empty. Is there a possibility to run an update query to update all fields below to that certain number? Can you define a range (by using a range in the autonumber) to update all the fields in that range to that specific number ??
PLEASE HELP!! :(
Thanks!!!
Cheers,
Hi Folks,
I have a table that has Student_ID, Course, and Grade. So each student has multiple records, one for each course they took. But what I want to return is only those people who have All F's. If I just put "F" as a criteria on the grade field then it will pump out those records that have an F, including those who maybe only have 1 F, but all I want is a list of Student_ID's of all of those people who have nothing but F's. Any ideas? HELP..i'm really in a bind with this one.
How can I get the record with a date field that is the earliest and the latest in a table.
tblSample(ID, Name, Type, ItemDate)
The ItemDate can be any date entered by the user, so the ID will not give me the earliest and the latest record. How do I make a query that will give me the earliest ItemDate and the latest ItemDate. I need to do this in Access. Thank you.
I am trying to create a sequential number field in a query. I cant use an auto number for the fact that i am combining multiple tables. Any help with this would be greatly appreciated.
View 14 Replies View RelatedHi,
Not able to add more column heading in cross tab query.
I tried to change the query properties to add more column headings as given below.
In the query's Design view, right-click up in the area where your tables are shown and choose "Properties" from the right-click menu. The 3rd line down is for Column names. Enter what you need there.
Evn after doing it. i am not able to .
Whn I try to save or view the datasheet it says. to create a crosstab query u need to have one or more row headin one column headin and one value.
please help. its ver urgent.
thanks in adv..
Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
View 3 Replies View RelatedI am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedI 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:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15
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:
WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15
I work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.
Because the source table of the query is also updated.
I upload the database with a table in it and a query. I want to add the data of this query to another web table....
Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.
So, basically, I want my code to look something like this but I am not sure of the correct syntax:
INSERT INTO tablename
VALUES
(SELECT........), ('value 1)
(SELECT........) ('value 2)
Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.
So, basically, I want my code to look something like this but I am not sure of the correct syntax:
INSERT INTO tablename
VALUES
(SELECT........), -> value 1
(SELECT........) -> value 2
hi,
is it possible to have an insert query to fill some of the parameters by select query from other table and rest of the parameters by passing the value.
ex:
insert into table1(a,b,c) values (10,select b fom table2 where.... , 35);
i tried with the above syntax but was unable to succeed.
if this is not possible can anyone help me in solving the problem
thanks,
aravind
Hi,
I'm trying to write the VBA code to insert data into an audit table. My code is in a function which will have the values to be inserted to it passed to the function.
My function code looks like:
Function InsertAuditRecord(stAction, stTimestamp, stUser) As String
Dim SQL As String
SQL = "INSERT INTO tblAuditLog " & _
"(UpdateDescription, UpdateTimestamp, UpdatedBy) " & _
"VALUES (stAction, stTimestamp, stUser)"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
stAction = ""
stTimestamp = ""
stUser = ""
End Function
The values "stAction", "stTimestamp" and "stUser" are the values passed by the calling procedure, but when this query is executed, the Enter Parameter Value window pops up, asking for the values for each of these 3 variable.
Can anyone tell me how to code the query so that it will use the values passed to the function?
Thanks,
Michael.
Hi all,
I have an INSERT INTO query that inserts multiple records in an excisting table.
What i need is a functionlity that fills a column called, lets say 'myID' with a query-unique increment.
So, when the query inserts 50 records the ID should increment from 0 to 49.
The next time i might insert 23 rec's and then the ID column should go from 0 to 22.
I tried to call a simple VBA function in the SQL query that returns i+1 and increments i, but that code is only evaluated once every run, so that doesnt work.
any suggestions? maybe in SQL or VBA
Thanks alot!
remvs
I am executing a query and I want to force a value of ALL into a additional field called BUKT. How would I go about this. I need to do something similar to DATE: Now() but with a set value.
Help!!
Hi I am creating a stock database and I have come across this problem:
I have a stock table, and I have successfully creted a query to insert a record with values selected in a form:
EG. In the form you can input a part no, comments and the quantity you want to add. I have created the stock system so that each individual item of stock is one record in the stock table.
What I can't seem to do is use the quantity field in the form to dictate how many times the form details is entered into the stock table.??
HELP
I have an insert query that works fine when all fields are entered:
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '2Main');
BUT...if I have Address2 as blank b/c its an optional field I get a validation rule error...even though there is no validation set up on this field in the table.
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '');
Please let me know if you know why this would be.
Thanks,
Kbreiss
I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.
FORM
frmAppendSOPTraining
lstEmployees (unbound)
cboSOPNumber (unbound) (the numbers are pulled from an SOP table)
txtRevisionNumber (unbound)
txtTrainingDate (unbound)
cmdAddRecords
TABLE Employees
EmployeeID (AutoNumber)
EmployeeNumber (Number Long)
FirstName (Text)
LastName (Text)
TABLE SOPTraining
TrainingID (AutoNumber)
EmployeeNumber (Number Long)
SOPNumber (Text)
RevisionNumber (Text)
TrainingDate (Date/Time)
I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended. :(
Private Sub cmdAddRecords_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Set frm = Forms!frmAppendSOPTraining
Set ctl = frm!lstEmployees
strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES ("
strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "
For Each varItem In ctl.ItemsSelected
strSQL2 = strSQL & ctl.ItemData(varItem) & ")"
CurrentDb.Execute strSQL2
Next varItem
End Sub