Nested make table query 
Author Message
 Nested make table query

Hi,
I have specific problem:
Several tables:
a: Table with CustomerID
b. I table with data
c. II table with data

I need to make automaticly three new tables for every customer in
table a and call them different (it will be best to put name of the
customer with three different prefixes).
So how to make loop to take data from first table and use it in that
three make table query's.

Thanx

Dubravko



Sun, 10 Nov 2002 03:00:00 GMT  
 Nested make table query
This sounds like a database design problem that could be solved with primary
key (CustomerID) and foreign key references in the Table's 1 & 2. It would
make the whole process a lot simpler.

With Jet there is no way to "automatically" create the new tables, you'd
have to manage this through code. CREATE TABLE statement could be used to
dynamically create new tables.

Steve

Quote:

>Hi,
>I have specific problem:
>Several tables:
>a: Table with CustomerID
>b. I table with data
>c. II table with data

>I need to make automaticly three new tables for every customer in
>table a and call them different (it will be best to put name of the
>customer with three different prefixes).
>So how to make loop to take data from first table and use it in that
>three make table query's.

>Thanx

>Dubravko



Sun, 10 Nov 2002 03:00:00 GMT  
 Nested make table query

Quote:
>This sounds like a database design problem that could be solved with primary
>key (CustomerID) and foreign key references in the Table's 1 & 2. It would
>make the whole process a lot simpler.

I have tables linked from Navision, and I have Primary keys and
Foreign keys.
Question is how to browse through table in loop, and every time use
new record.

Quote:
>With Jet there is no way to "automatically" create the new tables, you'd
>have to manage this through code. CREATE TABLE statement could be used to
>dynamically create new tables.

I can create automaticaly new table with Make table Query, but I don't
know how to automaticaly change name of that table.

Dubravko



Sun, 10 Nov 2002 03:00:00 GMT  
 Nested make table query
Hi Dubravko,

I'm not sure I understand what you need to accomplish here, I'll offer a few
suggestions, in-line.

Quote:

>>This sounds like a database design problem that could be solved with
primary
>>key (CustomerID) and foreign key references in the Table's 1 & 2. It would
>>make the whole process a lot simpler.

>I have tables linked from Navision, and I have Primary keys and
>Foreign keys.
>Question is how to browse through table in loop, and every time use
>new record.

Add a new record on the ONE side of the relationship can be down by using
SQL and an Autonumber field to guarentee a unique identifier. Adding records
to the many side would use the unique identifier created on the one-side
used for the foreign key on the MANY side.

Quote:
>>With Jet there is no way to "automatically" create the new tables, you'd
>>have to manage this through code. CREATE TABLE statement could be used to
>>dynamically create new tables.

>I can create automaticaly new table with Make table Query, but I don't
>know how to automaticaly change name of that table.

If you have to create a new table for each new record, and I believe this is
a very BAD <g> thing to do... You can use the Create Table statement to
create a new table with whatever fields you need and add records to the new
table. Example (modified from online help):

Function MakeNewTable (pstrTable as String)
 Dim dbs As Database

 ' Modify this line to include the path to Northwind
 ' on your computer.
 Set dbs = OpenDatabase("Northwind.mdb")

' Create a table with two text fields.

dbs.Execute "CREATE TABLE " & pstrTable _
  & "(FirstName TEXT, LastName TEXT);"

 dbs.Close
End Function

To invoke:

?MakeNewTable("CustomerXYZ")

Steve



Mon, 11 Nov 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Making nested directories

2. Regular expressions - making nested matches work?

3. nesting functions in access query tool

4. Nested queries for multiple pivot Elements

5. Nested SQL Queries in VB3

6. Nested Queries within VB6

7. help with nested query

8. Microsoft Oracle Driver and Nested queries

9. MDAC 2.5 SP1, Nested Queries, Wildcards

10. MDAC 2.5, Nested Query, Wildcards

11. Create nested table in Word using VBA

12. Nested tables

 

 
Powered by phpBB® Forum Software