Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    10

    Unanswered: Make table question...

    Hello everyone!

    Is there a way to make a table for every instance of a record and name it as such? For example I have a table of employee historical purchases, let say this contains 100 employees each employee has list of items they have purchased during their employment. What I want to do is to create separate tables for each employee with their names as the table name.

    I currently do this manually by filtering the table by the employee name and changing the table name accordingly.

    If I can automate this process it will be of great help and would allow me to concentrate on other projects.

    Thanks and happy holidays!

  2. #2
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Post Re: Make table question...

    Quinaba,

    In reguards to your question, I would like to comment on a sample database at Martin Green's Office Tips website.

    The following link will take you to his downloads page:
    http://www.fontstuff.com/downloads/

    Download the following database:
    Fontstuff2K.mbd

    and look particularly at the table: tblOffices

    It shows a great example of Indexing.. which I think would be right up your alley (a good way of organizing your project.)

    Although I understand that your project is already in progress.. so it might be impossible to implement a new strategy.. but.. I just thought I would share this site.

    Brian
    Last edited by stratego16; 12-23-02 at 11:43.
    -----

    "How much deeper would the ocean be without sponges?"

  3. #3
    Join Date
    Nov 2002
    Posts
    10
    Thanks though...

  4. #4
    Join Date
    Nov 2002
    Posts
    10
    Help please....

  5. #5
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    Trust me, you don't need a seperate table for each employee. You can accomplish this using only two tables and setting up a relationship.

    I've created a small sample database for you demonstrating this.
    It contains an employee table and an employee purchases table along with a query and report.

    Check it out...HTH
    Attached Files Attached Files
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  6. #6
    Join Date
    Oct 2002
    Location
    Charlotte, NC
    Posts
    45

    Arrow See if this helps

    QUINABA,

    This might get you started. You might have to adapt what I give you to your project.

    I developed a FORM that gives you a list of employees in your DB - see attached picture.

    The form's combo box should contain the following "Row Source" property:
    SELECT [DataBaseName].[FieldName] FROM DatabaseName;
    Choose the location for your Employee names to go here.

    Place a button (ButtonName) on the form and add the following function:
    Private Sub ButtonName_Click()
    DoCmd.CopyObject , Me!CBoxName, acTable, "Generic_Database"
    End Sub

    This will take whatever info is in the Combo Box and create a table ..and name it in accordance to the combo box. The new table will have the layout of the Generic_Database YOU ALSO NEED TO CREATE. Basically.. an empty generic database that has the fields you need to keep track of employee purchases.

    Now.. after you put it all together.. you should have a form that allows you to select an employee.. and click the button to create you a new empty table... named after that employee.

    --- here's where I can't help. I am not sure how to loop this.. so it automatically goes down the list of employees and does them all. But maybe this will give you a start?

    Ask any questions.. I'll be glad to give any help. If I'm going about this all wrong.. I appologize in advance.
    Attached Thumbnails Attached Thumbnails form1.jpg  
    -----

    "How much deeper would the ocean be without sponges?"

  7. #7
    Join Date
    Feb 2002
    Location
    San Antonio, TX
    Posts
    69
    Creating a seperate table for each employee is a bad idea.....best not to encourage this behavior. =)

    It would be better to make a select query and/or a report that lets you select individual employees and then either email/print or export to excel/word for further use.....
    "Doing stuff is overrated. Take Hitler for example. He did lots but don't we all wish he'd have just stayed home and gotten stoned?"

  8. #8
    Join Date
    Nov 2002
    Posts
    10
    Thanks guys! I'm in the process of reviewing the responses. it looks like this would guide me the right way.

    Thanks again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •