Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2017
    Posts
    10
    Provided Answers: 1

    Answered: Pulling Lat months Totals

    Hi all
    I have a Table containing all Orders placed by coustomers in Access.
    I'm, trying to create a query that would show the customer, the totals for this Month's orders and the total orders from the previous month.
    I have succeeded Quering the this Month's Totals but not the previous Month's
    Customer --- ThisMonthtotal --- Previous Month's total.
    Thanks in Advance.

  2. Best Answer
    Posted by Lava_River

    "Found a Solution here is the new Query
    Code:
    SELECT Reading_tbl.rdID, Format([Reading_Date],"mmm-dd-yy") AS ReadingDate, Customer_qry.[Meter#] AS Account_Code, Customer_qry.plzName AS Plaz, Customer_qry.cusName AS Account, Customer_qry.CustShort AS [Short], Customer_qry.UNIT, Customer_qry.[Meter#], Customer_qry.Meter_Size AS [Meter Size], (SELECT TOP 1 Dupe.Reading                 
     FROM Reading_tbl AS Dupe                     
     WHERE Dupe.Customer = Reading_tbl.Customer
       AND Dupe.Reading_Date < Reading_tbl.Reading_Date) AS Previous, Reading_tbl.Reading AS Current_Reading, MAX.MaxOfReading AS MaxReading, Min.MinOfReading AS MinReading
    FROM ([Min] RIGHT JOIN ([MAX] RIGHT JOIN Reading_tbl ON MAX.Customer = Reading_tbl.Customer) ON Min.Customer = Reading_tbl.Customer) LEFT JOIN Customer_qry ON Reading_tbl.Customer = Customer_qry.cusName
    WHERE (((Format([Reading_Date],"m/yy"))=[Month/Year]))
    ORDER BY Format([Reading_Date],"mmm-dd-yy") DESC;
    "


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    make a form, with a customer combo box.
    2 text boxes, txtStartDate, txtEndDate
    2 text boxes, txtPrevStart, txtPrevEnd

    make a query to pull current data, qsCurrentMonth1Customer
    select customer,Sum(amt), forms!myForm!txtStartDate as 'Current Month' from table where [customer] = forms!myForm!cboCust and [Date] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

    make a query to pull last month data, qsLastMonth1Customer
    select customer,Sum(amt), forms!myForm!txtStartDate as 'Prev Month' table where [customer] = forms!myForm!cboCust and [Date] between forms!myForm!txtPrevStart and forms!myForm!txtPrevEnd

    then put both together in a union query :
    select * from qsCurrentMonth1Customer
    union
    select * from qsLastMonth1Customer


    (with some vb, all dates can be auto filled by just picking a month from a combo box.)
    Last edited by ranman256; 02-09-17 at 06:09.

  4. #3
    Join Date
    Feb 2017
    Posts
    10
    Provided Answers: 1

    subquery

    I was Thinking more like a subquery instead o adding forms.
    When the user runs the query it asks for the user to enter the month/year. So I would like to use this input substruck one month and use it in a subquery, all my atempts failed.
    This is what I already have running.
    Code:
    SELECT Reading_tbl.Reading_Date, PLAZA.plzName AS Plaz, Customer.[Meter#] AS [Account Code], Customer.cusName AS Account, Customer.UNIT, Customer.CustShort, Customer.[Meter#], Customer.Meter_Size, Reading_tbl.Reading AS [Current], MAX.MaxOfReading AS [MAX], Min.MinOfReading AS [MIN], Reading_qry.Reading, Format([Reading_Date],"m/yy") AS mnth
    FROM ((((PLAZA LEFT JOIN Customer ON PLAZA.plzID = Customer.CustPlaza) LEFT JOIN Reading_tbl ON Customer.cusName = Reading_tbl.Customer) LEFT JOIN [MAX] ON Customer.custID = MAX.custID) LEFT JOIN [Min] ON Customer.custID = Min.custID) LEFT JOIN Reading_qry ON Customer.cusName = Reading_qry.Account
    WHERE (((Format([Reading_Date],"m/yy"))=[Month / Year]));

  5. #4
    Join Date
    Feb 2017
    Posts
    10
    Provided Answers: 1
    Found a Solution here is the new Query
    Code:
    SELECT Reading_tbl.rdID, Format([Reading_Date],"mmm-dd-yy") AS ReadingDate, Customer_qry.[Meter#] AS Account_Code, Customer_qry.plzName AS Plaz, Customer_qry.cusName AS Account, Customer_qry.CustShort AS [Short], Customer_qry.UNIT, Customer_qry.[Meter#], Customer_qry.Meter_Size AS [Meter Size], (SELECT TOP 1 Dupe.Reading                 
     FROM Reading_tbl AS Dupe                     
     WHERE Dupe.Customer = Reading_tbl.Customer
       AND Dupe.Reading_Date < Reading_tbl.Reading_Date) AS Previous, Reading_tbl.Reading AS Current_Reading, MAX.MaxOfReading AS MaxReading, Min.MinOfReading AS MinReading
    FROM ([Min] RIGHT JOIN ([MAX] RIGHT JOIN Reading_tbl ON MAX.Customer = Reading_tbl.Customer) ON Min.Customer = Reading_tbl.Customer) LEFT JOIN Customer_qry ON Reading_tbl.Customer = Customer_qry.cusName
    WHERE (((Format([Reading_Date],"m/yy"))=[Month/Year]))
    ORDER BY Format([Reading_Date],"mmm-dd-yy") DESC;

Posting Permissions

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