Get Data From Another Query In INSERT Query
Mar 29, 2007
Hi there
my current query
INSERT INTO [Transaction] ( booking_ref, transaction_type, transaction_description, transaction_amount, [date/time] )
VALUES (10, 'Booking', 'Hotel Room Booking Charge', 4.5, Now());
now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.
I hope someone can point me in the right direction
thanks in advance
View Replies
ADVERTISEMENT
Jul 2, 2010
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.
View 2 Replies
View Related
Feb 25, 2007
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
sqlStatement = "SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"
Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)
saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
saverecord.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
and I have even looked into an insert sql statement
[B]Method 3
Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message
Run time error '3061'
Too few parameters. Expected 3.
:confused:
View 14 Replies
View Related
Feb 5, 2014
I want to create a append query in access 2003 to insert data into an existing table from Excel workbook.My Table name is TokenDetail in Access 2003.
And Excel File is TokenCreation.i want to create query with msg box and requered file path for data becuase my excel files have various path and name.
View 8 Replies
View Related
Jul 15, 2014
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 Related
Oct 8, 2013
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:
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
View 2 Replies
View Related
Jul 10, 2015
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....
View 1 Replies
View Related
May 11, 2006
Hallo everyone,
I have two tables in the database table1(col1,col3,col2) and table2(col1, col2). First of all i will have to select col2 from database and for each and every value of col2 i need o make an insert query in the table1(..,..,col2).
It will be helpful for me if anyone call solve this problem for me.
aravind.s
View 1 Replies
View Related
Apr 24, 2006
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)
View 1 Replies
View Related
Apr 24, 2006
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
View 1 Replies
View Related
May 8, 2006
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
View 1 Replies
View Related
Feb 21, 2007
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.
View 2 Replies
View Related
May 17, 2005
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
View 5 Replies
View Related
Jan 17, 2006
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!!
View 3 Replies
View Related
Feb 20, 2006
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
View 1 Replies
View Related
May 9, 2006
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
View 2 Replies
View Related
May 10, 2006
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
View 4 Replies
View Related
Jan 9, 2007
Hi,
I was wondering if i could get some help here. I got error message saying "user defined - typed not defined" and it's highlighting the first line that is WorkBase as Database.
Here is my code.
Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim rsNew As New ADODB.Recordset
Set WorkBase = CurrentDb
strSQL = "INSERT INTO [Projects](Project_ID, [OLD J_ID]) "
strSQL = strSQL + "VALUES ('" & myProjectID & "', '" & myOldPID & "')"
WorkBase.Execute strSQL, dbFailOnError
WorkBase.Close
This is for Office 2003
Thank you in advance
View 1 Replies
View Related
May 23, 2007
Im trying to do an insert an an update in the same function,but it only allow an upate only if a record exist.
Here is my code:
Sub insert(ByVal UserSelection, ByVal Grand_Prix_ID)
'The date function
'IF the 2 or more days left before the race then
' Do the insert
CompareDates(User)
Dim mysql As String
Dim strConn As String
Dim MUser_ID = Request.QueryString("UserID")
Dim Nickname = Request.QueryString("name")
Dim LastGP_ID As Integer = Grand_Prix_ID - 1
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & Server.MapPath("App_DataFantasyF1.mdb") & ";"
If TeamSelection_ID(Grand_Prix_ID) Then
mysql = "INSERT INTO TeamSelection(Grand_Prix_ID, User_ID, Driver_ID1, Driver_ID2, Driver_ID3, Driver_ID4, Driver_ID5, Driver_ID6)" & "VALUES(@Grand_Prix_ID ,@User_ID, @Driver_ID1, @Driver_ID2, @Driver_ID3, @Driver_ID4, @Driver_ID5, @Driver_ID6)"
'else if already selected before do the update
Else
MsgBox("You have made the selection before and You are about to update", MsgBoxStyle.YesNo)
mysql = "UPDATE TeamSelection SET Grand_Prix_ID = @Grand_Prix_ID, User_ID = @User_ID, Driver_ID1 = @DRIVER_ID1, Driver_ID2 = @DRIVER_ID2, Driver_ID3 = @DRIVER_ID3, Driver_ID4 = @DRIVER_ID4, Driver_ID5 = @DRIVER_ID5, Driver_ID6 = @DRIVER_ID6" & ""
mysql = mysql & " WHERE User_ID =" & MUser_ID
mysql = mysql & " AND Grand_Prix_ID =" & UserSelection(7) & ""
Dim Myconn As New OleDbConnection(strConn)
Dim objComm As New OleDbCommand(mysql, Myconn)
Myconn.Open()
With objComm.Parameters
.Add(New OleDbParameter("@Grand_Prix_ID", UserSelection(7)))
.Add(New OleDbParameter("@User_ID", UserSelection(6)))
.Add(New OleDbParameter("@Driver_ID1", UserSelection(0)))
.Add(New OleDbParameter("@Driver_ID2", UserSelection(1)))
.Add(New OleDbParameter("@Driver_ID3", UserSelection(2)))
.Add(New OleDbParameter("@Driver_ID4", UserSelection(3)))
.Add(New OleDbParameter("@Driver_ID5", UserSelection(4)))
.Add(New OleDbParameter("@Driver_ID6", UserSelection(5)))
End With
objComm.ExecuteNonQuery()
Message.Text = "Your Selection has been successfully recieved"
Myconn.Close()
End If
End Sub
View 3 Replies
View Related
Jun 7, 2005
i m a newbie to MS access ...
i have two tables
1) LabTest with Fields (Test Name: primary key and Rates )
2) Patient Visit (Visit ID: primary key, Test Name, Rates)
problem is ... when ever i change the rates
the past rates also get updated
one solution was to include a rate field in visit also
but how am i going to insert it from Lab Test table to patient visit table ...
can anyone help
thanks in advance!!!
View 9 Replies
View Related
Aug 15, 2014
how I can use this INSERT query to insert the value of max_hoeveelheid_contract into the "AS Expr7" part (end of the strQuery)
Code:
Dim max_hoeveelheid_contract As String
max_hoeveelheid_contract = som_hoeveelheid_NettoGewicht -Me.Hoeveelheid.Value
Dim strQuery As String
[code]....
View 3 Replies
View Related
Nov 7, 2007
Hi,
I wonder if i could get some help here with the SQL insert into table. I got Syntax Error when executing the following code
strSQL = "INSERT INTO [Time Sheet] (SFirstName, SLastName, Department_No, Status, subject_taken)"
strSQL = strSQL & " VALUES ('Mike','Fraser','01','FullTime',3)"
WorkBase.Execute strSQL, dbFailOnError
WorkBase.Close
Time Sheet table:
SFirstName - type(text)
SLastName - type(text)
Department_No - type(text)
Status - type(text)
subject_taken - type(number)
Everytime i remove the subject_taken field and 3 from the insert query, it works nicely.
The field size of the subject_taken is double
Decimal places = auto
default value = 0
required = no
indexed = no
Is there a proper syntax for inserting data field of type double?
Thank you in advance
View 1 Replies
View Related
Dec 8, 2006
hey all, In my database (For a doctors practice) I have individual patient records with information about them on there. There are 5 boxes showing the current medication, if any, that they are on. The form is all linked up and so when I go through each record for each patient all the data changes as it should. I have a seperate table with all of the drug information on it and I want to be able to click on the box on the patient form (One of the boxes that has one of their medications in) and I want it to be able to automatically put the name of that drug in a search query and bring up the results in a report or form view. Is it possible to do this?
So far I have managed to create a control button on the pateint data form and when clicked, it asks for a a parameter value i.e. the name of the drug. I'd like to be able to miss out this step and for it to automatically enter the name of the drug from the text box into the serach criteria.
PLEASE HELP! I need this urgently!
Thank you for your time.
normski
View 2 Replies
View Related
Oct 8, 2006
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
View 2 Replies
View Related
Apr 3, 2013
Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?
View 2 Replies
View Related
Nov 12, 2013
I would like to insert a sequence number into a query that currently returns 7367 rows.
Here's what it looks like now:
ID_NUM
DTE_FIN_TRANS_RCV
1322
9/27/2012
1322
7/10/2012
1011
7/2/1999
1011
9/22/1999
1011
10/21/1999
Here's the desired outcome with sequences:
ID_NUM
DTE_FIN_TRANS_RCV
SEQUENCE
1322
9/27/2012
1
1322
7/10/2012
2
1011
7/2/1999
1
1011
9/22/1999
2
1011
10/21/1999
3
All I need is a way to number rows with a value higher than the previous for that particular ID_NUM.
View 4 Replies
View Related
