Double loop multiple table creation

Obrad 41 Reputation points

A bit rusty with coding - Thank you in advance!

I have a database with two tables

  1. sales rep data two fields - Sales_Rep_Number and Sales_Rep_Name
    Sales_Rep_Number | Sales_Rep_Name
    1000 | John Doe
    1001 | Jane Smith....etc
  2. Sales data for reps
    1000 | $25.36 | Apples
    1000 | $45.15 | Oranges
    1001 | $55.02 | Pears

FACT - common field between two tables - Sales_Rep_Number

TASK - Creating multiple tables where table name will be in format "Sales_Rep_Number_Sales_Rep_Name"
and all records in particular table would correspond to that rep number.

APPROACH- Created a public sub where I loop through Sales Rep table creating a table name. "1000_John Doe"

VBA CODING ISSUE - Creating a new loop where the Sales Data table data would be selected based on rep number from the Sales Rep table and created table with "Sales_Rep_Number_Sales_Rep_Name" name....and continued until all sales reps are selected and all tables are created from Sales data table.

Final RESULT -

Table Name - 1000_John Doe
Data in the table
1000 | $25.36 | Apples
1000 | $45.15 | Oranges

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
821 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ken Sheridan 2,666 Reputation points

    You would not need a second loop for this. At each iteration of the first loop you could build and execute the SQL statement for a 'make table' query restricted to the current Sales_Rep_Number value.

    However, I would recommend against creating multiple tables; this encodes data as table names. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way. You can easily create a simple query which accepts the Sales_Rep_Number as a parameter, open the query, or more usually a report or form based on the query, to return each sales' reps sales.

    If you want to present the data in forms take a look at in my public databases folder at:!169

    In this little demo file the section on 'retrieving data from the database' includes, towards the end of the section, an example of how to open multiple instances of a form, each restricted by a separate criterion, which in your case would be a Sales_Rep_Number value.

    To present the data in report format, you could simply group a report by Sales_Rep_Number, and set up the report to start a new page for each sales rep, or you could loop through a recordset of sales reps and call the OpenReport method at each iteration of the loop, restricting it by means of the WhereCondition argument.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Obrad 41 Reputation points

    Thank you @Ken Sheridan ,

    I was able to complete the loop shortly after posting this question.
    Thank you very much.


    0 comments No comments