1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

access query

Discussion in 'Business Applications' started by mkraus04, Feb 12, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    i have 2 tables
    Closed
    1 hi
    1 now
    2 told ya
    2 fine

    Request
    1 tmnow
    1 tmhi
    2 tmtrythis

    i need to have them combined by the number but the issue is they could have 1 line of different data or 10
    1 hi now tmnow tmhi
    2 toldya fine tmtrythis

    I am not the best with coding any help would be appreciated.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    About the only way to do this is with VBA code and a temporary table to hold the re-formatted data.
     
  3. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    Kind of what I was thinking just having issues since i am not the handy in coding :( . I tried this in excel and it worked using a formula like =INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1))) which worked on a small scale but the scale i need it to didnt work.
    so i am trying to throw it in access. I have the 2 tables combined in a query so i am assuing it may be a module that has to be created to loop through and create fields? thats where i am stuck...
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The information could be in just one table, which would make the VBA code easier.
    At the moment the VBA would need to open one table or query at a time and loop through the records adding each record's second value to the appropriate first value's record.
    ie field one would hold the value 1, the second field would hold the values hi now tmnow tmhi separated by spaces or commas and spaces etc.
     
  5. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    I do have the infomation moved into the one query but in that query some of the information duplicates because of different amounts of data so i wouldnt want it to grab dups either
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post an example database?
     
  7. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    THis is extremly small scale example
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is the significance of the colour field?
    Is that to be included in the summary?
     
  9. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    yes if you take the ID of 5 the way i need it to spit out the the info kindof like below
    ID | names | service1 | service2 | service3 | service request | service request
    5 | my test | Carwash | door to door| lunch | blue |
    6 | now try | carwash | gas | door to door| blue | pink | yellow | green |
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Does it need to be in actual Columns, or can it be
    5 | my test | Carwash | door to door| lunch | blue |
    or
    5, my test, Carwash, door to door, lunch, blue
     
  11. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    It would make it easier to be in seperate columns because i have to match the service with the request but i can make it work however
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Will the "matching" take place in Access?
     
  13. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    No I dont think so
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well here is the method I described.
    There is a form called Form1, open the form and click on the Command Button.
    You can look at the table called Combined first if you like as I have already run it once and then delete the data, there is also a query to delete the data which is run prior to updating the table and there is a query to display the contents of the "Combined" table as well.

    If the data in the services and Service Requests fields are likely to exceed 255 characters change them to "Memo" type.
     

    Attached Files:

  15. mkraus04

    mkraus04 Thread Starter

    Joined:
    May 30, 2012
    Messages:
    26
    It works but when i brough all the data in i got error# 6 overflow
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089235

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice