Combining multiple tables into one table

I have inherited a database which has a table for each of our 1200
customers. Each table is exactly the same. It is very difficult to
generate reports

I would like to create a new table and take in all the records of all
the tables into this database. The new table will have an extra field
which will hold the table name for the records imported from the
tables.

Can anyone help?

re: Combining multiple tables into one table

On 22 Dec 2003 09:26:49 -0800, mcommins@kennys.ie (Martin C) wrote:
[color=blue]
>I have inherited a database which has a table for each of our 1200
>customers. Each table is exactly the same. It is very difficult to
>generate reports
>
>I would like to create a new table and take in all the records of all
>the tables into this database. The new table will have an extra field
>which will hold the table name for the records imported from the
>tables.
>
>Can anyone help?[/color]
Wow, that's a strange data structure - 1200 one row tables.

Well, since they are all the same you might step through the tables
collection (I'm assuming some identifying name) and insert them into
the new table you have created. I'm not sure why you would need to
keep the old table name unless that is where the customer name is
kept.

You would want to make sure you have some scheme to use a unique
customer ID and apply that during the converson.

- Jim

re: Combining multiple tables into one table

Here's some code that does what Jim suggested

It assumes you have a query named qryAllCustomers in your database.
Doesn't matter what its SQL is, since the code changes it. This code
also assumes that all your cust tables begin with "Cust". If not, you will
need to find another way to filter for only the tables you want.

Good Luck.


Function CreateUnionSQL()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim sName As String
Dim sSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryAllCustomers")

For Each tdf In dbs.TableDefs
sName = tdf.Name
If Left(sName, 4) = "Cust" Then
If Len(sSQL) > 0 Then sSQL = sSQL & vbCrLf & "UNION "
sSQL = sSQL & " SELECT '" & sName & "' AS CustTable, * FROM " & sName
End If
Next

sSQL = sSQL & " ORDER BY CustTable "
qdf.SQL = sSQL

Set tdf = Nothing
Set qdf = Nothing
Set dbs = Nothing

DoCmd.OpenQuery "qryAllCustomers"

End Function



--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast


"Martin C" <mcommins@kennys.ie> wrote in message news:a90579eb.0312220926.1955c5e@posting.google.co m...[color=blue]
> I have inherited a database which has a table for each of our 1200
> customers. Each table is exactly the same. It is very difficult to
> generate reports
>
> I would like to create a new table and take in all the records of all
> the tables into this database. The new table will have an extra field
> which will hold the table name for the records imported from the
> tables.
>
> Can anyone help?[/color]

re: Combining multiple tables into one table

Maybe this is a basic question and I think I've read it somewhere: why
CreateUnionSQL() is a function and not a sub??? I think I've seen many
examples using function while I will think rather instinctively what they
are doing requires a sub.

Herbert

"Danny J. Lesandrini" <dlesandrini@hotmail.com> ¦b¶l¥ó
news:bs7cu8$a93m4$1@ID-82595.news.uni-berlin.de ¤¤¼¶¼g...[color=blue]
> Here's some code that does what Jim suggested
>
> It assumes you have a query named qryAllCustomers in your database.
> Doesn't matter what its SQL is, since the code changes it. This code
> also assumes that all your cust tables begin with "Cust". If not, you[/color]
will[color=blue]
> need to find another way to filter for only the tables you want.
>
> Good Luck.
>
>
> Function CreateUnionSQL()
>
> Dim dbs As DAO.Database
> Dim tdf As DAO.TableDef
> Dim qdf As DAO.QueryDef
> Dim sName As String
> Dim sSQL As String
>
> Set dbs = CurrentDb
> Set qdf = dbs.QueryDefs("qryAllCustomers")
>
> For Each tdf In dbs.TableDefs
> sName = tdf.Name
> If Left(sName, 4) = "Cust" Then
> If Len(sSQL) > 0 Then sSQL = sSQL & vbCrLf & "UNION "
> sSQL = sSQL & " SELECT '" & sName & "' AS CustTable, * FROM " &[/color]
sName[color=blue]
> End If
> Next
>
> sSQL = sSQL & " ORDER BY CustTable "
> qdf.SQL = sSQL
>
> Set tdf = Nothing
> Set qdf = Nothing
> Set dbs = Nothing
>
> DoCmd.OpenQuery "qryAllCustomers"
>
> End Function
>
>
>
> --
>
> Danny J. Lesandrini
> dlesandrini@hotmail.com
> http://amazecreations.com/datafast
>
>
> "Martin C" <mcommins@kennys.ie> wrote in message[/color]
news:a90579eb.0312220926.1955c5e@posting.google.co m...[color=blue][color=green]
> > I have inherited a database which has a table for each of our 1200
> > customers. Each table is exactly the same. It is very difficult to
> > generate reports
> >
> > I would like to create a new table and take in all the records of all
> > the tables into this database. The new table will have an extra field
> > which will hold the table name for the records imported from the
> > tables.
> >
> > Can anyone help?[/color]
>
>[/color]

re: Combining multiple tables into one table

"Herbert Chan" <herbert@chan.com> wrote in news:3fe96926$1_1@storm.i-
cable.com:
[color=blue]
> Maybe this is a basic question and I think I've read it somewhere: why
> CreateUnionSQL() is a function and not a sub??? I think I've seen many
> examples using function while I will think rather instinctively what they
> are doing requires a sub.
>
> Herbert
>
> "Danny J. Lesandrini" <dlesandrini@hotmail.com> ¦b¶l¥ó
> news:bs7cu8$a93m4$1@ID-82595.news.uni-berlin.de ¤¤¼¶¼g...[color=green]
>> Here's some code that does what Jim suggested
>>
>> It assumes you have a query named qryAllCustomers in your database.
>> Doesn't matter what its SQL is, since the code changes it. This code
>> also assumes that all your cust tables begin with "Cust". If not, you[/color]
> will[color=green]
>> need to find another way to filter for only the tables you want.
>>
>> Good Luck.
>>
>>
>> Function CreateUnionSQL()
>>
>> Dim dbs As DAO.Database
>> Dim tdf As DAO.TableDef
>> Dim qdf As DAO.QueryDef
>> Dim sName As String
>> Dim sSQL As String
>>
>> Set dbs = CurrentDb
>> Set qdf = dbs.QueryDefs("qryAllCustomers")
>>
>> For Each tdf In dbs.TableDefs
>> sName = tdf.Name
>> If Left(sName, 4) = "Cust" Then
>> If Len(sSQL) > 0 Then sSQL = sSQL & vbCrLf & "UNION "
>> sSQL = sSQL & " SELECT '" & sName & "' AS CustTable, * FROM " &[/color]
> sName[color=green]
>> End If
>> Next
>>
>> sSQL = sSQL & " ORDER BY CustTable "
>> qdf.SQL = sSQL
>>
>> Set tdf = Nothing
>> Set qdf = Nothing
>> Set dbs = Nothing
>>
>> DoCmd.OpenQuery "qryAllCustomers"
>>
>> End Function
>>
>>
>>
>> --
>>
>> Danny J. Lesandrini
>> dlesandrini@hotmail.com
>> http://amazecreations.com/datafast
>>
>>
>> "Martin C" <mcommins@kennys.ie> wrote in message[/color]
> news:a90579eb.0312220926.1955c5e@posting.google.co m...[color=green][color=darkred]
>> > I have inherited a database which has a table for each of our 1200
>> > customers. Each table is exactly the same. It is very difficult to
>> > generate reports
>> >
>> > I would like to create a new table and take in all the records of all
>> > the tables into this database. The new table will have an extra field
>> > which will hold the table name for the records imported from the
>> > tables.
>> >
>> > Can anyone help?[/color][/color][/color]

One of the reasons that we write functions which return nothing and which
might appear to more appropriately manifested as subs is that macros can
find and run functions, but, (unless something changed recently) macros
cannot find and run subs.
I believe that menus in early verions of Access found functions as their
action parameters more consistently and quickly than they found subs. In
later versions, subs seem to be just fine.
In the past couple of years, I have used subs by preference, even when I
need values returned, by passing parameter variables byref.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: Combining multiple tables into one table

Lyle is exactly correct. I never create Sub procs, since they won't be
available to the property pages of controls. It is, however, a bad habbit
to create a Function without a return. When I'm being good, I set it to
boolean, even if that has no particular meaning.

--
Danny


"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote...[color=blue]
> "Herbert Chan" <herbert@chan.com> wrote ...>[color=green]
> > Maybe this is a basic question and I think I've read it somewhere: why
> > CreateUnionSQL() is a function and not a sub??? I think I've seen many
> > examples using function while I will think rather instinctively what they
> > are doing requires a sub.
> >[/color]
>
> One of the reasons that we write functions which return nothing and which
> might appear to more appropriately manifested as subs is that macros can
> find and run functions, but, (unless something changed recently) macros
> cannot find and run subs.
> I believe that menus in early verions of Access found functions as their
> action parameters more consistently and quickly than they found subs. In
> later versions, subs seem to be just fine.
> In the past couple of years, I have used subs by preference, even when I
> need values returned, by passing parameter variables byref.
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

re: Combining multiple tables into one table

On Wed, 24 Dec 2003 06:33:47 -0700 in comp.databases.ms-access, "Danny
J. Lesandrini" <dlesandrini@hotmail.com> wrote:
[color=blue]
> It is, however, a bad habbit
>to create a Function without a return.[/color]

Why?

--
A)bort, R)etry, I)nfluence with large hammer.

