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
....but check the exact requirements according to the msdn help.
insert into mytable my, list, of, columns select another, set, data, values from aledgertable
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 way.company 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