adding records from another table via SQL Mk. II

[TB]

About a week ago, some of you people kindly assisted me in correcting an SQL
statement whereby a certain number of records where added to one table based
on a data from another table ( name of the thread: "adding records from
another table via SQL").

Now a variation of that theme:

The solution provided last time (thanks a lot):

INSERT INTO newcustomers (customer, customergroup)
SELECT IDcustomer, 7 FROM customers

added a number of records to newcustomers equvalent to the total number of
records in customers. But what if the table customers contains a column
called customercount with values that indicate the number of records to add
to newcustomers in each case?

For example, let's say that

'Select customercount from customers where IDcustomer = 1'

returns the value 2.

How can I create an SQL statement that adds the number of records indicated
in customers.customercount (in this case 2) to newcustomers, adding the
value 7 (as in the previous example) to the column customergroup?

If there are different approaches for Access and MySQL, please let me know
as I have to use this on both databases.

Once again, thanks a bundle for your time.

TB

 

[Mohamed Shafiee]

Hi,

What are you trying to do?
Who are the new customers?
Instead of having a field to count customers, why don't you use a boolean
field to indicate that the customer is a new customer? The SQL statement
would then be something like this:

INSERT INTO newcustomers ( CustomerID, CustomerName )
SELECT customers.CustomerID, customers.CustomerName
FROM customers
WHERE (((customers.[New Customer])=True));

Why are you using the new customer table anyway? When you can use a select
statement to get the list of new customers? A view can be used just like a
table too.

New customers can also be determined with SQL, based on the count of sub
records or the registered date.

Shafiee.

 

[TB]

I could also illustrate the situation with another example:

Imagine that 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 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 want is: Every time an order is generated for a specifc
product in a specific quantity in the table Orders, a number of records
is inserted in Productionitems equivalent to the value entered in the
Quantity column of the Orders table.

Any suggestions will be highly appreciated.

Thanks

 

[John Vinson]

But what if the table customers contains a column
called customercount with values that indicate the number of records to add
to newcustomers in each case?

If it's REALLY necessary to do this - and given that it will create a number
of *identical* records, you should be very careful before doing this! - one
handy way is to have an auxiliary table Num, with one integer field N. Fill
this field N (perhaps using Fill Down in Excel) with values from 1 through
the largest number of newcustomers you're ever likely to encounter.

Your Append query could then be based on a "Cartesian Join" query. Add your
table Customers and the table NUM with NO join line and put a criterion on N
of

<= [newcustomers]

This will give you newcustomres new records, and you can even include N in
the query to individually number the records.

John W. Vinson/MVP

 

[TB]

The records will not be identical. Below is the content of another of mine
in this thread. I would like to hear your comments.

--------

could also illustrate the situation with another example:

Imagine that 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 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 want is: Every time an order is generated for a specifc
product in a specific quantity in the table Orders, a number of records
is inserted in Productionitems equivalent to the value entered in the
Quantity column of the Orders table.

Any suggestions will be highly appreciated.

Thanks

---------

But what if the table customers contains a column
called customercount with values that indicate the number of records to
add
to newcustomers in each case?

If it's REALLY necessary to do this - and given that it will create a
number
of *identical* records, you should be very careful before doing this! -
one
handy way is to have an auxiliary table Num, with one integer field N.
Fill
this field N (perhaps using Fill Down in Excel) with values from 1 through
the largest number of newcustomers you're ever likely to encounter.

Your Append query could then be based on a "Cartesian Join" query. Add
your
table Customers and the table NUM with NO join line and put a criterion on
N
of

<= [newcustomers]

This will give you newcustomres new records, and you can even include N in
the query to individually number the records.

John W. Vinson/MVP

 

[Mohamed Shafiee]

Hi TB,

If you can't do it with SQL, do it programmatically through the client
program. I don't know about MySQL... but SQL server has stored procedures
which can be written in TSQL, and Oracle has stored procedures which can be
written in PLSQL just right for that purpose. They have things called
triggers too. Triggers execute each time a record is added, or for other
events such as that.

You should see what is available for MySQL. And can you please post it here?
Please?

Shafiee.

The records will not be identical. Below is the content of another of mine
in this thread. I would like to hear your comments.

--------

could also illustrate the situation with another example:

Imagine that 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 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 want is: Every time an order is generated for a specifc
product in a specific quantity in the table Orders, a number of records
is inserted in Productionitems equivalent to the value entered in the
Quantity column of the Orders table.

Any suggestions will be highly appreciated.

Thanks

---------

But what if the table customers contains a column
called customercount with values that indicate the number of records to
add
to newcustomers in each case?

If it's REALLY necessary to do this - and given that it will create a
number
of *identical* records, you should be very careful before doing this! -
one
handy way is to have an auxiliary table Num, with one integer field N.
Fill
this field N (perhaps using Fill Down in Excel) with values from 1
through
the largest number of newcustomers you're ever likely to encounter.

Your Append query could then be based on a "Cartesian Join" query. Add
your
table Customers and the table NUM with NO join line and put a criterion
on N
of

<= [newcustomers]

This will give you newcustomres new records, and you can even include N
in
the query to individually number the records.

John W. Vinson/MVP

 

[John Vinson]

So, what I want is: Every time an order is generated for a specifc

product in a specific quantity in the table Orders, a number of records
is inserted in Productionitems equivalent to the value entered in the
Quantity column of the Orders table.

Ok... so base a Query on Products joined to Orders, with Num as an
additional table; use <= the quantity field as a criterion on N. This will
give you the desired number of records. You can include the needed fields
from Products and Orders and make it an Append query; you can even use a
calculated field


IDItems: DMax("IDItems","ProductionItems]") + N - 1

to append to the new IDItems field, if it's a long integer rather than an
Autonumber.

John W. Vinson/MVP

 

[Mohamed Shafiee]

You can't do what you just said, because it would require a loop. You can do
this only with TSQL or PLSQL. For a good database, it should work the other
way around. I mean, sub records and its totals should not exist as raw data.
Total or Count can always be calculated. You should have a new approach for
your problem. Modify the flow diagram for data entry. I mean, you can have
your staff enter the data for production items, and have another staff to
associate production items with an order, and another group of staff to pack
and ship the right items (supervisors and labourers). That could mean that
each production item will have a serial number so that the correct item can
be shipped. In an automated system, data for production items would be
automatic. So what you will be seeing is data for production items, without
staff for that purpose. And all you have to do is to associate it with
orders.

Shafiee.

The records will not be identical. Below is the content of another of mine
in this thread. I would like to hear your comments.

--------

could also illustrate the situation with another example:

Imagine that 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 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 want is: Every time an order is generated for a specifc
product in a specific quantity in the table Orders, a number of records
is inserted in Productionitems equivalent to the value entered in the
Quantity column of the Orders table.

Any suggestions will be highly appreciated.

Thanks

---------

But what if the table customers contains a column
called customercount with values that indicate the number of records to
add
to newcustomers in each case?

If it's REALLY necessary to do this - and given that it will create a
number
of *identical* records, you should be very careful before doing this! -
one
handy way is to have an auxiliary table Num, with one integer field N.
Fill
this field N (perhaps using Fill Down in Excel) with values from 1
through
the largest number of newcustomers you're ever likely to encounter.

Your Append query could then be based on a "Cartesian Join" query. Add
your
table Customers and the table NUM with NO join line and put a criterion
on N
of

<= [newcustomers]

This will give you newcustomres new records, and you can even include N
in
the query to individually number the records.

John W. Vinson/MVP