Combine multiple tables into one

I took over a database in which the last person created a new table for 
everyday worth of data (ie. Feb-21-08 is named 022108).  There are over 100 
tables in the single database with the same columns and table attributes, I 
would like to combine all of these tables into one where I have a Date_Key 
field in place of the table name.  I figured out how to query the table names 
using the following:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY 
MSysObjects.Name;

Now I just need to figure out a way to have a macro populate the master 
table using the table query to select the tables I want to combine.  Any 
ideas?

Thank you for your help.

0
Utf
2/28/2008 7:18:00 PM
access 16762 articles. 3 followers. Follow

8 Replies
2881 Views

Similar Articles

[PageSpeed] 54

On Thu, 28 Feb 2008 11:18:00 -0800, Travis <Travis@discussions.microsoft.com>
wrote:

>I took over a database in which the last person created a new table for 
>everyday worth of data (ie. Feb-21-08 is named 022108).  There are over 100 
>tables in the single database with the same columns and table attributes, I 
>would like to combine all of these tables into one where I have a Date_Key 
>field in place of the table name.  I figured out how to query the table names 
>using the following:
>
>SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
>(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY 
>MSysObjects.Name;
>
>Now I just need to figure out a way to have a macro populate the master 
>table using the table query to select the tables I want to combine.  Any 
>ideas?

I wouldn't do it with a macro, but you could do so using a VBA code loop.
Assuming that you want to select only tables whose names contain six digits,
and that Date_Key is a Text field that you want to contain the table name, try
this: create a query TablesToMerge using

SELECT [Name] FROM MsysObjects WHERE (MSysObjects.Type)=1 AND [Name] LIKE
"######" ORDER BY [Name];

Then you could use VBA code like the following:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo Proc_Error
Set db = CurrentDb
Set rs = db.Openrecordset("TablesToMerge", dbOpenDynaset)
Do Until rs.EOF
  strSQL = "INSERT INTO NewTablename(DATE_KEY, field1, field2, ..., fieldn)" _
  & " SELECT '" & rs![Name] & "', field1, field2, ..., fieldn" _
  & " FROM [" & rs![Name] & "];"
  db.Execute strSQL, dbFailOnError
  rs.MoveNext
Loop
Proc_Exit:
  Exit Sub
Proc_Error:
  <error handling code here>
Resume Proc_Exit
End Sub

You'll need to use your tables' actual fieldnames of course... do this ON A
BACKED UP database obviously!!!
-- 
             John W. Vinson [MVP]

0
John
2/28/2008 9:09:01 PM
Good idea, Travis!

However, as this involves a looping operation, it would be quite awkward 
for a macro.  Using a VBA procedure would be smoother.  Here is a 
skeleton suggestion (test on backup!!)...

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dt As Date
   Dim strSQL As String
   Dim strAPP As String
   Set dbs = CurrentDb
   strSQL = "SELECT MSysObjects.[Name]" & _
           " FROM MSysObjects"
           " WHERE (Left$([Name],1)<>"~")" & _
               " AND (Left$([Name],4) <> "Msys")" & _
               " AND (MSysObjects.Type)=1" & _
           " ORDER BY MSysObjects.Name"
   Set rst = dbs.OpenRecordset(strSQL)
   With rst
      Do Until .OEF
         dt = CDate(Left(![Name],2) & "/" & Mid(![Name],3,2) & "/" & 
Right([Name],2))
         strAPP = "INSERT INTO MasterTable ( Date_Key, SomeField, 
AnotherField, etc )" & )
                 " SELECT #" & dt & "#, SomeField, Another Field etc" & _
                 " FROM " & ![Name]
         dbs.Execute strAPP, dbFailOnError
      Loop
      .Close
   End With
   Set rst = Nothing
   Set dbs = Nothing

-- 
Steve Schapel, Microsoft Access MVP