re: Combining multiple tables into one table

"Trevor Best" <bouncer@localhost> wrote in message
news:s9kjuvcatcujtn1dpa4p4hirh5pklubm50@4ax.com...[color=blue]
> On Wed, 24 Dec 2003 06:33:47 -0700 in comp.databases.ms-access, "Danny
> J. Lesandrini" <dlesandrini@hotmail.com> wrote:
>[color=green]
> > It is, however, a bad habbit
> >to create a Function without a return.[/color]
>
> Why?[/color]

That's what I was wondering.

Particularly one that returns false no matter how you use it.

re: Combining multiple tables into one table

Because when someone comes along later to maintain the application, they may
not know whether the omission of a return value is intentional or
accidental, and waste time figuring that out. You can document it, but many
of us have been known to get in a hurry, sometimes, and skimp on
documentation.

Larry

"Trevor Best" <bouncer@localhost> wrote in message
news:s9kjuvcatcujtn1dpa4p4hirh5pklubm50@4ax.com...[color=blue]
> On Wed, 24 Dec 2003 06:33:47 -0700 in comp.databases.ms-access, "Danny
> J. Lesandrini" <dlesandrini@hotmail.com> wrote:
>[color=green]
> > It is, however, a bad habbit
> >to create a Function without a return.[/color]
>
> Why?
>
> --
> A)bort, R)etry, I)nfluence with large hammer.[/color]

