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. 2 followers. Follow

8 Replies
2355 Views

Similar Articles

[PageSpeed] 57

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:

Multiple Exchange Servers
Is it possible to install ms-crm email router on more then one Exchange Server in the same domain that will work with a single crm server? Also, is it possible to install the email-router on 2 exchange servers that one of them is in a different domain and both of them will work with a single crm server that resides in one of these domains? Is there a change in this cases in verion 3.0 (vNext)? There is definately a change for v3. The support for multiple servers is much better in v3, but not sure how it will handle the multiple Domain question. For v1.x, it could work, but depends a lot ...

Summary sheet for multiple sheets (difficult problem)
Here's the scenario: people owe money to companies. We assign each of these people a number, and create a separate sheet for each of the many companies. We need to compile a summary sheet (within the same workbook). Here's a sample sheet (let's call it Sample Company, which is the name of the sheet): Name ID # Amount John Jones 555 500 Jim Smith 123 175 Mary Long 232 100 Thus far the workbook has about twenty sheets (not counting the summary sheet) each named after a company and additional sheets are added freque...

Multiple Payees
I am using Money 2002 v10.0 My banking institution sends its transactions inclusive of transaction fees. At the moment I am 'Split'ing categories to show the fees, which makes my budget look OK. However the bank fees are showing against the Payees and not the Banking institute, so my Payee balances are all out. Is there a way I can also 'Split' a transaction into multiple payees as well as multiple categories EG.. Paying the phone bill: Phone company $20.00 Bank Fee $0.50 Total Trans. $20.50 Although I only paid the phone company $20.00 it shows as $20.50. ALter...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

How can I create a chart to compare multiple data series?
Using Excel, is there a way to merge multiple charts with different data series into one to demonstrate a direct comparison of the data series within one single chart. I've got 4 charts of the stacked column type. magnoliak77 Tufte calls them small multiples, Cleveland calls them trellis displays. I call them panel charts. Take a look at the examples on this page. http://processtrends.com/toc_panel_charts.htm Another option is to stick with your 4 charts, however, you can size and align them. Here's a link to a simple macro that sizes and aligns all charts on a single sheet....

Linked Table Manager in ACCESS
Hi, I am trying to change a field in an ACCESS table and get an error message that says the table is a linked table and fields can't be changed. After googling for some answers, I think I should be able to find out the link using "Linked Table Manager" in ACCESS. However, the "Linked Table Manager" button is grayed out. Any ideas/suggestions are welcome. Thanks. Richard Open the table in Design View. Reduce the window so that you can see the window's top bar. Right click in the top bar of the window (usually blue in color) and select ...

changing colours of multiple items in Publisher 2000
Does anyone have any idea how I can change the colour of multiple items on a page? It is so time consuming to have to select and change the colour of each item individually - it would be so useful to be able to select all the items I need and change the colour en bloc. Do the newer Publisher packages have this facility (2003, 2007?) What items are you trying to chage? -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Fluffbrain" <Fluffbrain@discussions.microsoft.com> wrote in message news:0B67BD6A-B0E8-45A9...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Multiple calls for Campaign
Hello I am attempting to set up a telemarketing campaign by using quick campaign in Microsoft CRM 4.0. I have set up the marketing list with multiple contacts and created a quick campaign for this marketing list. While creating the quick campaign, I selected phone call to all of the contacts within the marketing list. However, a phone call task was not created for every contact within the list. Only one phone call task was created for the entire marketing list. How can I create multiple phone calls for each and every contact within the marketing list. Kindly advise. Thank you ...

how to use temporary tables in dex
Hi Somebody has one example of how to use temporary tables in dex Cesar Hi Cesar. Define temp tables just as you would define a SQL table in Dex - but with physical name as "temp" without the quotation. I recommend using database type ctree for performance as it would just create a temp file in your directory. From there on, just use the temp table as you would with normal dex table but remember that once you are done with your routine, your temp table will be deleted automatically. Use temp table as a temporary repository for your calculations, reports, etc. ---Darryl Baj...

One use licence
I have purchased a one use student/teacher copy of Microsoft office 2007 from my colleges IT department. My laptop has recently developed a serious fault and will need to be replaced. I still have the disks and I was wondering, if i remove the program from my old laptop will i be able to install it on my new machine. If so what would I need to do? On 2/28/2010 8:05 AM, Mr Random101 wrote: > I have purchased a one use student/teacher copy of Microsoft office 2007 from > my colleges IT department. My laptop has recently developed a serious fault > and will need to be...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

multiple account registers
Been using Money for years, now I'm taking on my mother's finances and would like to track them in Money but in a separate account register. How can I do this, or other options for keeping her accounts separate from my own? In microsoft.public.money, Needinghelp! wrote: >Been using Money for years, now I'm taking on my mother's finances and would >like to track them in Money but in a separate account register. How can I do >this, or other options for keeping her accounts separate from my own? Use File->New->Newfile (or similar with an older version) to cre...

Money Plus not activated for just one Vista user
I posted a message over two hours ago and it never appeared. Sorry if this turns out to be a duplicate. I recently installed and activated Money Plus Deluxe after using the demo. The problem is that when I run it under my normal, restricted Vista account the program reports that it needs to be activated. I reactivate the program under that account and am told that it was successful, but the next time I start the program it reports that it needs to be activated again. The problem does not occur for other accounts on the computer. It only happens on the one Vista account that I actu...

Average IFS (Multiple Criteria)
I have used the following formula and it works prefectly, however, one of my columns contains % and I want an average not a sum. Is there a way to use AVERAGEIFS to get the average only if other cells contain certain criteria? =SUMIFS(AF$4:AF$169,$G$4:$G$169,$G174,$F$4:$F$169,$F174,$J$4:$J$169,"<=12/31/10",$J$4:$J$169,">=01/01/10") ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Emails to multiple recipients not being delivered
Windows 2000 Server SP4 Exchange 5.5 SP4 We've had multiple complaints about people not being able to send to multiple recipients in the "To:" field. For example: John Doe, Jane Smith, and Mike Brown are all listed as the "To:" recipients of an email. Jane Doe gets the email but nobody else listed does. We have had a few people tell us about this problem. Somtimes all recipipients will get the email but sometimes not. Are there any settings in Exchange 5.5 that will prevent this? Thanks in advance. Mr. Mike On Mon, 8 Aug 2005 16:02:02 -0700, "Mr. Mike"...

one local profile
Hi, I want to create only one local profile on a PC and and I want prevent the domain accounts to create a local profile when they will login on this PC. Is it a way to configure it? Thanks, Haydar "Haydar" <Haydar@discussions.microsoft.com> wrote in message news:A2AA41AC-5BF9-4A49-B660-CEC711D60A58@microsoft.com... > Hi, > > I want to create only one local profile on a PC and and I want prevent the > domain accounts to create a local profile when they will login on this PC. > > Is it a way to configure it? > > Thanks, > ...

Make Table Query
How do you add (in run-time) primary key and indexing to a table (during creation or after creation) when it has been created through a make-table query? Either from a designed query or VBA SQL statement. ...

Opening Multiple files in separate Excel folders
Can this be done? Hi what do you mean with 'separate Excel folders'? -- Regards Frank Kabel Frankfurt, Germany Paul Dunn wrote: > Can this be done? Yes. Dim aryFiles Dim oFSO Sub LoopFolders() Dim i As Integer Set oFSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:\MyTest" Set oFSO = Nothing End Sub '--------------------------------------------------------------------------- Sub selectFiles(sPath) '--------------------------------------------------------------------------- Dim Folder As Object Dim Files As Object D...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

newb question
Hi all, new Outlook 2003 user here with a problem. Here's the issue: I have 2 email accounts in my life, a hotmail account, and my work email account. I've set them both up as seperate email accounts in Outlook, and both work just fine. But here's the rub: In order to connect to my work email account (which is set-up as an exchange server email account) I have to first connect using VPN software on my PC. I only really want to check my work email once or twice a month, so therefore I don't want to fire-up my VPN software every time I open Outlook. My hotmail, on the other...

Autoupdate Pivot Table for New Range
I have a 50-sheet workbook. The first sheet contains a long list (>40,000 rows) and the next 49 sheets each contain a pivot table that collects data from the first sheet. Each month the number of rows in the list changes as rows are added and deleted. Each month I have to update the pivot tables. How can I do this without having to use the wizard and manually drag through the new list? If you use a dynamic formula to name a range, then use that as the source for the pivot table, the new data will be included when the pivot table is refreshed. There are some instructions here: ...

Hide System Fileds in table
Hello All, How can I hide/show the system fields in a table i.e. s_Generation and s_GUID? Thanks Abe You posed your question in a newsgroup dedicated to Access forms. I'll assume you want not to see those fields in your form, rather than "hiding" them in the underlying table. After all, you don't want your users mucking about in the tables directly... If you need/want the fields attached to the form, but not visible, you can set the control's .Visible property to False. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author...