How to combine 27 tables?

T

Ted

I have linked all users Contacts in Outlook to an Access database. Now I
have 27 live tables. How do I combine them all so all records will show up
in one live table?

I did do a manual append of each one to a blank table. That worked but it
is time consuming and the data in the table is only as current as the time I
created it. I also tried to design a query by adding all the tables to it
but then I didn't know where to go from there. I know there must be an
easier way.

I have only used Access to the extent of what the wizards and worlocks will
let you do. I don't know VB or any other programming language. Someone
mentioned a union or a view but that is beyond me.

Is there hope?

Thanks,

Ted
 
G

George Nicholson

In SQL View of the query designer, create a UNION query. This syntax assumes
all tables have an identical structure (same # of fields, same field names
and in the same order):

TABLE tblUserData1
UNION TABLE tblUserData2
.... (etc)
UNION TABLE tblUserData27

This would get all the data into one dataset. You could then base either an
append or make-table query on this.

If for some reason some tables have a different structure, you can use
SELECT syntax for each table but then you'd also have to specify field names
and placeholder Alias (Aliai?). My guess is that you don't need to go that
route for this

HTH,
 
D

David Lloyd

Ted:

Are you in an Exchange Server environment? What version of Outlook are you
using?

In an Exchange environment, one alternative is to create a public folder for
storing the contacts.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have linked all users Contacts in Outlook to an Access database. Now I
have 27 live tables. How do I combine them all so all records will show up
in one live table?

I did do a manual append of each one to a blank table. That worked but it
is time consuming and the data in the table is only as current as the time I
created it. I also tried to design a query by adding all the tables to it
but then I didn't know where to go from there. I know there must be an
easier way.

I have only used Access to the extent of what the wizards and worlocks will
let you do. I don't know VB or any other programming language. Someone
mentioned a union or a view but that is beyond me.

Is there hope?

Thanks,

Ted
 
S

Sprinks

Hi, Ted.

An append query is the way to go. I suspect yours didn't work because you
included the primary key. If there were any conflicts, Access wouldn't
permit you to add the records. Use an AutoNumber primary key, and append all
fields *except* the key field from the source tables, and let Access assign
the primary key automatically.

Better still, if you name all the source tables something consistent, such
as Personal1, Personal2, etc., you can run an efficient loop of code from a
command button to add them all at once.

Dim intIndex As Integer
Dim intMax As Integer
Dim strSourceTable As String
Dim strSQL As String


' The highest numbered table, say, Personal3
intMax = 3

For intIndex = 1 To intMax

strSourceTable = "Personal" & Trim(str(intIndex))
strSQL = "INSERT INTO YourTarget (field1, field2, field3 )
SELECT " & _
strSourceTable & ".field1, " & _
strSourceTable & ".field2, " & _
strSourceTable & ".field3 FROM " & strSourceTable
DoCmd.RunSQL strSQL

Next intIndex

Hope that helps.
Sprinks
 
D

David Lloyd

Ted:

I thought the following KB article may be useful as reference:

http://support.microsoft.com/default.aspx?scid=kb;en-us;286044

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have linked all users Contacts in Outlook to an Access database. Now I
have 27 live tables. How do I combine them all so all records will show up
in one live table?

I did do a manual append of each one to a blank table. That worked but it
is time consuming and the data in the table is only as current as the time I
created it. I also tried to design a query by adding all the tables to it
but then I didn't know where to go from there. I know there must be an
easier way.

I have only used Access to the extent of what the wizards and worlocks will
let you do. I don't know VB or any other programming language. Someone
mentioned a union or a view but that is beyond me.

Is there hope?

Thanks,

Ted
 
T

Ted

Thanks very much, George, for the help. That worked great. Now I know a
bit of SQL!!
 
Top