Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006

    Unanswered: help with sql problem in access


    Im a newbie to sql and i have a problem. Im using sql in access and I have two tables and i want to query it using parameters that a user select from buttons and drop down menus in a form. I can query it once and all the information shows up perfectly in the form. Heres is the sql im using:

    Me.RecordSource = "SELECT tblMISO_Prices_DA.DATE, Avg(tblMISO_Prices_DA.LMP) AS DA_AVG2, Avg(tblMISO_Prices_RT.LMP) AS RT_AVG2 " & _
    "FROM tblMISO_Prices_DA INNER JOIN tblMISO_Prices_RT ON (tblMISO_Prices_DA.HE = tblMISO_Prices_RT.HE) AND (tblMISO_Prices_DA.DATE = tblMISO_Prices_RT.DATE) " & _
    "WHERE " & strFilter & " tblMISO_Prices_DA.NODE LIKE """ & IIf(IsNull(cboxDANode2), "*", cboxDANode2) & """ AND tblMISO_Prices_RT.NODE LIKE """ & IIf(IsNull(cboxRTNode2), "*", cboxRTNode2) & """ " & _
    "GROUP BY tblMISO_Prices_DA.DATE, tblMISO_Prices_DA.NODE; "

    Now the Problem: I want to be able to query it again but leaving the first query visible and then compare the two queries. Is there way to keep the firsty query constant? Or even a sql statement that can query the same table twice with different parameters?

    The overall image is...

    table names are: MISO_Prices_RT and MISO_PRICES_DA
    columns are same for both tables: DATE, NODE, LMP

    Please any advise would be great.

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    how are you planning to display the result?

    your current example uses .recordsource so i imagine you have a form. a form can have zero or one recordsource (so forget two). you could have two subforms each with it's own recordsource, or if this is for viewing, two lists (use .rowsource of course), or one list one form, or a UNION of both queries in one form or list, or ...there are many possible ways.

    how do you want the stuff displayed?
    how do you intend the user to specify the two different sets of criteria (both at once or sequentially one after the other)?
    is one or both of the recordsets intended for editing?
    ...add some more detail of your requirement and someone will suggest something.

    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2006
    The query is displayed in a form in a "query form". After the user selects the parameters, a button is pressed and then the query is displayed in the details of the forms. I've tried using two forms than combining them in one form, but i cannot do the comparison of each entry from each form from the queries, so i've decided on a different path. I intend to have the user specify the set of criterias at once than using the parameters to make the query. So what are my options? I've spent hours so far playing around, and still no luck.
    Last edited by truelaostha; 07-04-06 at 15:24.

Posting Permissions

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