Create Table

C

CatalinaJenx79

Also is there a way to use input from fields in a form, to create a
table name in runtime.

ie:
CREATE TABLE [Inv#]+Line.........
//Where Inv#=
Input from Invoice# Field in Main Form
//and Where Line =
Input from Item# Field in subform

So that from one form, there may be 3 tables created named
ie:
CTYUSE1921I607E2500000001
ie:
CTYUSE1921I607E250DO01025
ie:
CTYUSE1921I607E250KO01000
 
J

Jeff Boyce

I may be trying to read too much into your question...

Why do you want to dynamically create tables? A well-designed and
well-normalized data structure would use the existing structure to
accommodate "new" elements, rather than requiring new tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
C

CatalinaJenx79

Let me explain. The Truth is that this is a Work Order System for a
production facility. Each Item the user lists in the subform is also
the name of a Bill of Materials for options the unit is to be billed
for. This system is designed to capture each unit produced as a
"folder" of options(files). Each file points to a bill that contains
parts. The Invoice# mentioned is actually a Work Order Number specific
for each unit. Our customers specify what options they'd like and so
the items are references to those options. The end result is a
picklist. a report listing all the parts in all the bills used for
that unit for a specific station in production. As the unit moves
through the production stages, different picklists are needed. We need
the Work order file to act as the instruction for that particular unit
and we need the bills to compile the picklists. when the user inputs a
line item in a work order i want a snapshot of the bill associated. and
i need it saved as a table so i can query it over and over through the
next couple months. I will actually need to find a way to run a union
query on all the bills that coorespond to an entry in that work order.
and set the "stage" column in each bill as my criterea.
 
C

CatalinaJenx79

On second thought, instead of creating that snapshot table i could just
run the union queries on the Bills themselves. But i would still have
to save the Work Order as a table of options to reference the bills.
Therefore i still need a way to tell the query what tables to query
based on a list of Items(bills) in another Table(WorkOrder)

So i need to find out how to include those Variable parameters in my
CREATE TABLE [-------WO#------]( )
procedure.
and in my union query.
SELECT From bill1(Field1,Field2, Field3, ...)

union bill2(Field1,Field2, Field3,
....)...........etc
 
J

Jeff Boyce

A couple thoughts...

There are approaches to handle BOM (bill of materials) processes that don't
require creating new tables. Check at Google.com with these keywords.

It sounds like you are describing a list of options chosen. I will assume
that you have a defined list of options (at any particular point in time).

What would prevent you from using a simple table design like the following:

trelOrderOption
OrderOptionID (Primary Key)
OrderID (FK) (which order - I assume a customer can order more than
one)
OptionID (FK) (from a lookup table of possible options)

Your "Order" table would have the customer ID as a Foreign Key.

This is grossly oversimplified, as I am sure that your real world situation
is more complex.

However, pulling an "options list" for an order using this design is as
simple as querying the table for all options "belonging" to an OrderID (join
trelOrderOption in the query to the options lookup table).

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top