INSERT Query Which Generates An ID Per Rec.

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 Replies


ADVERTISEMENT

Report Generates ID Instead Of Field Name

Jun 10, 2014

I have 3 dynamic combo box. a combo box > filters b combo box and b filters c combo box. showing the field names whereas connected on field ID's. But my problem is when I am running reports (reports in form of charts) on the data saved in the backend through these combo boxes on the form. When I run report it shows ID's of combo box B instead of field name.

View 14 Replies View Related

Access Generates A Spelling Error

Nov 21, 2006

I have a label on a form with text including the word "generated".The funny thing is when I switch from Design View to Form View generated becomes genneerraat :eek: :confused: It's easy enough to use a different word but there's obviously a problem somewhere and I'm worried that Access might make a mess of other words too.Anyone have ideas what's going on here?Addition: No matter what words I use, the last 10 or so characters of the text in the label is changed when switching views. I have been playing around with the text a bit more and it appears that the character & is the source of the problem. When I delete the &s everything looks fine again. Anyone care to comment?

View 3 Replies View Related

INSERT Query - Insert New Data Only

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

Reports :: Report In Which A Textbox Generates Numerical Values And Letter Values

Aug 6, 2014

I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.

View 9 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

I 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.

View 1 Replies View Related

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

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

Queries :: Query To Insert Missing Rows From Another Query / Table

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

Queries :: Insert From A Query To A Table When Query Is Changed

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

INSERT INTO Query

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

INSERT INTO Query

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

Insert Query

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

INSERT INTO Query

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

Insert Field In Query

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

Insert Query Loop??

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

Insert Query (passing Nothing)

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

Insert Into Query Not Working

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

Error When Trying To Insert Query

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

Insert And Update Query

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

Insert Query Problem

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

Forms :: VBA INSERT Query

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

Issue With Insert Query Into Table

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

How To Automatically Insert Criteria In A Query.

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

URGENT : Insert Query Problem

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

Queries :: Put INSERT INTO Statement In A Query?

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

Insert Sequence Number In Query

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







Copyrights 2005-15 www.BigResource.com, All rights reserved