Travis wrote:
> I took over a database in which the last person created a new table for 
> everyday worth of data (ie. Feb-21-08 is named 022108).  There are over 100 
> tables in the single database with the same columns and table attributes, I 
> would like to combine all of these tables into one where I have a Date_Key 
> field in place of the table name.  I figured out how to query the table names 
> using the following:
> 
> SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
> (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY 
> MSysObjects.Name;
> 
> Now I just need to figure out a way to have a macro populate the master 
> table using the table query to select the tables I want to combine.  Any 
> ideas?
> 
> Thank you for your help.
> 
0
Steve
2/28/2008 9:20:28 PM
.... forget the MoveNext in the code!  Correction:

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dt As Date
   Dim strSQL As String
   Dim strAPP As String
   Set dbs = CurrentDb
   strSQL = "SELECT MSysObjects.[Name]" & _
           " FROM MSysObjects"
           " WHERE (Left$([Name],1)<>"~")" & _
               " AND (Left$([Name],4) <> "Msys")" & _
               " AND (MSysObjects.Type)=1" & _
           " ORDER BY MSysObjects.Name"
   Set rst = dbs.OpenRecordset(strSQL)
   With rst
      Do Until .OEF
         dt = CDate(Left(![Name],2) & "/" & Mid(![Name],3,2) & "/" & 
Right([Name],2))
         strAPP = "INSERT INTO MasterTable ( Date_Key, SomeField, 
AnotherField, etc )" & )
                 " SELECT #" & dt & "#, SomeField, Another Field etc" & _
                 " FROM " & ![Name]
         dbs.Execute strAPP, dbFailOnError
         .MoveNext
      Loop
      .Close
   End With
   Set rst = Nothing
   Set dbs = Nothing


-- 
Steve Schapel, Microsoft Access MVP
0
Steve
2/28/2008 9:24:36 PM
Steve,

Does it matter that I'm running Access 2000?  I'm getting compile errors on 
the "Dim dbs as DAO.Database" line.  Do I need to load a particular library 
to run this code?  I'm not a novice, however this code is a little more 
complicated than my skill set.  Am I over my head?

Travis

"Steve Schapel" wrote:

> .... forget the MoveNext in the code!  Correction:
> 
>    Dim dbs As DAO.Database
>    Dim rst As DAO.Recordset
>    Dim dt As Date
>    Dim strSQL As String
>    Dim strAPP As String
>    Set dbs = CurrentDb
>    strSQL = "SELECT MSysObjects.[Name]" & _
>            " FROM MSysObjects"
>            " WHERE (Left$([Name],1)<>"~")" & _
>                " AND (Left$([Name],4) <> "Msys")" & _
>                " AND (MSysObjects.Type)=1" & _
>            " ORDER BY MSysObjects.Name"
>    Set rst = dbs.OpenRecordset(strSQL)
>    With rst
>       Do Until .OEF
>          dt = CDate(Left(![Name],2) & "/" & Mid(![Name],3,2) & "/" & 
> Right([Name],2))
>          strAPP = "INSERT INTO MasterTable ( Date_Key, SomeField, 
> AnotherField, etc )" & )
>                  " SELECT #" & dt & "#, SomeField, Another Field etc" & _
>                  " FROM " & ![Name]
>          dbs.Execute strAPP, dbFailOnError
>          .MoveNext
>       Loop
>       .Close
>    End With
>    Set rst = Nothing
>    Set dbs = Nothing
> 
> 
> -- 
> Steve Schapel, Microsoft Access MVP
> 
0
Utf
2/29/2008 3:12:01 PM
On Fri, 29 Feb 2008 07:12:01 -0800, Travis <Travis@discussions.microsoft.com>
wrote:

>Does it matter that I'm running Access 2000?  I'm getting compile errors on 
>the "Dim dbs as DAO.Database" line.  Do I need to load a particular library 
>to run this code?  

Yes, and yes: 2000 defaulted to use the ADO object library rather than DAO.
Select Tools... References; scroll down the list to find Microsoft DAO x.xx
(the highest version if there are more than one), and check it.
-- 
             John W. Vinson [MVP]
0
John
2/29/2008 5:30:17 PM
Thank you, John.

This works like I had hoped.  You and Steve have given me great help and 
insight, not to mention saved me countless hours of time.

I have one more question.  Can you explain this logic to me?

Do Until .OEF

Thank you again.

Travis



"John W. Vinson" wrote:

> On Fri, 29 Feb 2008 07:12:01 -0800, Travis <Travis@discussions.microsoft.com>
> wrote:
> 
> >Does it matter that I'm running Access 2000?  I'm getting compile errors on 
> >the "Dim dbs as DAO.Database" line.  Do I need to load a particular library 
> >to run this code?  
> 
> Yes, and yes: 2000 defaulted to use the ADO object library rather than DAO.
> Select Tools... References; scroll down the list to find Microsoft DAO x.xx
> (the highest version if there are more than one), and check it.
> -- 
>              John W. Vinson [MVP]
> 
0
Utf
2/29/2008 6:41:01 PM
Travis,

EOF means "end of file" and is a property of a recordset.  So we are 
telling it to keep doing the routine for each row in the recordset, 
until it gets to the end.  :-)