re: Combining multiple tables into one table

MissingAddress@Invalid.Com (Lyle Fairfield) wrote in
<Xns945B3C89495FBFFDBA@130.133.1.4>:
[color=blue]
>One of the reasons that we write functions which return nothing
>and which might appear to more appropriately manifested as subs is
>that macros can find and run functions, but, (unless something
>changed recently) macros cannot find and run subs.
>I believe that menus in early verions of Access found functions as
>their action parameters more consistently and quickly than they
>found subs. In later versions, subs seem to be just fine.
>In the past couple of years, I have used subs by preference, even
>when I need values returned, by passing parameter variables byref.[/color]

Er, you write a function when it needs to return something or be
called in a context where only functions can be called, and you
write a sub where those are not the case.

What's difficult about this?

And exactly when has Access had any problems with functions vs.
subs?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Combining multiple tables into one table

"David W. Fenton" wrote
[color=blue]
> And exactly when has Access had
> any problems with functions vs.
> subs?[/color]

Never, that I recall; it's Access users/developers who have problems with
them.

Larry Linson

re: Combining multiple tables into one table

On Wed, 24 Dec 2003 20:04:10 GMT in comp.databases.ms-access, "Larry
Linson" <bouncer@localhost.not> wrote:
[color=blue]
>Because when someone comes along later to maintain the application, they may
>not know whether the omission of a return value is intentional or
>accidental, and waste time figuring that out. You can document it, but many
>of us have been known to get in a hurry, sometimes, and skimp on
>documentation.[/color]

But if it's a function just to do something and be called from a place
that can't call subs then it doesn't matter if it returns a value or
not, if it does then that return value will be bogus and the later guy
maintaining it will be just as (if not more) confused.

--
A)bort, R)etry, I)nfluence with large hammer.

re: Combining multiple tables into one table

I guess you're right ... of sorts. I just modified a Function in an
Access XP database and turned it into a Sub. That, suprisingly
to me, didn't break the form.

