Auto Create Tables

[dnelson24]

First off I know very little about Vb code so please be kind.

I have a table called SALESPERSON with a field called EMP_CD in it. There are 142 employee codes. Can someone explain and supply some code that would allow me to auto create a table for each employee, I would like the new tables named the same as the field EMP_CD and the only field in the table should be EMP_CD with the original EMP_CD value populated.

Thanks in advance.
David

 

[barny2006]

can you explain what you intend to do with 142 tables? why not just access the data using a query?

 

[Golom]

Short answer ... Yes.

Dim rs As DAO.recordset
Set rs = CurrentDb.Openrecordset("Select * From SalesPerson")
Do Until rs.EOF
   CurrentDb.Execute _
   "Select EMP_CD INTO " & rs![EMP_CD] & " " & _
   "Where EMP_CD = '" & rs![EMP_CD] & "'"
   rs.MoveNext
Loop

Somewhat longer answer ...

Why would you want to do such a thing?

A table in a relational database describes a distinct entity with it's own properties and attributes. EMP_CD however is just data and all employees have (presumably) the same attributes ... differing only in the values those attributes have.

You are making your life unbelievably complicated by generating 142 tables where one would do.

 

[dnelson24]

The problem is that we "currently" have 142 salespeople. Our sales has a somehwat high turnover. I am updating the EMP_CD from a table that has a termdate set or not.

My table that I am working with has EMP_CD, VSN(Vendor stock number), and SALES. Each employee has multiple VSN's. I need to rank the the top sales for vsn for each employee. I have some old code that would allow me to rank the top 5 VSN's and assign a value of 10 for the top selling VSN, 8 for next and so on but for only one EMP not the entire table of differing EMP codes. I thought if I could get each employee into it's own table I could rank accordingly?

 

[PHV]

I strongly suggest you to read carefully the following:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

The SQL to generate the rankings that you want from only one table is moderately complex but certainly possible. If you can provide as much detail as possible on what you require, perhaps we can provide some SQL code to do it.

... and do read the document that PHV posted.

 

[dnelson24]

EMP_CD VSN SALES$
AGS M450 $500
AGS N3098 $300
AGS T785 $165
AGS U58 $50
AGS 323 $964
BKN 4938 $567
BKN O987 $56
BKN 8371 $367
BKN B475 $456
BKN B234 $321

The table has 140 more EMP_CD's just like above data...
Some EMP_CD's have more than 5 entries and some may have less. Depends on what VSN's they sold.

I need the top 3 VSN's of each employee and rank them from highest SALES$ to lowest SALES$. And then assign a value of 10 to the highest and 2 for the lowest value.


Thus(for the entire table)
EMP_CD VSN SALES$ POINTS
AGS 323 $964 10
AGS M450 $500 8
AGS N3098 $300 6
AGS T785 $165 4
AGS U58 $50 2
BKN 4938 $567 10
BKN B475 $456 8
BKN 8371 $367 6
BKN B234 $321 4
BKN O987 $56 2
...etc

David

 

[dnelson24]

This is the code I used in another db....

Function MGWgp()

Dim MyDB As Database
Dim MySet As Recordset
Dim I As Integer
Dim R As Integer

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("select * from [MGW2Store - GP Select] order by [GP % To Target] DESC", DB_OPEN_DYNASET)

MySet.MoveLast
I = MySet.RecordCount
R = 12

If MySet.RecordCount > 0 Then
MySet.MoveFirst
For I = 1 To MySet.RecordCount
MySet.Edit
MySet![GP Rank] = (R - 2)
MySet.Update
MySet.MoveNext
R = R - 2
Next
End If

End Function

 

[dnelson24]

Perhaps i need a do loop or something...around this current code to start ranking at 10 again after the EMP_CD changes value?