Select Query + Insert Query

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 Replies


ADVERTISEMENT

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

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 1 Replies View Related

Union Query - Generating From A Select Query ?

Jun 23, 2006

(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-

Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)

Review Tables

Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)

The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.

The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)

I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;

SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;

I can then append the info into a new table. However this query is run at least twice a day and things change.

I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?

Any help greatly appreciated.

View 2 Replies View Related

Bug With Wrapper Select Query On A Union Query

Sep 14, 2007

I have the following query:

SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM (

SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1

UNION

SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper.
Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM [

SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1

UNION

SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable
Now when you want to execute the query you get an errormessage.
Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?

View 1 Replies View Related

Combining Select Query And Count Query

Jul 26, 2006

Ok, hope you can help me with this one.

I have a select query that pulls data from one table that includes the following data

MOS Grade Required Authorized



I have a separate count query that pulls data from another table and counts the following data

MOS Grade Assigned

How do I combine these 2 queries so the results I get will be

MOS Grade Required Authorized Assigned


For instance:

Select Query Says

MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3


Count Query Says

MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2


I want the results to read

MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2


Hope you can help.

View 5 Replies View Related

Crosstab Query Built On A Select Query

Jun 18, 2015

I have a crosstab query built on a select query.It works perfect. However, when I try to use the "Between [BegDate] And [EndDate]" it tells me access engine can not recognize it.If I use this in the actual select query it works fine. But when I try to use the crosstab query it gives me the above error.

Also, I'm using two undbound textboxes on a form for [BegDate] and [EndDate]. I have the syntax correct in my select query and it works fine. But it won't work with the crosstab query.

View 3 Replies View Related

Insert... ; Select @@identity

Feb 19, 2006

I work with project access (*.adp)

I want to know if it possible to take the last ID after the sql INSERT QUERY

my code:

strSQL = "INSERT INTO missionFather ....."
DoCmd.RunSQL strSQL


thanks!

View 2 Replies View Related

Insert Into ... Select Error

Nov 2, 2005

I`m trying to insert some rows of the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00].

So, I tryed to run the following queries:

A) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES (select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]);

But, when I want to save the query I get the following error:

"Syntax error. in query expression 'select [Produse finite dbo_SC03XX00].SC03001'"

B) INSERT INTO [Loturi dbo_SC33XX00] ( [Loturi dbo_SC33XX00].SC33001, [Loturi dbo_SC33XX00].SC33002, [Loturi dbo_SC33XX00].SC33003, [Loturi dbo_SC33XX00].SC33005)
VALUES select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00];

and I get the error:

"Syntax error in INSERT INTO statement."

I thought that the select query might be wrong, so I tested it:

"select [Produse finite dbo_SC03XX00].SC03001,'01','000000000999',5000 from [Produse finite dbo_SC03XX00]"

and it runs correctly.

How can I solve the problem? How can I insert the specified rows from the table [Produse finite dbo_SC03XX00] into the table [Loturi dbo_SC33XX00]?

Thank you in advance.

View 1 Replies View Related

INSERT SELECT Statements

Mar 1, 2007

I was wondering if you can an INSERT SELECT statement for 2 tables w/ different data source?

Like one's from Oracle and the other's from Access?

Please help :D

View 4 Replies View Related

How To INSERT INTO Values And Select...

Apr 28, 2007

My thinking must not be right as I can't get this to work but i have a table with

Date AMT
02/05 50
02/06 20
and soforth....

I am summing up each week and putting this in a table with values

WeekBeginningDate WeekEndingDate SumofAmt

I am trying to do
INSERT INTO tblTwo ("02/05/2007", "02/09/2007", SumOfAmt)
SELECT sum(Amt) FROM tblOne
where TDate >=#02/05/2007# and TDate <= #02/09/2007#

The two dates in the INSERT INTO are just values and the SumOfAmt is from the select. Can someone help me with this? Thanks in Advance!!

View 2 Replies View Related

Insert A Select Statement In A Text Box

Aug 1, 2006

hi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:

SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);

it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.

:o

View 4 Replies View Related

Modules & VBA :: INSERT INTO SELECT Statement

Mar 4, 2014

I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:

Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));

What I wrote for the VBA code is this:

Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb

[code]....

Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.

View 4 Replies View Related

Select Records To Insert Into A Field From Pop Up Form

Jul 4, 2005

Hi all,

I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?

Does anybody have an example of this.

Thanks for your help!

View 1 Replies View Related

Difficulty Combining Insert Into Or Select Into With A Union, Please Help!

Jan 23, 2007

I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:

INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")

It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!

View 1 Replies View Related

Can You INSERT INTO Table With SELECT.. UNION Queries

Mar 29, 2005

I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?



The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:

INSERT INTO TableCompleteForMailing

SELECT [Address List].[FamilySurname] AS Surname,

[Address List].[DEARFirstnames] AS FirstName,

[Address List].[Mailing] AS Mailing,

[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Address List]

UNION

SELECT [Names].[LastName] AS Surname,

[Names].[FirstName] AS FirstName,

[Names].[MailingList] AS Mailing,

[Names].[Selected] AS OtherMailing,

[Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Names],[Address List]

WHERE [Names].[AddressListID]=[Address List].[AddressListID]

ORDER BY Surname, FirstName;

View 3 Replies View Related

Count Query As Well As Select Query

Nov 30, 2007

Hello All,

I'm currently developing a football team website and I'm created a player profile bit of the website linking to my Access Database.

Currently I have the following :

selectCommand="SELECT [playername], [favouriteposition], [picture] FROM [player] WHERE [playername] ='Smith J'

This means I can display John Smiths Name, Fav Position and his Picture from my player table.

I also want to count his stats (goals, starts, subs) but am not sure how to do this.

I have a table called playermatchevents which records matchID, playerID, shirtnumber, goals, starts, subs, mom

goals is a straightforward Number datatype field, whereas starts,subs and mom are a Yes/No datatype field.

Basically I want to keep my select query, but I also want to add to it that it COUNTS the number of starts, subs, goals and mom that that player has.

Pardon me if I haven't used the correct wording, I'm still quite new to all of this.

I would be very grateful for any help.

Thanks,

covcitydv

View 6 Replies View Related

Queries :: Basic Delete Insert Select Statement

Nov 8, 2013

what is wrong with the following statements. I'm new to access SQl. This would work in ms sql.

Code:
DELETE from Table1
INSERT INTO Table1(email, productid, datecreated, datesend) values ('adf', 5, '10/10/2012','10/10/2012')
Select * from Table1

View 1 Replies View Related

Forms :: Insert / Select Values From Selection Combo Box

May 20, 2013

I want to insert obtain marks of the subjects. Find the attached my db. when i select combo value my requirement is to insert the all records which associate with the combo. but when i select combo value it will happen nothing.

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







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