Double loop multiple table creation

Question

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

Answer 1

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 DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!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.

Answer 2

Thank you @Ken Sheridan ,

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

Obrad