-- 
Steve Schapel, Microsoft Access MVP

Travis wrote:
> Thank you, John.
> 
> This works like I had hoped.  You and Steve have given me great help and 
> insight, not to mention saved me countless hours of time.
> 
> I have one more question.  Can you explain this logic to me?
> 
> Do Until .OEF
> 
> Thank you again.
> 
> Travis
0
Steve
2/29/2008 6:51:59 PM
On Sat, 01 Mar 2008 07:51:59 +1300, Steve Schapel <schapel@mvps.org.ns> wrote:

>EOF means "end of file" and is a property of a recordset.  So we are 
>telling it to keep doing the routine for each row in the recordset, 
>until it gets to the end.  :-)
>

"Start at the beginning, go on until you reach the end, and then stop." 
              - Lewis Carroll in Through The Looking Glass
-- 
             John W. Vinson [MVP]
0
John
2/29/2008 8:46:30 PM
Reply:

Similar Artilces:

One calendar with wrong meeting time
I send out Outlook recurring meetings for group and one person has them at the wrong time on his calendar, everyone else if fine. What can I look for to solve this? check the time zone on his computer "jerris" wrote: > I send out Outlook recurring meetings for group and one person has them at > the wrong time on his calendar, everyone else if fine. What can I look for > to solve this? Check the time zone and daylight time setting on his computer - it’s the usual cause of problems like this. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http:...

Combinning Multiple Lists
I am trying to combine three different lists into one without bein limited to the space between the number of the lists. For example: Fruit Veggie Meat Orange Carrot Beef Pear Eggplant Chicken Apple Broccoli Veal Lemon Cabbage Lime I want the three lists to become one like this: Orange Pear Apple Lemon Lime Carrot Eggplant Broccoli Cabbage Beef Chicken Veal Having quite a hard time on doing this. The real hard part is that th lists have variable lengths. And would perfer to not have a limit o the length of the lists. Than...

Combining Two Fields in One Calculated Field
Somebody wrote on this forum recently how to do this. In a table or a query, maybe both, and it was really simple and clever but I failed to print it for my notes. Take <FName>+" "+ <LName>, call them by another name (field name?) so that when I create a report, I don't have to worry about where to place the elements of a name so they look smooth and all melody and fine. Is this possible in a mere table? If not, please how do you do it in a query, and I promise to print it. TYIA. Don. It's rarely necessary or a good idea to store a ca...

Installation of CRM 1.2 in one machine
Dear Friends, Could somebody tell me if exist some problem, a real reason, that don't allow to install crm environment in only one server. I'm speaking about all the components: exchange, sql server, domain server and crm server. Thanks. Manel as long as the machine is fine,it should be fine. >-----Original Message----- >Dear Friends, > >Could somebody tell me if exist some problem, a real reason, that don't >allow to install crm environment in only one server. >I'm speaking about all the components: exchange, sql server, domain server >and crm serv...

Expression to identify the greatest dates for a one-to-many relati
I'm trying to produce a database will be used to track all the contracting agreements we have with our clients. At the heart of the database are two tables (tblFarms and tblContracts) which are related one to many respectively. Each farm may have many contracts but only one of the contracts for each farm will be current. The current contract can be identified as having the greatest TermDate (Data Type: Date/Time, Short Date). I need a query that produces a dataset with a calculated column of True/False value that identifies the current contract for each farm. The ultimate aim is to...

Multiple from addresses
Hi All, I have setup a mail server that forwards mail from users@domainA.com to the users real address. These users would now like to send mail from MyUser@domainA.com, but because he does not have a mailbox on my server he is unable to send mail from this new address. How can each of these users add another "from" address that uses their existing internal mail server to end mails from this address? Is their a clean way to do this, or must you duplicate their existing account and just change the from address? Thanks Warrick ...

macro to read and sort data from multiple text files
Hello there !! I have x number of ascii text files that have space delimited data columns (files may have 3 to 40 data columns). There are about 25 header rows at the top of each file but I am not going to use info from these rows. I also have a master.xls file. Could someone please help me create a macro to do the following - A) read first *.txt file from the given folder into sheet1 of the master.xls file. NOTE: I have already set up the sheet2 of the master.xls to sort required rows of data from sheet1 (based on row headers) using vlookup to populate a 40 column wide array. B) Copy the ...

