By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,991 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,991 IT Pros & Developers. It's quick & easy.

Actions on multiple records

P: 19
Hi,

For a set of records, which I get by doing a SELECT on a database, I want to some computations. After doing those computations, I want data, based on those computations to be stored in another table in the database. I just can't figure out how to do this.

For example:
1. do the select (like selecting all male employees from the database)
2. insert those records into another table, with, if their date of birth is between 1/1/1970 and 1/1/1980, a certain attribute, to true; if their dob differes, than set the attribute to false.

So what I basicaly want is to do this select, do a computation for each record in the selected set and insert the "modified record". Can access do that by using some kind of loop and storing the selected data in some array or so?

Thanks!
Oct 29 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,347
SELECT...INTO Statement
Creates a make-table query.

Syntax
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The SELECT...INTO statement has these parts:

Part Description
field1, field2 The name of the fields to be copied into the new table.
newtable The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the existing table from which records are selected. This can be single or multiple tables or a query.
INSERT INTO Statement
Adds a record or multiple records to a table. This is referred to as an append query.

Syntax
Multiple-record append query:

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part Description
target The name of the table or query to append records to.
field1, field2 Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the table or query to copy records from.
tableexpression The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2 The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').
You can create a completely new table using SELECT...INTO, or add new records into an existing table using INSERT INTO.
The computation can be done within the SQL statement, whichever of the two you need to use.
Oct 29 '06 #2

P: 19
You can create a completely new table using SELECT...INTO, or add new records into an existing table using INSERT INTO.
The computation can be done within the SQL statement, whichever of the two you need to use.
Oooh thanks a lot, I'll be trying that this afternoon!!!!!
Oct 29 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.