[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Combine tables and note which table each record came from

This may be a very easy problem to solve, but apparently not for me!

I have an MDB output from a Geographical Information System which on this occasion gives me around 60 tables of identical structure.  However, some of these tables may contain no records while others may contain 20+ records.  Table structure in the code window below. Fields are either Number or Text, so no messing around with dates.

I want to combine these tables into one, and have a new field which contains the name of the table each record came from.  I can easily make a list of the table names to be used in some sort of VBA loop.

This is something I will have to be doing on an ongoing basis so the easiest solution to apply to other situations would be greatly appreciated!
Field			Type
---------------------------------
OBJECTID		Number
Shape			Number
FID_MA2_Roadclip	Number
FID_MA2_Huntclip	Number
FID_MA2_Irrigclip	Number
FID_MA2_Buildingclip	Number
FID_MA2_Cultclip	Number
FID_MA2_Fertclip	Number
FID_MA2_Herbclip	Number
FID_MA2_Piripclip	Number
FID_MA2_Outfallclip	Number
FID_MA2_MinOilclip	Number
FID_MA2_Pruningclip	Number
Landuse			Text
SA			Number
Shape_Length		Number
Shape_Area		Number

Open in new window

0
HPyne
Asked:
HPyne
  • 5
  • 5
  • 4
  • +1
1 Solution
 
dqmqCommented:
Insert into NewTable
  Select 'TableName1', * from TableName1
  union all
  Select 'TableName2', * from TableName2
  union all
  Select 'TableName3', * from TableName3
...  
0
 
dqmqCommented:
To create the new table:

Select into NewTable
'Any String wide enough to accomodate longest table name' as TableName, * from TableName1 where 1=2  

0
 
HPyneAuthor Commented:
Thanks for the prompt reply.  I thought of a union query, but how could I make this work with adding a field with the table name each record came from?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Andrew_WebsterCommented:
This might get you going in the right direction:
1. In VBA open a recordset based on the table names in MsysObjects (the system table that lists all Access objects)
2. Write some code to "SELECT * INTO ..." the data from the first table into a new combined table (see air-code below)
3. Cycle through the rest of the recordset using "INSERT * INTO ..." to append the rest of the data.

This is "air-code" - in other words made up on the fly, not tested, shot in the dark.  It's up to you to tweak this!

Dim rst As New DAO.Recordset
Set rst = CurrentDB.OpenRecordset ("SELECT Name FROM MsysObjects WHERE Type = 4")  'This might be wrong, check Access help for the right type, or just open MsysObjects and work it out!'

rst.MoveFirst
DoCmd.RunSQL "SELECT * INTO MyNewCombinedTable FROM " & rst!Name
rst.MoveNext
Do Until rst.EOF
    DoCmd.RunSQL "INSERT * INTO MyNewCombinedTable FROM " & rst!Name
    rst.MoveNext
Loop

Open in new window


Sorry to leave you with aircode, but as usual, I'm under a time crunch, but couldn't resist offering some help!
0
 
HPyneAuthor Commented:
Andrew, thanks, this gives me a starting point. I'll look into adapting this.

I presume there won't be a problem with it trying to add data from MyNewCombined table!
0
 
Andrew_WebsterCommented:
Good point.  Check the Access/VBA help on creating a recordset, and either have it be Static (i.e. the same records as when created) or put a test into the loop...
if rst!Name <> "MyNewCombinedTable" Then
    'go ahead as planned...'
End if

Open in new window

0
 
dqmqCommented:
>I thought of a union query, but how could I make this work with adding a field with the table name each record came from?

See the example in my answer.  The first field is the table name.
0
 
HPyneAuthor Commented:
dgmg: I get a "query is too complex" error when I try this method of multiple union queries.
0
 
dqmqCommented:
Break it into pieces.
0
 
Andrew_WebsterCommented:
It just struck me.  You'll have to create a new column "OriginalTable".

So maybe the best thing is to copy and paste one of the table, structure only, then add that column by hand as the last column.

(Otherwise you'll have to do this in code either using an "ALTER TABLE" statement and DoCmd.RunSQL or using DAO or ADOX - once again, I refer you to the VBA help!)

Add in to my code above
...
INSERT *[u], rst!Name[/u] INTO MyNewCombinedTable
...

Open in new window


so that your original table name is recorded with it's data.

None of this is difficult, it's just fiddly, like most code.  Do it a bit at a time, test as you go, and you'll manage it, I'm sure.

(BTW, a UNION query sounds good in theory, but as you've found, would hit Access's limitations.  If this was SQL Server or Oracle, you'd have a chance.)
0
 
Andrew_WebsterCommented:
Oh.  I was trying to underline a bit of that code - didn't work!

INSERT *, rst!Name INTO MyNewCombinedTable

was how I was trying to emphasize it.
0
 
Nick67Commented:
Hey,

Here's a working sample based on your original post to work with.
Explore the form and its code
Test.mdb
0
 
HPyneAuthor Commented:
Although Andrew's solution certainly got me most of the way there with little work, I feel that Nick67's example worked perfectly for my data.

Thanks all who responded, much appreciated for digging me out of my hole!
0
 
HPyneAuthor Commented:
Very easy to adapt to other situations and to see what you've done.
0
 
Nick67Commented:
Thanks!
Glad it worked for you.

Nick
0
 
Andrew_WebsterCommented:
Ditto.  Wish I'd had more time to do what Nick did!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now