Easy one..?
Hi Anyone know the slickest way of returning the letter of the column I'm in off the top of their heads? Cheers G -- Gromit ------------------------------------------------------------------------ Gromit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=928 View this thread: http://www.excelforum.com/showthread.php?threadid=277912 One way is to put this in a regular module and use Function GetColLet(ColNumber As Integer) As String GetColLet = Left(Cells(1, ColNumber).Address(False, False), _ 1 - (ColNumber > 26)) End Function another Sub ...

Pub 2003 One Color Gradient Changes in PDF
I have selected a one color gradient (horizontal) from the fill effects box, with dark at top and bottom, fading into the center of the page. This is a background that fills the page behind text and a photo. It will bleed, so I need the darkest part at top and bottom. The gradient looks terrific on the screen, but when I convert the file to PDF, the gradient reverses itself - the darkest part is in the middle of the page, and it fades toward the top and bottom. I have tried saving the text box (the only thing in it is the gradient fill) as a PNG and reinserting. When I converted that ...

Exact rows, multiple sheets
I need to compare two sheets one workbook. I need to know which rows are the same in Sheets1 and Sheets2 with 14 columns of information to compare? There are about 8000 rows in each sheet. -- jcohen029 ------------------------------------------------------------------------ jcohen029's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24062 View this thread: http://www.excelforum.com/showthread.php?threadid=376683 In sheet3, simply enter =IF(Sheet1!A1=Sheet2!A1,Sheet1!A1=Sheet2!A1,"") or =IF(Sheet1!A1=Sheet2!A1,Sheet1!A1,"") and copy to ...

Scientific charts: what is the best add-in or package for getting sane ones?
I need a normal scientific chart. You know, the ones with axes on the right, left, top, and bottom sides. I think I did this once before and it took an hour, since I had to trick excel into thinking i had about four different sets of data, most of which were fake. It was insane. Meanwhile something like Mongo or Axum or ?? or IDL would do this naturally (make good scientific charts instead of bubble gum candy/girlie/kiddie charts). What is the best option for me? Do I go with a simple template a consultant has worked out (speak up please), an excel add-in (which one?), or do I really...

Text entered in two separate cells appear as one word in another
Text entered in two separate cells eg. A1 TOM B1 HARRIS how do I get these two words to appear as TOMHARRIS in Cell C1 Hi, =A1&B1 if you need a comma in between =A1&","&B1 if this helps please click yes, thanks "lizzie" wrote: > Text entered in two separate cells eg. A1 TOM B1 HARRIS how do I get these > two words to appear as TOMHARRIS in Cell C1 ...

Pivot table #13
Hi, currently I've a pivot spreadsheet that pull data from another exce spreadsheet. In the range whereby I specify the path, can I creat something that more flexible, ie, if the path change I do not need t edit the pivot again to change it? Thanks -- momok ----------------------------------------------------------------------- momoko's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 ...

Excel Pivot Table
Hi there! Does anybody know how I can refresh the criteria data in the drop down boxes of an Excel Pivto Table? If I deleted data in the database the data still shows up in the dropdown boxes. Thanks for your quick reply, Stephen Hi Stephen have a look at http://www.contextures.com/xlPivot04.html Frank Stephen Godee wrote: > Hi there! > > Does anybody know how I can refresh the criteria data in the drop > down boxes of an Excel Pivto Table? If I deleted data in the database > the data still shows up in the dropdown boxes. > > Thanks for your quick reply, > > ...

Money 2007 & E*Trade Multiple Accounts
Just spent several hours configuring Money 2007 Premium for access to both Banking & Investing accounts. For anyone having similar issues, here is what I discovered: - You must use the 'Receive account updates from MSN Money My Accounts' option for all Banking Accounts. You enable this option fromm Tools->Settings - You CAN NOT have your Investment accounts set up for tracking in MSN Money My Accounts. If you already are tracking this way, login to the MSN website and go to My Money. Select 'Accounts', chose the 'Remove' hyperlink and delete each Investment Acc...

Pvt Table Question #2
Hi, I'm in excel 2007. I have a couple of really big sheets and would like to make a pvt table. My question is - is it possible to make a pvt table using two sheets? If so, how do you do this? Thanks! Alt/DP, then choose Multiple consolidation ranges -- the UI was removed from the ribbon. "lj" <lj@spu.edu> wrote in message news:a137474c-7ea2-4c35-a9ab-43af7056c140@x38g2000yqj.googlegroups.com... > Hi, > I'm in excel 2007. I have a couple of really big sheets and would > like to make a pvt table. My question is - is it possible to make a > pvt table...

