updating one table with information from another table.

Hi All:

Once again I have to ask for assistance from all you friendly people out
there.

I have an ASP page that updates a set of Access tables in the following
manner:

I have a list of products that I produce. Every time I produce say 10 items
of product A, I have to generate unique information about each of these
items (for production purposes).

This means that I have three tables (simplified here):

- Products: This is where I keep information about the products. Columns:
IDproduct (primary key), Productname

- Orders: This is where the orders are kept. Columns: IDorder (primary key),
IDproduct, IDcustomer, Quantity

- Productionitems: These are items actually produced as a result of the
orders. Columns: IDitems (primary key), IDproduct, Dateproduced, Producedby

So, what I am looking for is an SQL statement that every time an order is
generated for a specifc product in a specific quantity in the table Orders,
inserts a number of records in Productionitems equivalent to the value
entered in the Quantity column of the Orders table and of course the value
of IDproduct of Order in the IDproduct column of Productionitems.

Finally, I would also like to know whether a different SQL statement would
have to made in case of using MySQL (the reason being that I might migrate
to that program at a later stage).

Any suggestions will be highly appreciated.

Thanks

TB

Re: updating one table with information from another table.

TB wrote:
> Hi All:
>
> Once again I have to ask for assistance from all you friendly people
> out there.
>
> I have an ASP page that updates a set of Access tables in the
> following manner:
>
> I have a list of products that I produce. Every time I produce say 10
> items of product A, I have to generate unique information about each
> of these items (for production purposes).
>
> This means that I have three tables (simplified here):
>
> - Products: This is where I keep information about the products.
> Columns: IDproduct (primary key), Productname
>
> - Orders: This is where the orders are kept. Columns: IDorder
> (primary key), IDproduct, IDcustomer, Quantity
>
> - Productionitems: These are items actually produced as a result of
> the orders. Columns: IDitems (primary key), IDproduct, Dateproduced,
> Producedby
> So, what I am looking for is an SQL statement that every time an
> order is generated for a specifc product in a specific quantity in
> the table Orders, inserts a number of records in Productionitems
> equivalent to the value entered in the Quantity column of the Orders
> table and of course the value of IDproduct of Order in the IDproduct
> column of Productionitems.
> Finally, I would also like to know whether a different SQL statement
> would have to made in case of using MySQL (the reason being that I
> might migrate to that program at a later stage).
>

Sorry, but the syntax for this operation depends on the database. You can
see the syntax for the operation in Access by using the Access query builder
to create the query that does what you want.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: updating one table with information from another table.

OK, let's stick with Access for the moment as this is database that will be
used for the time being. However, I hardly ever use the Access frontend for
anything, so I would not know how to do a point-and-click INSERT query.
Would it not possible to ask for your advice regarding the SQL approach to
take in this case? If so, and if you ever visit Spain (where I am at the
moment), I'll definitely buy you a beer.

Thanks

TB
"Bob Barrows [MVP]" wrote in message
news:%23zo$oFyMFHA.2468@tk2msftngp13.phx.gbl...
> TB wrote:
>> Hi All:
>>
>> Once again I have to ask for assistance from all you friendly people
>> out there.
>>
>> I have an ASP page that updates a set of Access tables in the
>> following manner:
>>
>> I have a list of products that I produce. Every time I produce say 10
>> items of product A, I have to generate unique information about each
>> of these items (for production purposes).
>>
>> This means that I have three tables (simplified here):
>>
>> - Products: This is where I keep information about the products.
>> Columns: IDproduct (primary key), Productname
>>
>> - Orders: This is where the orders are kept. Columns: IDorder
>> (primary key), IDproduct, IDcustomer, Quantity
>>
>> - Productionitems: These are items actually produced as a result of
>> the orders. Columns: IDitems (primary key), IDproduct, Dateproduced,
>> Producedby
>> So, what I am looking for is an SQL statement that every time an
>> order is generated for a specifc product in a specific quantity in
>> the table Orders, inserts a number of records in Productionitems
>> equivalent to the value entered in the Quantity column of the Orders
>> table and of course the value of IDproduct of Order in the IDproduct
>> column of Productionitems.
>> Finally, I would also like to know whether a different SQL statement
>> would have to made in case of using MySQL (the reason being that I
>> might migrate to that program at a later stage).
>>
>
> Sorry, but the syntax for this operation depends on the database. You can
> see the syntax for the operation in Access by using the Access query
> builder to create the query that does what you want.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: updating one table with information from another table.

I had keyed in on the use of "update" in your subject line. I thought you
wanted an UPDATE query, not an INSERT. Inserting is much more
asni-compliant:

