Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Appending Tables (OFFICE 97 SR2)

    I have 6 tables within 1 mdb that I want to append into one table, which I will call 'AllTables' for discussion purposes. First, I copy one of the 6 and rename it 'AllTables'. Then I create a New query, bring in one of the other 5 tables and append it to 'AllTables'. Do I have to go through this operation 5 times (once for each of the other 5 tables) or is there some way I can append all 5 tables at once.
    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    If it's a one time deal, just going through the motions 5 times would be my recommendation, as well as my practice.

    When I've had to do something along these lines multiple times, i.e. a "repository" table that gets filled from multiple constituent tables all with the same field names, I've built the SQL statement in code, then changed the source table in the FROM clause while looping through the constituent tables.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Shane:
    Thanks for the response. I do need to go through these steps for 5 other databases. Here is how it goes:
    "The 6 tables within 1 mdb" are for the first 6-months of the year. I have 5 other databases that each have tables for each of the 6 months. In addition, I will need to set up for appending tables for each of the next 6 months of the year. So, I do need to automate.
    I understand what you mean when you say you "changed the source table in the FROM clause" but what did you mean by "looping through the constituent tables." Are you suggesting that I simply copy the SQL code that I have been using to each of the other databases and then go through the process of bringing in the other 5 monthly Tables one by one. Or were you suggesting that I use VBA code to loop through the process for each table. If the later, then I need a little help because I have never writen VBA code in ACCESS before.
    Thanks for whatever help you can give. <img src=/S/help.gif border=0 alt=help width=23 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    I'm a touch confused why there are so many databases, and why each month has its own table in a given db. Can you describe what you have going on for a process? Maybe we can simplify what you have to do.

    BTW, what I had were multiple text files, all with the same file layout (sales records from multiple branches of a company - their format, not mine! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>). The code imported each text file, then iterated through each table in the Tables container, and appended it to the "repository" table.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Shane:
    If you have the time, maybe reading the posts associated with Linking Many Access DBs Using an External DB Table (MSOffice97 SR2) Post: 61312 might help you understand what I am doing. I am working with one of the 6 databases now and have progressed to the point where have combined the 6 monthly tables and am writing queries to get needed outputs.
    If, after reading these posts, you are still willing to hang in there with me on this, give me a reply and we can start trying to narrow the focus to the skill areas where I need the most help.
    Thanks for your responses so far.
    Stephen <img src=/S/help.gif border=0 alt=help width=23 height=15>

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Why append into a new table? if you just want a quick look at the combined data from the tables you can create a new query

    select * from tbl1
    union
    select * from tbl2
    union
    etc etc

    This is a bit slow when executed and will only work if the number of fields is the same, same order etc but it appears that this would be the case.

    Depends on what you are doing and what you need but is an easy solution. To add a new table to the pot just add a new union statement.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Thanks, Stewart. I will try it. I have a couple of related question, though.
    1) I have to bring in some fields from another Table (call it Table X) using a query that links the results (of this union query) to another table (call it Table X). So, would it be better (faster) to use the union query as a make Table Query and then link this Table with Table X or would it better to link the Union Query to Table X and do the second query this way?
    2) Instead of bringing the 6 Tables into one database (they reside in 6 separate Databases) and then using the Union query, would I be better off linking these databases together. If I link them, would the union query still be appropriate or is there some other way of doing it (using a query to access the data from all 6 tables without importing them into one database first)?
    Thanks

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    No worries, glad you thought the reply was useful.

    1. basing a query on the union and table X is certainly a viable option. If speed is a factor then make a table from the union query and create indexes in the new table for the fields you will be joining on, and any fields that you are particularily interested in using for queries etc.

    2. I'd link the tables in this case rather than import the data. The advantage is that the data is dynamic, assuming that the tables are production tables rather than manufactured for your particular exercise.

    If the tables are manufactured using a make table query, you may wish to change your process to create the tables in your final database rather than in the source database and then linking them.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Linking works like a charm. I had some problems when I tried it the first time for some reason. It simplifies getting all the data into one place to be queried and also eliminates the necessityh of going through all the steps when the next month's data becomes available. I ran queries against the linked tables and I did get the same results had I run the same queries against the appended table (one hwere I appended the 6 into 1). However, my objective is to get the data into EXCEL, where I have a set up where the user can click on a variable name and so generate a chart showing the accuracy trendline for that variable across the months (1 average accuracy figure for each month). This is much easier than running queries out of ACCESS a variable at a time.
    So, that means that I have to do the Append queries or the union query that you suggested after I do the linking and before I EXPORT to EXCEL or Query the database from EXCEL. I have a problem with the Union query. I cant use it to create a table. So, I guess I would export it directly to EXCEL. I would rather use MSQUERY to import it bu I need to have a Table to link to.
    Once I solve this problem, I would like to build some simple interface that would, based on the proper actions by the user, do this linking and appending (I am open to suggestions here). Then, I can write some VBA code in EXCEL to send MSQUERY off to bring the data in from ACCESS and then set up to generate the trend lines.
    Does this sound like a good strategy? I am having a little difficulty knowing how much detail I should provide to get the guidance that I need.
    By the way, if I do have to do the Appending to create the table I need, do I have to do the Append one Table at a time or is there a way to do them all at the same time? Your patience is appreciated. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    you can base a make table query on the union query ie

    SELECT qryUnion.field[n] INTO tblNewTable
    FROM qryUnion;

    To me it sounds like the export to excel is making work for yourself. The ability to calculate an average for a variable on demand in an access query is there. Linking this to a control or list box on a form is also quite straight forward. Creating a Chart in a report is also fairly straightforward, not as simple as excel I admit, but the advantage is again, linked tables with the variable average based on a query provides live data to your users.

    Of course if you are quite competent in excel and a little lost in access you are probably doing the right thing. You could shell to access from excel and execute the query, or use access to open the database, waiting for it to terminate. In the autoexec macro, run the make table query and then exit access, then fire off your processing from withing excel.

    My patience is endless, your thanks is appreciated. Do you need more detailed explanations?

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Got the "base(ing) a make table query on the union query ". We are making progress now. I ran "UnionQuery":
    SELECT *
    FROM [Feb]
    Union
    SELECT *
    FROM [Aug]
    Union
    Select *
    from July
    .
    .
    UNION
    Select * from Sept;
    Then, I ran a MakeTable query using Select * from UnionQuery.
    Can this be done in 1 step? Or, would I run the queries in sequence "In the autoexec macro"? Other questions:
    1) by "shell to access from excel" are you suggesting that my VBA code in EXCEL would 'Shell' the user to ACCESS where he would take some action to link the files and run the UnionQuery, export to EXCEL, and close ACCESS? Then, my EXCEL VBA query would continue by importing the ACCESS data using an ODBC connection with MSQuery. Is this where I am heading?
    2) what do you mean by "or use access to open the database, waiting for it to terminate" -- for what to terminate?
    3) RE: "In the autoexec macro, run the make table query,
    and then exit access" ... How do I do this?
    I think that I will stay away from "Creating a Chart in a report", for now, as I have yet to get my feet wet with ACCESS reports and "provides(ing) live data to your users" is not an issue because once these source databases are created they do not change.
    I have to put "Linking this to a control or list box on a form" on my agenda. I have been putting off working with Forms for too long.
    Thank you so much. It feels good to be asking pointed rather than general questions, for a change.
    Stephen

  12. #12
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending Tables (OFFICE 97 SR2)

    Then, I ran a MakeTable query using Select * from UnionQuery.
    Can this be done in 1 step? Or, would I run the queries in sequence "In the autoexec macro"?

    A. The union query does not need to be run at all. When the make table query is executed the union query provides the data. In effect the make table query is running the union query in the background.

    1) what do you mean by "shell to access from excel" .. I was thinking about getting the data from Access using an ODBC connection with MSQuery.

    A. using the following, to open access & your db where the autoexec will make the table you need and quit access.

    ***************** Code Start ******************
    'This code was originally written by Terry Kreft.
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code Courtesy of
    'Terry Kreft
    <pre>Private Const STARTF_USESHOWWINDOW& = &H1
    Private Const NORMAL_PRIORITY_CLASS = &H20&
    Private Const INFINITE = -1&

    Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
    End Type
    Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
    End Type
    Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
    hHandle As Long, ByVal dwMilliseconds As Long) As Long
    Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal _
    hObject As Long) As Long
    Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
    .cb = Len(start)
    If Not IsMissing(WindowStyle) Then
    .dwFlags = STARTF_USESHOWWINDOW
    .wShowWindow = WindowStyle
    End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
    NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ret& = CloseHandle(proc.hProcess)
    End Sub
    </pre>

    '***************** Code End ****************


    2) what do you mean by "or use access to open the database, waiting for it to terminate" -- for what to terminate?

    A. wait for access to terminate. When access closes the new table has been created and then you can attach via ODBC.

    3) RE: "In the autoexec macro, run the make table query, and then exit access" ... How do I do this?

    A. create new macro
    Action = "OpenQuery". Query Name = your query name.
    Action = "Quit"

    Save the macro as "AUTOEXEC" and that is all you have to do.


    4) RE: "then fire off your processing from within excel." Is there any way that I could control opening ACCESS and firing off the AutoExec Macro from VBA within EXCEL?

    A. This is what 1) should do. You could also use automation to do this, but I'm not sure if your vba code stops when opening an automation object. you definitely want your code to open access, make the table and then return control to excel, ie executing the next line of code.


    "I think that I will stay away from "Creating a Chart in a report", for now, as I have yet to get my feet wet with ACCESS reports and "provides(ing) live data to your users" is not an issue because once these source databases are created they do not change.
    I have to put "Linking this to a control or list box on a form" on my agenda. I have been putting off working with Forms for too long."

    Access reports are fairly easy but as with anything if you don't have the experience it can take much longer than it should. Working with access forms is again very simple. if you have a grip on VBA in excel it should be fairly easy to make the move to access, just getting used to a new group of commands.

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •