Results 1 to 8 of 8
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    access qry to vba function

    All; using 2010. I have inherited a database which has a qry joined with about 12 tables which has about 6 calculations. i'm working to revise the query for better efficiency. Is it possible to convert the query to a vba function to better utilize the calculations?
    Thanks in advance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by convert to VBA? How do you propose VBA get the data from the tables? DLookup? Open a recordset object? What do you want to do with the calc results?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the issue with the current setup that has you looking for more efficiency?
    If you post the query sql, you may get focused responses.

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thank you all for replying. This is a thought I have to make it easier on the user. I feel there are too many queries with too many fields to get to results. I thought maybe if I put it in a function and call it from a button on a form after I make it more efficient. The calculated results which may be about 4000-5000 records read in a report. Again this is something I really want to make happen but I am trying to find a beginning point. To answer the question of how I can get the data; I hadn't thought that far but I wanted to know my options. Like I stated; this is a query so I didnt know if I should start with a dlookup or recordset because of the many joining tables to get the information. I am open to any ideas from the experts. I will post the query as soon as I can. Thanks again.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Without knowing more about the data and the calculations hard to say if VBA would be helpful. Some data manipulations just might necessarily involve a series of queries to get the final output on report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I tried to remove obvious confidential field names but this is a sample of what I am talking about:

    Code:
    SELECT qryUNS.UNSid, tblTax.custid, tblTax.St, tblTax.grosswgs, tblTax.taxablewgs, IIf([grosswgs]/[taxablewgs]>0,0,[grosswgs]/[taxablewgs]) AS adjusttax., IIf([qrytax1].[rte] Is Null,[qrytax1].[revn],[qrytax1].[Nondisp_rate]) AS disp, IIf([qrytax2].[nte] Is Null,[qrytax2.[nontx],[qrytax2].[cost]) AS nondisp, IIf([cin]>0,"audit",IIf([max] Is Null,"noaudit","minaudit")) AS aud, IIf([num]>0,”N”,”Y”) AS audn, IIf([qryPermissions.cust_id] Is Null,"Y","N") AS granted, qrycust.custid, tblchg.stat, qryWgsN.[sDisp], qryGgsS.bDip
    FROM ((((((((((tblTax LEFT JOIN qrytax1 ON (tblTax.custid = qrytax1.id) AND (tblTax.Sid = qrytax1.Sid)) LEFT JOIN qrytax2 ON (tblTax.Sid = qrytax1.[Sid]) AND (qrytax2.id = qrydisWgs.cust)) LEFT JOIN qryTblchg ON (tblTax.ID= qryTblchg.ID) AND (tblTax. id = qryTblchg. id)) LEFT JOIN qryPermissions ON (tblTax.St = qryPermissions.cust_id) AND (tblTax.id = qryPermissions.cust_id)) LEFT JOIN qryWrates ON (qryPermissions.cust_id= qryWrates.[id] AND (qryPermissions.cust_id = qryWrates.ID)) LEFT JOIN qryGrnts ON (tblTax.ID = qryGrnts.ID) AND (tblTax.cust_id = qryGrnts.id)) LEFT JOIN qrydisWgs ON (tblTax.ID = qrydisWgs.[disP]) AND (tblTax.id = qryWgs. id)) LEFT JOIN qryGgss ON tblTax.id = qryaud.id) LEFT JOIN qryUNS ON (tblTax.id = qryUNS.sid) AND (tblTax.id = qryUNS.id)) LEFT JOIN tblDt ON tblTax.id = tblID.cid) LEFT JOIN tblAud ON tblID.num = tbldis.num
    WHERE (((qrytax2. disp) is not null));

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How can a field name be confidential?

    That is a very long query and it is hard to read. Too hard to interpret and try to gain understanding of data structure. The calcs don't look so complicated that VBA is called for.

    Yes, there are a lot of table joins and I presume the are vital to the query.

    Just have to leave it up to you to analyze the situation and determine best approach.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok Thanks for looking

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

Similar Threads

  1. VLookup function in Access?
    By mveda2004 in forum Queries
    Replies: 2
    Last Post: 02-25-2013, 09:42 PM
  2. how to build a function in access
    By AmandaMc in forum Access
    Replies: 6
    Last Post: 02-10-2013, 07:38 PM
  3. Using the Nz function (Access 07)
    By jonny3000 in forum Access
    Replies: 5
    Last Post: 11-15-2012, 12:46 PM
  4. function in access
    By ali zaib in forum Access
    Replies: 3
    Last Post: 01-14-2012, 01:09 PM
  5. Help with Access - IIf Function
    By cs93 in forum Programming
    Replies: 7
    Last Post: 03-19-2011, 11:52 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