Rearrange data columns in Pivot Table
In a pivot table, how do you rearrange the column order in the data section? Right now I show columns named A, B, C, and Grand Total. I want to be able to rearrange them as follows (for example): Grand Total, B, A, C. I am using Excel 2000. TIA Hi i'm not sure if you can move the "grand total" column, but the others can be easily re-arranged by just typing the names in the order you want (ensure that you don't make any typing errors though), what i mean is, click on the A heading and type B once you press enter B will now come first and A will be moved continue as req...

Pivot Table showing data with blank values
In a pivot table, if I have a row field and I select field settings and deselect the "Show items with no data", does that mean that if the Data items related to that field are blank, that the field item will also be blank. If so, it doesn't happen for me. Can someone assist me with this please. Rob What you have done stops empty fields appearing. Check the box if yo *do* want them to show ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Fi...

Multiple modal windows with the same parent
This is an old, and I believe, well known problem. You run into it every time you have two modal windows running at the same time. Closing one of them will always enable their parent, which is incorrect behaviour as the parent should stay disabled as long as there is any modal window shown. [Refer to following post: news:OTyfwfF5CHA.972@TK2MSFTNGP11.phx.gbl] I found a great description of the problem and a workaround here: http://www.voidnish.com/Articles/ShowArticle.aspx?code=notmodaldialogs Though, workaround turned out not to be an equivalent solution. The problem lays in Win32 EndDialog...

Is it possible to close multiple activites?
If we create a quick campaign we like to close all related activites by selecting all and then close. Is this possible or do i need to write some custom code? // Mats You can make a manual workflow rule to change the status of the activities to complete. Then select the activities and apply the rule. -- Matt Wittemann, CRM MVP http://icu-mscrm.blogspot.com "MatsL" wrote: > > > If we create a quick campaign we like to close all related activites by > selecting all and then close. Is this possible or do i need to write > some custom code? > > // Mats &...

Using AutoSort to look the same date for multiple years
I have a columnar database that includes mulitple years of data. I wa wondering if anyone knows how to use a custom autosort in order to vie the same date for more that one year. I have chart that runs off o the sorted data. Thanks, Wil -- Message posted from http://www.ExcelForum.com Instead of using a custom autosort, maybe you could use a helper column: Say A2:Axxx contained your dates. then =text(a2,"mm-dd") would show the month/day. If it was ok to always use the same year: =date(2000,month(a2),day(a2)) and format it as mm-dd (but don't display the year) (2000 w...

How to create 2 separate indexex to one doc??
Hi I'd like to create 2 indexes to one document in Word 97, I've already made one but the second one mixes with the previous and I want them separate, what can I do? On Sat, 23 Jan 2010 02:05:01 -0800, lumtecua <lumtecua@discussions.microsoft.com> wrote: >Hi >I'd like to create 2 indexes to one document in Word 97, I've already made >one but the second one mixes with the previous and I want them separate, what >can I do? There are two separate mechanisms for inserting multiple indexes in a document. You didn't explain enough to tell whi...

Table record count
Is there an easy way to get the number of record in a temporary table without iterating through? yes i just did that Declare @RowCount Int set @RowCount = (Select Count(*) from "YOUR TEMP TABLE") why not use countrecords() function - assuming dexterity here of course. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Phil M" <PhilM@discussions.microsoft.com> wrote in message news:13090613-04BC-4960-8466-31A411FEC3E7@microsoft.com... > Is there an easy way to get the number of record in a temporar...

Create a table from 2 tables
Access 2000. I need to create/add to tblWorkOrders from info in two existing tables tblCustomers and tblBuildings. Relationships are 1 to many tblCustomers to tblWorkOrders and tblbuildings. The form is related to the tblWorkOrders which has a combo box which calls tblCustomers. The row source is: GetCustomerNameForInvoice a query used in invoicing. I want to be able to select a customer in the combo box and the corresponding buildings related to that customer come up in the subform. When I select a customer there is nothing in the subform. If form is closed and reopened the same ...

distribute an Analytical Accounting transaction multiple times
There is a need to be able to distribute an Analytical Accounting transaction mulitple times. In this example, there are mulitple fields containing different fruit trees. When the fields are fertilized, you need to report upon how much fertilizer goes to each type of fruit. Field 1 only contains cherry trees, field 2 contains half cherry trees and the other half orange trees. Field 3 contains 1/4 cherry, 1/4 orange and 1/2 plum trees. When entering a transaction for $100.00, you need to enter this information so that it can be tracked per fruit but also per field. One way to do this...