Results 1 to 11 of 11
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    114

    Need to append data from multiple tables

    hello guys,
    I have 25 different warehouse table and all of them has same fields and data. I built a query for with other tables using only 1 warehouse table and want to append all warehouse.

    Below is the example. This is one way of append all data but my query would be more 20 pages. what is best approach ? I dont know much about loops , can we use loop instead. Thanks in advance.


    Select ponum, ............. from potble t1
    inner join warehouse tbl1 t2 on t1.ponum=t2.ponum

    union all

    Select ponum, ............. from potble t1
    inner join warehouse tbl2 t2 on t1.ponum=t2.ponum

    Union all

    Select ponum, ............. from potble t1


    inner join warehouse tbl3 t2 on t1.ponum=t2.ponum
    .
    .
    .
    .
    .
    .
    .
    .

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    I'm not sure why you'd care how long the query was but:

    Code:
    For x = 1 To 25
      strSQL = "SELECT...tbl" & x & "t2 ON..."
    Next x
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,953
    post deleted
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    114
    Quote Originally Posted by pbaldy View Post
    I'm not sure why you'd care how long the query was but:

    Code:
    For x = 1 To 25
      strSQL = "SELECT...tbl" & x & "t2 ON..."
    Next x
    Hi I didnt get this point

    you mean
    SELECT...tbl1, tbl2 , tbl2 " & x & "t2 ON...

  5. #5
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    808
    Quote Originally Posted by adnancanada View Post
    Hi I didnt get this point

    you mean
    SELECT...tbl1, tbl2 , tbl2 " & x & "t2 ON...
    No, I believe Paul is saying you build the sql statement using the loop index to select the relevant table.

    So when x = 1
    Select ponum, ............. from potble t1
    inner join warehouse tbl1 t2 on t1.ponum=t2.ponum

    when x = 2
    Select ponum, ............. from potble t1
    inner join warehouse tbl2 t2 on t1.ponum=t2.ponum

    and so on

    However I am surprised it was not mentioned that your DB appears to not be normalised and should likely be a warehouse table with a field to indicate which warehouse.?

    HTH

  6. #6
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    114
    Quote Originally Posted by Welshgasman View Post
    No, I believe Paul is saying you build the sql statement using the loop index to select the relevant table.

    So when x = 1
    Select ponum, ............. from potble t1
    inner join warehouse tbl1 t2 on t1.ponum=t2.ponum

    when x = 2
    Select ponum, ............. from potble t1
    inner join warehouse tbl2 t2 on t1.ponum=t2.ponum

    and so on

    However I am surprised it was not mentioned that your DB apperas to not be normalised and should likely be a warehouse table with a field to indicate which warehouse.?

    HTH
    Can we run as select query.

    for example make it simple i have 3 tables and takes one field.


    select distinct whse from pr200_whse
    union all
    select distinct whse from pr300_whse
    union all
    select distinct whse from pr400_whse;

    it gives me result

    WHSE
    01
    02
    03

    How can run in select statement using loop. I am confused. Do we need to create store procedure for it?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    My perhaps wrong assumption is that this was an attempt to normalize the db (appending the numerous tables into one). Perhaps you should give us the overall goal here. The UNION query you first posted should work as a source to append the tables into one. You could do them one by one in a loop by why bother if you already have the UNION query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    114
    Quote Originally Posted by pbaldy View Post
    My perhaps wrong assumption is that this was an attempt to normalize the db (appending the numerous tables into one). Perhaps you should give us the overall goal here. The UNION query you first posted should work as a source to append the tables into one. You could do them one by one in a loop by why bother if you already have the UNION query?
    I do not want to use Union All query then I have to do for 25 tables. That is why I want to use loop

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,764
    Is this not a one-time need? If you insist on a loop, you have not specified what you want to do within that loop. My generic example showed how to use the loop variable (x in my example) in an SQL statement you built in code. When x = 1 you'd get "tbl1", when x = 2 you'd get "tbl2", and so on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    771
    If you want to use a loop in T-SQL on SQL server, best practice is building a stored procedure with as output the result table. I would build a procedure that uses a temp table and adds the records of all 25 tables to the temp table. As output you make a select of all records from that temp table and then delete the temp table.

    To get the tables in a loop you can build a cursor to get all involved tables like:
    Code:
    DECLARE CMyTables cursor fast_forward for
    
    
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_NAME like 'pr%_whse'
    With this cursor you can build and execute in a loop a dynamic insert SQL string for each table.

  11. #11
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    114

    Thumbs up

    Quote Originally Posted by NoellaG View Post
    If you want to use a loop in T-SQL on SQL server, best practice is building a stored procedure with as output the result table. I would build a procedure that uses a temp table and adds the records of all 25 tables to the temp table. As output you make a select of all records from that temp table and then delete the temp table.

    To get the tables in a loop you can build a cursor to get all involved tables like:
    Code:
    DECLARE CMyTables cursor fast_forward for
    
    
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_NAME like 'pr%_whse'
    With this cursor you can build and execute in a loop a dynamic insert SQL string for each table.
    Thanks that is good idea. I will try.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. append to one table using multiple tables
    By tagteam in forum Access
    Replies: 2
    Last Post: 06-27-2015, 07:29 AM
  3. Replies: 6
    Last Post: 02-10-2014, 07:43 AM
  4. Combine Multiple Access Files into One and Append Tables
    By KLTurner in forum Import/Export Data
    Replies: 10
    Last Post: 01-02-2014, 11:38 AM
  5. Append to multiple tables
    By GraemeG in forum Queries
    Replies: 1
    Last Post: 04-05-2012, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums