Problem automatically creating new tables from queried field value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that contains records within a group identified by a batch
number, which is the primary key. I currently have the ability to query all
data by date range, which now creates a table with multiple batch numbers. I
then filter out the batch numbers and create a list. I can't figure out,
however, how to automate creating individual batch tables from the list. Any
help would be greately appreciated.
 
the story of having batch numbers that are primary keys with a table that has
multiple batch numbers that you filter out to make a list and now want to
automate making individual batch number tables is pretty difficult to
comprehend.....

have read and re-read but can't understand your question...I'm sure it is
clear to you but......

you might resubmit with just the briefest possible sample of a couple
records of the table in what you currently have and then a brief display of
what you want....
 
I am running a MakeTable query that creates a table with two fields called
BatchNumber and Date. The table has lets say 4 records (rows). I need to
automate creating 4 new tables (because there are only 4 records) using the
values in the BatchNumber field as the table names. I also need to populate
each individual table with other information associated with and including
the batch number.
Example: FieldName1 FieldName2
BatchNumber Date
70034 11/10/2006
70035 11/11/2006
70036 11/12/2006
70037 11/13/2006

Create Table 1 Name: 70034TBL - Containing the following information
FieldName1 FieldName2
BatchNumber Date
70034 11/10/2006

Create Table 2 Name: 70035TBL - Containing the following information
FieldName1 FieldName2
BatchNumber Date
70035 11/11/2006

Create Table 3 Name: 70036TBL - Containing the following information
FieldName1 FieldName2
BatchNumber Date
70036 11/12/2006

Create Table 4 Name: 70037TBL - Containing the following information
FieldName1 FieldName2
BatchNumber Date
70037 11/13/2006
Unfortunately, due to confidentiality, and the size of the actual database,
I am unable to post actual examples.
 
Why do you need to create separate tables at all? Why not create a
query that prompts for a batch number? You could do all this from a
form that contains a combobox that has a Select query to retrieve all
the batch numbers for you.

Sounds to me like you're making this way more complicated than it needs
to be. And copying your tables will make the AutoNumber carry over,
which you probably don't want. What if someone adds a record to the
new table? Which record is right?

Just sounds like a dodgy design to me.
 
I know. I already have that feature built in. Unfortunately, my customer is
trying to get an additional feature that will provide the same results as the
combo box. The combo box queries a list of all the batch numbers obtained to
date (approximately 34,000+ records) When the user selects a batch number,
they click the command button and receive the production yield for that one
batch. However, if they want a yield report showing consisting of a mixture
of batches, the query currently provides an overall yield and treats mixture
of batches as one batch. I need to be able to show each individual's batch
production yield in one report. I don't use an autonumber at all within
access. My batch numbers are created on an assembly line using a Visual
Basic program I have designed. After much thought. I am going to see if I
can obtain the result I need by implementing a filter similar to the Invoices
filter example in the Northwind Database. If anyone in the community has
some more thought, it would be greately appreciated. Thanks anyway
(e-mail address removed) for your response.
 
Back
Top