INSERT: Multiple Subqueris

Jan 25, 2007

I have a form with a print button. When pressed it runs of a set of labels between 2 dates. I wish to keep a log of the print jobs that are done. Storing the date and time that the print was done, who did it and (The bit I can't figure) the date range of the print job.

I am trying to make an INSERT statement with 2 queries. 1 for the oldest date of the print and one with the most recent date.


INSERT INTO PrintHistory (RangeStart, RangeEnd)
VALUES (
(SELECT TOP 1 [Bookings].DateEntered
FROM [Bookings]
WHERE [Bookings].ConfirmationSent Is Null
ORDER BY [Bookings].DateEntered),

(SELECT TOP 1 [Bookings].DateEntered
FROM [Bookings]
WHERE [Bookings].ConfirmationSent Is Null
ORDER BY [Bookings].DateEntered DESC)
);


The above doesent work and gives 'Reserved Error (-3025)'

Am I barking up the wrong tree with this or have I just made a small error?

View Replies


ADVERTISEMENT

Multiple INSERT String

Aug 4, 2005

Can anyone help?

Have searched the net with no luck. This is my problem.

I am trying to use a multiple INSERT string to create records in a table, from a VB application and using ADO to do it.

If I send the single INSERT it works fine, but if I try to send more than one in the same string I get a 'MISSING SEMI COLON AT END OF STATEMENT' error.

I have tried putting one at the end of each statement, then just at the end of the INSERT statements string, but still get this error.

Can Access actually handle more than one SQL instruction at a time?

Thanks for any help.

View 3 Replies View Related

Trying To Insert Multiple Rows

Nov 13, 2006

I'm trying to insert multiple rows into a table at once, but I'm having problems.

I've tried a syntax like this:

INSERT INTO [TABLE] ( [FIELD1], [FIELD2], [FIELD3] ) VALUES
(( '1', 'A', '1' ),( '2', 'B', '2' ));

..but with no success.


On a site I even found this other way, but with no success either...

INSERT INTO [TABLE]
SELECT '1', 'A', '1'
UNION ALL
SELECT '2', 'B', '2';

Is it possible to insert multiple rows and I'm just missing the syntax, or is it not possible with Access?

View 1 Replies View Related

Multiple Insert Into - Some Don't Work

Nov 7, 2007

I have an empty database, copies of which will be used in a number of locations. Before a copy is sent out to the user some data, specific to the user's location, needs to be entered into a total of 40+ tables . I have tried to do this using a series of Update and Insert queries but find that not all of the changes have been successful.
It seems as if a block of several tables is missed every so often through the list, as if Access has had trouble doing the writes quickly enough and skipped some.
I have looked for some method of forcing each query to complete its writes before moving on to the next one, but have found nothing so far.
The database is self-contained and will be run on a PC, not on a server.
What I am looking for I guess is the Access equivalent to the Commit command used in other systems.

Will someone please tell me, is there anything like this In Access 2000?

View 1 Replies View Related

Insert Multiple Records

Oct 11, 2005

I am working on an electronic catalog for my company. I am populating the database and I'm trying to speed the process. This is what I want to do. This database deals with cars and trucks, I would like to design a form where I can enter:

1989-1995 Chevy Malibu, etc.

Once the form is full and I save the records it will create 7 individual records, one for each year.

1989 Chevy Malibu
1990 Chevy Malibu and so on.

Any ideas?

View 4 Replies View Related

Insert Multiple Rows

Nov 6, 2006

I am trying to insert multiple rows to a table using the query

insert into rvp (rvp, rvp_name, controller_id)
values (1200, 'rvp1', 10), (1201, 'rvp2', 10)

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

Can someone please help me out.

View 3 Replies View Related

Insert A Multiple Selection Into A Table

Mar 23, 2008

This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.

I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.

Eg.

Listbox:
ID
1
2
3

User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.

Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.

Thank you all in advance.

Steve

View 8 Replies View Related

Insert Multiple Blank Records

Apr 26, 2006

Hi,

I'm new to Access and I've run into a little problem. I've created a table with a primary key (autonumber) which will contain 1520 records. At present I only have data for the last 8 records. Ideally I would like to create 1512 blank records, with only the autonumber entered e.g. 0001, 0002, 0003 etc.

Unfortunately I cant add the data that I have got to 0001 - 0008 as it relates specifically to the primary ID.

Thanks.

View 10 Replies View Related

Insert Multiple Values Into A Text Box

Apr 9, 2007

Hi All

I have a list box. Below it lies a text box.

I'd like to click on multiple values in the list box which then populates a text box with each value to form a sentence.

currently my code looks like this

Dim Comment As String
Comment = Me.lstComments.Column(0)
Me.txtComment = Comment

Using the above method only inserts one value. How can I modify this to insert multiple values into the text box?

Much appreciated

View 4 Replies View Related

Queries :: Insert Multiple Record

Apr 11, 2014

I have 2 table and the relation between the 2 table are [Booking_Num]

Code:
table A: [ID],[Booking_Num] [Prod_Name],[Qty_Order]
table B: [ID],[Booking_Num],[Prod_Name],[Date_Out]

when I get insert to table A, should be the table B get many record base on [Qty_Order]..?
sample:

Code:
table A
ID | Booking_Num | Prod_Name | Qty_Order |
01 | BOK001 | Hammer | 4 |

table B
ID | Booking_Num | Prod_Name | Date_Out |
01 | BOK001 | Hammer | Null |
02 | BOK001 | Hammer | Null |
03 | BOK001 | Hammer | Null |
04 | BOK001 | Hammer | Null |

View 1 Replies View Related

Help In Insert/update Multiple Selection From A Listbox

Aug 7, 2005

thank you in advance for all your BIG help. :)

