Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014

    Question Unanswered: How to consolidate 38 similar tables into one new table?

    I was recently asked a favor from a friend to help him create a database. This is my first time making one outside of the classroom, and I think I made a mistake.

    I was given around 40 ledgers containing names, emails, phone numbers, and emergency contacts. I messed up by creating different Tables for each ledger instead of shoving all the records into one table. I was wondering if there was a way to consolidate all those tables into one new one, so I don't have to re-enter all that data again. All the data types are short tex

    I couldn't find a way to do it through Access, so I just copy+pasted all the records into an excel spreadsheet. When I'm done I'm going to import it into Access.
    Last edited by MikeLitterus; 12-17-14 at 01:34. Reason: Solved

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Do ypu need to retain the origin of the information, ie person x came from ledger 5, person f from ledger a?
    assuming tbe data is of the same type, and ssme or similar number of columns then you can do what you want within Access using a series of insert into queries.
    Do a google on ms access insert into for details, but its going to be something like

    insert into mytable my, list, of, columns select another, set, data, values from aledgertable
    ....but check the exact requirements according to the msdn help.
    the only tricks are.
    The order of the columns extracted matterrs, it must be the same order as the insert into list.
    data types of the columns must be the same, or compatable. You can convert from one datatype to another using a cast function, eg for dates cdate (atextcolumn)
    You dont have to list all columns in the destination table (mytable in the above example), you shouldnt for an autonumber column anyway. But you must match the number of columns in the destination table with those from the source table (myledgertabke in the above).
    You can supply values not in the source table but required in the destination, ferinstance say you wanted to retain the ledger where tge dara came from.
    Insert into mytable source, my..... select 'ledger05', another...... from ledger05

    Make certain you are using the right datatype for each column (eg datetime for dates or time values, one of the numeric types for numeric data)
    Where you are storing text make certain the size specified is appropriate for the expected data. In exceptional circumstances, where there is a wide range consider using varchar. Ferinstance if the column defines a persons initials consider using, say, text (10) as opposed to the default text (255). Addresses, person or company names are alwats goid candidates to use varchar. But be aware using varchar in other database engines may cause problems if the column needs to be indexed in a specific names can be incredibly long so make cetain you choose the correct/appropriate size BEFORE inserting data.
    work out how to handle duplicates, because with 40+ sources of similar data uou are guaranteed to have sone duplicates, work out your strategy on how to identify duplicatws now and eliminate as many as possible as soon as possible.
    choice of primary key. There is the easy way out of using an autonumber column which is fine. There is another option to create a composite key comprised of say the first 5 letters of the surname / company name and a numerical suffix. Eg LITTE043 or WALLM93 for say wallmart. Youd start at SMITH001 for say mary smith, when the next smith appears use SMITH002 for them. This will require a function to fibd the next code.
    break apart composite data into normalised form, you can easily get back to the composite or another composite form from normalised data. Eg store a persons title, forenames, surname separately. When required you can bring out the specific elements in any required sequence.
    consider storing a coding for the surname such as double metanome to handle spelling mistakes or typos
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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