Results 1 to 27 of 27

Thread: [RESOLVED] never done this before How to insert 4 tables to 1 table called customers

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Resolved [RESOLVED] never done this before How to insert 4 tables to 1 table called customers

    i have a table called customers
    also i have 4 more tables called y1 y2 y3 y4
    each table have diffrent number of customers
    i need to group all the customers from the tables and insert them all into customers table
    now in those tables i have customers
    how do i insert all the 4 tables in the customers tables?
    e.x
    Code:
    in y1 i have 30 customers
    field name a 
    in y2 i have 900 customers
    field name b
    in y3 i have 70 customers
    field name c
    in y4 i have 500 customers
    field name d
    i know how to insert only 1 table how do i do the rest?
    Code:
    INSERT INTO Customers ( FullName)
    SELECT a From Y1
    so in the customers table i need to see the total 1500 customers that are joined from all the tables
    the ID of the customers table is autonumber
    tnx for any help
    salsa31

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,442

    Re: never done this before How to insert 4 tables to 1 table called customers

    will all these customers in the five tables be DIFFERENT from each other, or will some be duplicates?

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,442

    Re: never done this before How to insert 4 tables to 1 table called customers

    I know how "I" would do it (the bull and jamb way)...I would select the customers' names from each table, put them into a listbox, then after I have gone through Y1 thru Y4 to do that, I'd loop through my listbox and insert the customers' names into my customers table.

    I am 100% certain ONE single SQL can be made to do that, but my way is simpler to me.

    EX
    Code:
    Select customers from Y1 into a recordset (rs)
    do while not rs.eof
        list1.additem rs!customers
    loo
    p

    Do that for each table

    then

    Code:
    for x = 0 to list1.listcount -1
        list1.listindex = x
        insert into customers (custname) values (list1.text)
    next x

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,278

    Re: never done this before How to insert 4 tables to 1 table called customers

    If you want all of those customers in the same table and you know how to use a insert into select from then you know how to do it already. Did you not consider just using that method for each table?

    I definitely would not use the Listview method above, more code to write and much slower to execute.

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: never done this before How to insert 4 tables to 1 table called customers

    i am in access making the query
    not in vb 6

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,278

    Re: never done this before How to insert 4 tables to 1 table called customers

    So is there a reason why you did not just do 4 queries and be done with it?

  7. #7

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: never done this before How to insert 4 tables to 1 table called customers

    So is there a reason why you did not just do 4 queries and be done with it?
    i dont know how sir not from access it self
    this is only what i knw from access
    Code:
    INSERT INTO Customers ( FullName)
    SELECT a From Y1

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,278

    Re: never done this before How to insert 4 tables to 1 table called customers

    What do you mean you don't know?
    execute that query and then change Y1 to y2, a to b and execute it again repeat for 3 and 4 and you are done.
    Extremely simple and you know how to do it already. could have been done in less time that it took to create the thread.

    btw why on earth would you name fields for customer names a,b,c,d and have it different in every table? That is a very bad idea.

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,442

    Re: never done this before How to insert 4 tables to 1 table called customers

    I definitely would not use the Listview method above, more code to write and much slower to execute.
    But "I" would..... Just makes SENSE to me (listbox, not listview). I am not good at MS Access SQl compound queries, so I do things a bit slower, but easier for me. Too old to learn that stuff in detail.....

    But seeing as how salsa is doing this in Access, his thread should have been posted in the Office Development forum anyway.

  10. #10

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: never done this before How to insert 4 tables to 1 table called customers

    i think you dont understand sir
    i need to insert all the tables into customer at once
    Code:
    INSERT INTO Customers ( FullName )
    SELECT c  from y1
    SELECT a From Y4
    btw this is not working

  11. #11
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: never done this before How to insert 4 tables to 1 table called customers

    Why not use something like

    INSERT INTO Customers ( FullName)
    SELECT a as FullNameFrom Y1
    UNION
    SELECT b as FullName From Y2
    UNION
    SELECT c as FullName From Y3
    UNION
    SELECT d as FullName From Y4
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  12. #12

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: never done this before How to insert 4 tables to 1 table called customers

    bingo!!!! tnx colin

  13. #13
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: never done this before How to insert 4 tables to 1 table called customers

    Quote Originally Posted by salsa31 View Post
    i think you dont understand sir
    i need to insert all the tables into customer at once
    Code:
    INSERT INTO Customers ( FullName )
    SELECT c  from y1
    SELECT a From Y4
    btw this is not working
    Hardly surprising as field a is not in Y4 and field c is not in Y1!
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  14. #14
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: never done this before How to insert 4 tables to 1 table called customers

    Quote Originally Posted by salsa31 View Post
    bingo!!!! tnx colin
    De nada
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  15. #15

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    BTW colin if i want to insert 2 fields i do like this?
    Code:
    INSERT INTO Customers ( FullName,Cellular)
    SELECT b as FullName,a as Cellular From za
    UNION
    SELECT d as FullName,c as Cellular From zb
    because i get a error here

  16. #16
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Correct!

    EDIT: Just noticed your last line. What error are you getting. Always helpful to say...
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  17. #17

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    i get a error

  18. #18
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Quote Originally Posted by salsa31 View Post
    i get a error
    Yes. We've established that. What error?
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  19. #19

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Name:  ERRRRRRR.jpg
Views: 33
Size:  15.2 KB
    Code:
    INSERT INTO Customers ( FullName,Cellular)
    SELECT b as FullName,a as Cellular From za
    UNION
    SELECT d as FullName,c as Cellular From zb

  20. #20
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Hmm. I don't use Access but the SQL looks fine to me. Don't know why the error message suggests otherwise!

    Are you sure that's the exact SQL you are trying to execute?
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  21. #21

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    every thing is ok collin
    my bad sry
    tnx for your help

  22. #22
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    OK but you really do need to check things before posting here, salsa!
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  23. #23
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,278

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    I still don;t understand why you could not have just ran 4 queries to start with since that was the way you said you understood. Why would it matter if you ran 1 query or 4 the same data would end up in the customer table when it was done and that was your stated goal. Either you did not explain your issue correctly or you just did not take a minute to think about it.

    Edit: Just to be clear consider this

    TblCustomers starts with no records in it
    You run a query to insert all records from y1 so tblcustomers now has all records from y1
    You run a query to insert all records from y2 so tblcustomers now has all records from y1 and y2
    You run a query to insert all records from y3 so tblcustomers now has all records from y1, 2 and 3
    You run a query to insert all records from y4 so tblcustomers now has all records from y1,2,3 and 4

    or you run a more complex query that you do not understand that does it all at once.

    In both cases you end up with all the records in the table where you want them the difference is that the first one you could have done yourself in less than 5 minutes and the second one required you to create a thread and multiple posts to get to the same result
    Last edited by DataMiser; Mar 19th, 2015 at 12:42 PM.

  24. #24
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Perhaps there were duplicates across the 4 tables. Running 4 separate queries might have seen one or more of them fail a constraint check whereas a UNION automatically resolves all the duplicates. Who knows...
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  25. #25
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,278

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Hard to say, no info provided but if that were the case and there were unwanted dupes that would have only needed a simple where clause to filter out which salsa also knows how to do.

    Of course there may have been some other reason that just was not mentioned. It is not uncommon for the OP to be lacking required info.

  26. #26
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Quote Originally Posted by DataMiser View Post
    It is not uncommon for the OP to be lacking required info.

    Or subsequent posts in the case of salsa
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  27. #27
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,120

    Re: [RESOLVED] never done this before How to insert 4 tables to 1 table called custom

    Quote Originally Posted by DataMiser View Post
    if that were the case and there were unwanted dupes that would have only needed a simple where clause to filter out which salsa also knows how to do.
    Which would require a NOT EXISTS or NOT IN and that is (arguably) more difficult than the UNION?
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

Posting Permissions

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



Click Here to Expand Forum to Full Width