I have 3 tables - SMaster, SI_map, ILookup

I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.

in SMaster [ Sid, other fields ]
in SI_map [ primary key, Sid, ILookupid ]
in ILookup [ ILookupid, IName ]

questions:
1. How to get the multi-selected values from the list box?
2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields)
3. how to populate the records into form for user to see and update the values?

Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.

Thank you.

View 3 Replies View Related

Modules & VBA :: Multiple Variables For Insert INTO SQL Statement

Sep 28, 2013

Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.

Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False

[Code] ....

Error
Microsoft Visual Basic popup
Run-time error '3061'

Too few parameters. Expected 1.

View 5 Replies View Related

Queries :: How To Insert Multiple Conditions / Criteria For A Field

Jul 24, 2013

I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".

This is what I have so far for checking the minimum value:

field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)

I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.

View 3 Replies View Related

Queries :: Insert Multiple Groups Of Text Fields In Table

Feb 17, 2015

I am trying to do is to make a for loop to insert multiple text fields in on table.

Depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that I'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.

Code:
For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
"KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _

[Code] ....

View 3 Replies View Related

Tables :: Unable To Insert Multiple Values Selected From Listbox To Database

Nov 19, 2012

i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.

i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.

(ps: in my property sheet for my lstCuisine listbox its multi select is simple)

Here is my codes:

Private Sub Add_Record_Click()
If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then
MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name"
Else
DoCmd.GoToRecord , , acNewRec
End If
Dim conceptValue As String

[Code]...

View 10 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

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

Sep 8, 2014

I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.

The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.

This is what I have so far:

Code:

Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String

[Code] ....

When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.

View 14 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

Queries :: Insert Into One To Many PK And FK Insert

Mar 25, 2015

I have a normalized DB with one to many relationships, using Primary and Foreign Keys.

I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.

How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.

I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details

I have 2 tables, tblName and tblPhone. 1 name can have many phones.

tblName has PKName. tblPhone has PKPhone, FKName.

I have a form where user enters a new Name and PhoneNumber.

Name gets inserted to tblName, assigned with PKName = 100

Phone should get inserted into tblPhone with PKName.

IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").

The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.

View 2 Replies View Related

Db Sql Insert

Dec 19, 2006

I have a question.....i am uing Access as the back-end database of a VB6 Front end app.
Basically i have a recordset with about 300K records....i currently Insert 1 row at a time....which takes ALOT of time.....is there a faster way to do the insert? i saw some things on the internet about a BULK insert....but it sounds like it is only with SQL Server or ORACLE. Please let me know what you think.

View 2 Replies View Related

Insert Into??

Jun 10, 2005