Has this behavior changed since Access 97 (or was it Access 2)
Used to be (I'm sure of it) that if you put this in the Click event
it would break unless you were calling a function.

=ActivateMyProc(123)

If it's not a function, it's not available to the RunCode method
of a macro, not that I ever use macros, but it's another clue to
why one might have gotten into the habit of using Functions instead
of Subs.
--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast


"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote ...[color=blue]
> And exactly when has Access had any problems with functions vs.
> subs?
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

re: Combining multiple tables into one table

Hey, I take that back. It doesn't work to use a Sub in the way
described above ... It HAS to be a function. Putting this in the
property for the Click event compiles, but doesn't work if it
references a Sub.

=ActivateMyProc(123)
--

Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast


"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote ...[color=blue]
> I guess you're right ... of sorts. I just modified a Function in an
> Access XP database and turned it into a Sub. That, suprisingly
> to me, didn't break the form.
>
> Has this behavior changed since Access 97 (or was it Access 2)
> Used to be (I'm sure of it) that if you put this in the Click event
> it would break unless you were calling a function.
>
> =ActivateMyProc(123)
>
> If it's not a function, it's not available to the RunCode method
> of a macro, not that I ever use macros, but it's another clue to
> why one might have gotten into the habit of using Functions instead
> of Subs.
> --
>
> Danny J. Lesandrini
> dlesandrini@hotmail.com
> http://amazecreations.com/datafast
>
>
> "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote ...[color=green]
> > And exactly when has Access had any problems with functions vs.
> > subs?
> >
> > --
> > David W. Fenton http://www.bway.net/~dfenton
> > dfenton at bway dot net http://www.bway.net/~dfassoc[/color]
>
>[/color]

re: Combining multiple tables into one table

To be honest, I've never gone out of my way to document the absence of a
return value on a function that I used only because it was called from a
property. In that instance, I figured any Access contractor worth an hourly
rate ought to know at a glance why a function was used, and why it didn't
matter whether it returned a value.

I was just answering the question -- and that's true, if it isn't an
"obvious" situation like being called from a Property.

Larry

"Trevor Best" <bouncer@localhost> wrote in message
news:6bqluv800is2invkq0ntgbfkcdgt3omsme@4ax.com...[color=blue]
> On Wed, 24 Dec 2003 20:04:10 GMT in comp.databases.ms-access, "Larry
> Linson" <bouncer@localhost.not> wrote:
>[color=green]
> >Because when someone comes along later to maintain the application, they[/color][/color]
may[color=blue][color=green]
> >not know whether the omission of a return value is intentional or
> >accidental, and waste time figuring that out. You can document it, but[/color][/color]
many[color=blue][color=green]
> >of us have been known to get in a hurry, sometimes, and skimp on
> >documentation.[/color]
>
> But if it's a function just to do something and be called from a place
> that can't call subs then it doesn't matter if it returns a value or
> not, if it does then that return value will be bogus and the later guy
> maintaining it will be just as (if not more) confused.
>
> --
> A)bort, R)etry, I)nfluence with large hammer.[/color]

re: Combining multiple tables into one table

