Results 1 to 2 of 2
  1. #1
    michaelwarr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    1

    How do you extract multiple results from a single query

    Hi,

    Any help on this will be greatly appreciated.

    I have a list of sales customers (acc code, addresses, email, phone etc)
    and a list of all the products they have bought over the past twelve months.

    the problem I have is extracting each customers spend.

    On the table containing the product Info it has the account code next to each product.

    How do I get a query to run to show each account and then show all of the products they have bought over the last twevle months???

    I am trying to create a database, with an easy access form whereby I can create a record for each account showing their contact details, then on a seperate tab within the form have all of the products they have purchased??



    Sorry if I haven't explained it too clearly if you need any further info then please ask..

  2. #2
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    Two ways:

    Using a query named query1 in this example:

    Contents of query1:
    Code:
    select [acc code] & "|" & [addresses] & "|" & [email] & "|" & [phone] as Field1 from table1
    and a little bit code
    Code:
    dim strResult as string
    dim arr() as string
     
    strResult = dlookup("Field1","query1")
    arr = split(strResult, "|")
     
    'Number of elements
    'Ubound(arr,1)
     
    'First element
    'arr(0)
     
    'Second element
    'arr(1), etc.
    or you can use the method most people use:

    Code:
     
    dim rst as recordset
    dim str1st as string
    dim str2nd as string
     
    set rst = currentdb.querydefs("queryname")
     
    if not rst.eof then
        str1st = rst.fields("acc code")
        str2nd = rst.fields("addresses")
    endif
    Enjoy!

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

Similar Threads

  1. Multiple UPDATE in single query
    By nishezaman in forum Access
    Replies: 0
    Last Post: 12-13-2009, 01:40 AM
  2. Multiple Query Results Sorted Together
    By Rawb in forum Reports
    Replies: 1
    Last Post: 12-10-2009, 04:05 PM
  3. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM
  4. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  5. Replies: 1
    Last Post: 07-07-2009, 01:00 PM

Tags for this Thread

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