Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: Taking data from one table and inserting it into many different tables

    Hi, Im hoping one of you clever people can help me out. Im writing a time management database and iv'e hit upon a problem.

    Basically, i have a weekly time sheet of what work has been done. in this time sheet there is a job number field to say what job the work has been done on. what i need to do is on the click of a button, all the data will be inserted into tables based on the job number, so i need to read from the weekly time sheet the job number in each record and insert that record into a table named after that job number (e.g in the time sheet there may be say 20 records and in those 20 records there may be 5 different job numbers, so thats 5 different tables the records need to be inserted to).

    what would be an easy way of reading each job number from each record so i can use an insert command to update the job number tables.

    for the update i was going to use the sql string "INSERT INTO [Job number] SELECT * FROM [Timesheet] WHERE [Job Number] = (what ever job number) AND [Entered] = FALSE" but i can only do this when i have read which job numbers are in the sheet.

    I may not have explained that in the easiest way but if anyone can help it will be greatly appreciated

  2. #2
    Join Date
    Sep 2004
    Posts
    8
    I really would appreciate any help that might point me in the right direction here.

    I guess what i need to do is write a small loop that reads job number from the first record to a variable, then select the next record and do the same again, etc until all records have been read. any ideas anyone??

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    so i need to read from the weekly time sheet the job number in each record and insert that record into a table named after that job number
    Why do you want to create separate tables ? Why not store everything in the same table which would be the natural thing to do ? You can still limit what you see in your form/query e.g. show me this weeks data only.

    I guess what i need to do is write a small loop that reads job number from the first record to a variable, then select the next record and do the same again, etc until all records have been read. any ideas anyone??
    If you must create separate tables then yes, I think you have to go down the code route:
    1. store all the unique jobs in an array
    2. loop through each value in the array and run an sql for each table name using the job as the table name
    My VB coding is slow/basic so I leave it to the experts for the moment.

    Chris

  4. #4
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    I tend to agree with Chris, seems unjustified to store similar data such as this in separate tables. If there is nothing unique about this data other than the various jobs to which it is applied and the amount of time, then it would seem prudent to store it in one table. You can simply query the data to separate it according to the job. I would imagine that you could have a large number of jobs occurring over time, do you really want that many tables in your database? This situation falls under normalization. Why create more work for yourself than you have to?

Posting Permissions

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