dlesandrini@hotmail.com (Danny J. Lesandrini) wrote in
<bsf4nn$9u4lh$1@ID-82595.news.uni-berlin.de>:
[color=blue]
>I guess you're right ... of sorts. I just modified a Function in
>an Access XP database and turned it into a Sub. That, suprisingly
>to me, didn't break the form.
>
>Has this behavior changed since Access 97 (or was it Access 2)
>Used to be (I'm sure of it) that if you put this in the Click
>event it would break unless you were calling a function.
>
> =ActivateMyProc(123)
>
>If it's not a function, it's not available to the RunCode method
>of a macro, not that I ever use macros, but it's another clue to
>why one might have gotten into the habit of using Functions
>instead of Subs.[/color]

And this is a problem with Subs() rather than with controls?

It's obvious, in that circumstance, if you need a function to use
it in an event of a control, then you write a function.

I see nothing inherently unreliable about subs in this, only a
limitation of calling code from controls instead of from other
code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Combining multiple tables into one table

David, you previously wrote ...
[color=blue]
> And exactly when has Access had any problems with functions vs.
> subs?[/color]

Now you write ...
[color=blue]
> I see nothing inherently unreliable about subs in this, only a
> limitation of calling code from controls instead of from other
> code.[/color]

So, Access has no "problem" with Subs, except the "limitation" that
they cannot be used, that is, called, from control property pages.

This was all in response to my comment ...[color=blue]
> I never create Sub procs, since they won't be available to the
> property pages of controls.[/color]

I used neither the word "problem" or "limitation" ... I only explained
why I used a Function without a return value. You should have been
gracious and let it lie, since what I wrote was and continues to be
correct. Now I'm retracting my self-castigation. I was right, and
you were wrong.
--
Danny J. Lesandrini

re: Combining multiple tables into one table

On Thu, 25 Dec 2003 19:35:08 GMT in comp.databases.ms-access, "Larry
Linson" <bouncer@localhost.not> wrote:
[color=blue]
>To be honest, I've never gone out of my way to document the absence of a
>return value on a function that I used only because it was called from a
>property. In that instance, I figured any Access contractor worth an hourly
>rate ought to know at a glance why a function was used, and why it didn't
>matter whether it returned a value.
>
>I was just answering the question -- and that's true, if it isn't an
>"obvious" situation like being called from a Property.[/color]

I generally use Functions rather than Subs nowadays anyway, since they
can be called just the same as subs in VBA and I might want to call
the routine from a property in the future, if I do that then I don't
have to go around and make sure it's called a function and not a sub.
IMHO Subs are a bit redundant nowadays (except as event procedures).

--
A)bort, R)etry, I)nfluence with large hammer.

re: Combining multiple tables into one table

dlesandrini@hotmail.com (Danny J. Lesandrini) wrote in
<bsfqhf$ch20t$1@ID-82595.news.uni-berlin.de>:
[color=blue]
>David, you previously wrote ...
>[color=green]
>> And exactly when has Access had any problems with functions vs.
>> subs?[/color]
>
>Now you write ...
>[color=green]
>> I see nothing inherently unreliable about subs in this, only a
>> limitation of calling code from controls instead of from other
>> code.[/color]
>
>So, Access has no "problem" with Subs, except the "limitation"
>that they cannot be used, that is, called, from control property
>pages.[/color]

That's not a limitation of subroutines, but a limitation of Access
controls.
[color=blue]
>This was all in response to my comment ...[color=green]
>> I never create Sub procs, since they won't be available to the
>> property pages of controls.[/color]
>
>I used neither the word "problem" or "limitation" ... I only
>explained why I used a Function without a return value. You
>should have been gracious and let it lie, since what I wrote was
>and continues to be correct. Now I'm retracting my
>self-castigation. I was right, and you were wrong.[/color]

My question is and always was: why make a routine that will never
be called from a control property into a function, which is what
your statement implies.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Combining multiple tables into one table

> My question is and always was: why make a routine that will never[color=blue]
> be called from a control property into a function, which is what
> your statement implies.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

The original question was, "Why did you write a Function w/o a return
value?" and my answer was, "It's a habit."

Yes, if one consciously plans out the flow of code and knows exactly
where each custom method will be used, then they could make a better
choice of Sub or Function. I used to do that, but after a couple of
times of getting stuck and having to go back and change a Sub into a
Function, I decided to just use functions.

I guess you have to ask yourself if the performance gain from using
Subs outweighs the frustration of rewriting code. For me, it doesn't

re: Combining multiple tables into one table

"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in news:bsi5qd$d3dki$1
@ID-82595.news.uni-berlin.de:
[color=blue]
> I guess you have to ask yourself if the performance gain from using
> Subs outweighs the frustration of rewriting code. For me, it doesn't[/color]

Well, on my rather antiquated work machine, I find that four million calls to
a simple sub take about 4 seconds, while four million calls to the same code
written as a simple function take about 5 seconds.

So, I'd say, "No, it's not worth it to me" (except perhaps in some special
cases where I am investigating all possible permutations of a set of values).

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: Combining multiple tables into one table

dlesandrini@hotmail.com (Danny J. Lesandrini) wrote in
<bsi5qd$d3dki$1@ID-82595.news.uni-berlin.de>:
[color=blue]
>I guess you have to ask yourself if the performance gain from
>using Subs outweighs the frustration of rewriting code. For me,
>it doesn't[/color]

I don't see the difficulty in converting it to a function.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Combining multiple tables into one table

Not difficult ... frustrating.
--
Danny J. Lesandrini


"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote >[color=blue]
>
> I don't see the difficulty in converting it to a function.
>[/color]