Create new table with VBA

[sotiris]

Hello,
I have a table named Table01 with three fields
"AN" "DESCRIPTION" "QTY"
1 DESC01 2
2 DESC02 1
3 DESC02 4

I want to make a routine for creating a new table with four fields as below

"NAN" "AN" "DESCRIPTION" "QTY"
1 1 DESC01 1
2 1 DESC01 1
3 2 DESC02 1
4 3 DESC03 1
5 3 DESC03 1
6 3 DESC03 1
7 3 DESC03 1

The field "QTY" must be 1 for each record.

 

[MStCyr]

Here's a sample to create tables and insert data.


DROP TABLE Books;
CREATE TABLE Books (
Title TEXT (30),
Author TEXT (10),
Length INTEGER
);
INSERT INTO Books VALUES ("Visual Basic Algorithms", "Stephens", 358);
INSERT INTO Books VALUES ("Visual Graphics Programming", "Stephens", 677);
INSERT INTO Books VALUES ("Advanced Visual Basic Techniques", "Stephens", 2358);
INSERT INTO Books VALUES ("The Longest Day", "Elliott", 1220);
INSERT INTO Books VALUES ("This Thing Called Calculus", "Newton", 731);
INSERT INTO Books VALUES ("Huh?", "Cambell", 412);
INSERT INTO Books VALUES ("The Killer Tortoises of Maui", "Arfin", 215);
INSERT INTO Books VALUES ("The Big Book of Smallness", "Ranson",302);
DROP TABLE Films;
CREATE TABLE Films (
Title TEXT (30),
Producer TEXT (10)
);
INSERT INTO Films VALUES ("This Thing Called Calculus", "Robertson");
INSERT INTO Films VALUES ("The Longest Day", "Jones");
INSERT INTO Films VALUES ("The Killer Tortoises of Maui", "Arfin");

SELECT Books.Title, Author, Producer
FROM Books, Films
WHERE Books.Title = Films.Title;

SELECT Books.Title, Author, Producer
FROM Books LEFT JOIN Films
ON Books.Title = Films.Title;