INSERT INTO table1 (
SELECT FROM table2
WHERE

Most database products (at leat the ones that I have worked with), will
easily handle this syntax

Bob Barrows

TB wrote:
> OK, let's stick with Access for the moment as this is database that
> will be used for the time being. However, I hardly ever use the
> Access frontend for anything, so I would not know how to do a
> point-and-click INSERT query. Would it not possible to ask for your
> advice regarding the SQL approach to take in this case? If so, and if
> you ever visit Spain (where I am at the moment), I'll definitely buy
> you a beer.
>
> Thanks
>
> TB
> "Bob Barrows [MVP]" wrote in message
> news:%23zo$oFyMFHA.2468@tk2msftngp13.phx.gbl...
>> TB wrote:
>>> Hi All:
>>>
>>> Once again I have to ask for assistance from all you friendly people
>>> out there.
>>>
>>> I have an ASP page that updates a set of Access tables in the
>>> following manner:
>>>
>>> I have a list of products that I produce. Every time I produce say
>>> 10 items of product A, I have to generate unique information about
>>> each of these items (for production purposes).
>>>
>>> This means that I have three tables (simplified here):
>>>
>>> - Products: This is where I keep information about the products.
>>> Columns: IDproduct (primary key), Productname
>>>
>>> - Orders: This is where the orders are kept. Columns: IDorder
>>> (primary key), IDproduct, IDcustomer, Quantity
>>>
>>> - Productionitems: These are items actually produced as a result of
>>> the orders. Columns: IDitems (primary key), IDproduct, Dateproduced,
>>> Producedby
>>> So, what I am looking for is an SQL statement that every time an
>>> order is generated for a specifc product in a specific quantity in
>>> the table Orders, inserts a number of records in Productionitems
>>> equivalent to the value entered in the Quantity column of the Orders
>>> table and of course the value of IDproduct of Order in the IDproduct
>>> column of Productionitems.
>>> Finally, I would also like to know whether a different SQL statement
>>> would have to made in case of using MySQL (the reason being that I
>>> might migrate to that program at a later stage).
>>>
>>
>> Sorry, but the syntax for this operation depends on the database.
>> You can see the syntax for the operation in Access by using the
>> Access query builder to create the query that does what you want.
>>
>> Bob Barrows
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: updating one table with information from another table.

Thanks a lot.

Sorry for not replying before.

TB
"Bob Barrows [MVP]" wrote in message
news:OK5bWt5MFHA.2468@tk2msftngp13.phx.gbl...
>I had keyed in on the use of "update" in your subject line. I thought you
> wanted an UPDATE query, not an INSERT. Inserting is much more
> asni-compliant:
>
> INSERT INTO table1 (
> SELECT FROM table2
> WHERE
>
> Most database products (at leat the ones that I have worked with), will
> easily handle this syntax
>
> Bob Barrows
>
> TB wrote:
>> OK, let's stick with Access for the moment as this is database that
>> will be used for the time being. However, I hardly ever use the
>> Access frontend for anything, so I would not know how to do a
>> point-and-click INSERT query. Would it not possible to ask for your
>> advice regarding the SQL approach to take in this case? If so, and if
>> you ever visit Spain (where I am at the moment), I'll definitely buy
>> you a beer.
>>
>> Thanks
>>
>> TB
>> "Bob Barrows [MVP]" wrote in message
>> news:%23zo$oFyMFHA.2468@tk2msftngp13.phx.gbl...
>>> TB wrote:
>>>> Hi All:
>>>>
>>>> Once again I have to ask for assistance from all you friendly people
>>>> out there.
>>>>
>>>> I have an ASP page that updates a set of Access tables in the
>>>> following manner:
>>>>
>>>> I have a list of products that I produce. Every time I produce say
>>>> 10 items of product A, I have to generate unique information about
>>>> each of these items (for production purposes).
>>>>
>>>> This means that I have three tables (simplified here):
>>>>
>>>> - Products: This is where I keep information about the products.
>>>> Columns: IDproduct (primary key), Productname
>>>>
>>>> - Orders: This is where the orders are kept. Columns: IDorder
>>>> (primary key), IDproduct, IDcustomer, Quantity
>>>>
>>>> - Productionitems: These are items actually produced as a result of
>>>> the orders. Columns: IDitems (primary key), IDproduct, Dateproduced,
>>>> Producedby
>>>> So, what I am looking for is an SQL statement that every time an
>>>> order is generated for a specifc product in a specific quantity in
>>>> the table Orders, inserts a number of records in Productionitems
>>>> equivalent to the value entered in the Quantity column of the Orders
>>>> table and of course the value of IDproduct of Order in the IDproduct
>>>> column of Productionitems.
>>>> Finally, I would also like to know whether a different SQL statement
>>>> would have to made in case of using MySQL (the reason being that I
>>>> might migrate to that program at a later stage).
>>>>
>>>
>>> Sorry, but the syntax for this operation depends on the database.
>>> You can see the syntax for the operation in Access by using the
>>> Access query builder to create the query that does what you want.
>>>
>>> Bob Barrows
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap so
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>