I have a 2 identical databases. One named customerrent and the other customerhistory. Both databases contain related tables.
tblcustomer
tblrent
What i would like is a button on my form to archive the current record, to the customerhistory database then delete that same record from the customerrent database. Can someone give me an example how to accomplish this?

Thanks in advance!
Tim

View 1 Replies View Related

Insert All

Feb 27, 2008

What would be the best way to do the following scenario:

I have a form where users can select continents. Another combo box is autofilled to show the respective countries. In that same combo box there is also an "All" option. So for North America the combo box would be filled with U.S., Mexico, Canada, and All. There are many other text/combo boxes that the user fills in and then finally a table is update with the record.

I want to be able to insert into a table identical records for U.S., Mexico and Canada if the user selects "All" in the countries combo box.

View 14 Replies View Related

Help With SQL Insert

Mar 26, 2006

Hi,

I need urgent help. I have a question to ask and wonder if anyone might be able to help me.

My question is how do i execute SQL insert into a table called Job Register in access.

Here is my code before update

If foundflag = False Then
'new job detected
StrSQL = "INSERT INTO [Job Register](Industry_No, Client_No, Job_No, Job_Name, Job_Contact, Job_Phone, Job_MPhone, Job_Fee, Job_Reference, Job_Manager, Date_Registered) "
StrSQL = "VALUES ('" & Me.Combo20 & "', '" & Me.Text2 & "', '" & Me.Text4 & "', '" & Me.Text8 & "', '" & Me.Text10 & "', '" & Me.Text12 & "', '" & Me.Job_Mphone & "', '" & Me.Text14 & "', '" & Me.Text16 & "', '" & Me.Job_Manager & "', '" & Me.Date_Registered & "')"

End If

Your help is greatly appreciated.

Thank you very much in advance

View 3 Replies View Related

Insert A Row

Sep 18, 2006

I have a database that is tracking proposals and job orders. First the proposal goes out and then sometimes the customer what to change the order in some way which means i need to put a new row somewhere other then the end of the record. I know in Access you can not add a row in the middle of a table but everyone was use to doing this in Excel and was able to make such adjustments. I thought about adding a Sort column so the data entry person could add their own sort order but some proposals can be hundreds of lines and no one wants to do this, however they want to be able to make changes and adjustments to the existing proposal. Is there some magic I could create in the development of this database to make everyone happy?????

Any suggestions greatly appreciated.

View 2 Replies View Related

Insert Into Help

Oct 22, 2004

HI,

I have the statement as follows. But it is giving me the error message 3134(insert problem).

Assigning ERate = 7.1

DoCmd.RunSQL "INSERT INTO Currency (Exchange_Rate)VALUES(" & ERate & ")"

Currency is the table with 3 fields.
Curr_ID(Auto Generated,Primary Key)
Curr_Name(Text)
Exchange_Rate(Double)

Any ideas?

Thanks in advance.

View 5 Replies View Related

SQL Insert Into Help

Jan 10, 2005

Let me explain my situation first. I have a form that has a complete wells information(I'm doing Gas Balancing)

Every month we get new gas balancing statements. When a new year starts, I need to copy most of the info from one record to another- but make it a new year.

I have a form that displays all of the information. I am trying to make it so they push a command button for a new year, input what year, and then it copies the required information over- then refreshes the current filter(so that after they add a new year, they can go to the new year they just created)

I have googled for about 3 hours, and found a ton of differnt formats for Insert Into statements. Here is my latest(that does not work)

Dim strYear As Integer


strYear = InputBox("What year would you like to add to this well?")


DoCmd.RunSQL "INSERT INTO Gas_Balances (Sec, Twn, Rng, [Interest Owner], [Well Name], Year, County, State, WI%, NRI%, [Follow up Date], Comments, Operator) VALUES ( Sec, Twn, Rng, [Interest Owner], [Well Name], strYear, County, State, WI%, NRI%, [Follow up Date], Comments, [Operator_Operator])"

The values I want input to the table are from the current form(which is brought from a query of the Gas_Balances table).

I'm sure this is an easy fix, and I hate to bother the forum with what is probably a stupid question, but I have just about given up(it is 2:30 in the morning, I have to be up in 3 hours, and have spent the last 5 hours just trying to get something to work).

If anyone has any ideas/an example, please let me know. Thanks a lot everyone.

-Justin

View 4 Replies View Related







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