Good Day,
I have read just about everything on how to set up a query for a report for max([date]) but still having trouble.
What I need is to pull all of the orders by CustomerID, OrderID, for a certain date range that do not have an insurance hold on them but can have a balance of less than or more than zero.
There is also a subreport that includes the balance due by 30, 60, 90, etc.
That is where the problem resides. It pulls all orders and lists them by date with the same balance due. Thus if I have an order that was 30 days ago it includes the amount under 30, and if another order was 60 days ago then it prints the same balance due under 60 but of course it totals all and I end up with the wrong amount in total balance due.
Here is the main form:
SELECT DISTINCTROW Customers.CustomerID, ([Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME]) AS Name, Orders.OrderID, Orders.OrderDate, Customers.FIRSTNAME, Customers.MI, Customers.LASTNAME, Customers.ADDRESS1, Customers.ADDRESS2, Customers.Zip_ID, Customers.City_ID, Customers.State_ID, Customers.Doctor_ID, [Receivables Aging Report Query].Insurance_Hold
FROM (Customers INNER JOIN [Receivables Aging Report Query] ON Customers.CustomerID = [Receivables Aging Report Query].CustomerID) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((([Receivables Aging Report Query].Balance)<>0))
GROUP BY Customers.CustomerID, ([Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME]), Orders.OrderID, Orders.OrderDate, Customers.FIRSTNAME, Customers.MI, Customers.LASTNAME, Customers.ADDRESS1, Customers.ADDRESS2, Customers.Zip_ID, Customers.City_ID, Customers.State_ID, Customers.Doctor_ID, [Receivables Aging Report Query].Insurance_Hold
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order Date]) AND (([Receivables Aging Report Query].Insurance_Hold)=No));
and here is the subreport:
SELECT DISTINCTROW Orders.CustomerID, [Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME] AS Name, [total sale]+[sum of tax] AS TotalSale, RecAgingQryBal.[Sum Of Tax], RecAgingQryBal.[Total Sale], RecAgingQryBal.Current, RecAgingQryBal.[31-60 Days], RecAgingQryBal.[61-90 Days], RecAgingQryBal.[91+ Days], RecAgingQryBal.Balance
FROM (Customers INNER JOIN RecAgingQryBal ON Customers.CustomerID = RecAgingQryBal.CustomerID) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID, [Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME], RecAgingQryBal.[Sum Of Tax], RecAgingQryBal.[Total Sale], RecAgingQryBal.Current, RecAgingQryBal.[31-60 Days], RecAgingQryBal.[61-90 Days], RecAgingQryBal.[91+ Days], RecAgingQryBal.Balance;
Thanks to all, your forum has helped me a lot.
I have read just about everything on how to set up a query for a report for max([date]) but still having trouble.
What I need is to pull all of the orders by CustomerID, OrderID, for a certain date range that do not have an insurance hold on them but can have a balance of less than or more than zero.
There is also a subreport that includes the balance due by 30, 60, 90, etc.
That is where the problem resides. It pulls all orders and lists them by date with the same balance due. Thus if I have an order that was 30 days ago it includes the amount under 30, and if another order was 60 days ago then it prints the same balance due under 60 but of course it totals all and I end up with the wrong amount in total balance due.
Here is the main form:
SELECT DISTINCTROW Customers.CustomerID, ([Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME]) AS Name, Orders.OrderID, Orders.OrderDate, Customers.FIRSTNAME, Customers.MI, Customers.LASTNAME, Customers.ADDRESS1, Customers.ADDRESS2, Customers.Zip_ID, Customers.City_ID, Customers.State_ID, Customers.Doctor_ID, [Receivables Aging Report Query].Insurance_Hold
FROM (Customers INNER JOIN [Receivables Aging Report Query] ON Customers.CustomerID = [Receivables Aging Report Query].CustomerID) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE ((([Receivables Aging Report Query].Balance)<>0))
GROUP BY Customers.CustomerID, ([Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME]), Orders.OrderID, Orders.OrderDate, Customers.FIRSTNAME, Customers.MI, Customers.LASTNAME, Customers.ADDRESS1, Customers.ADDRESS2, Customers.Zip_ID, Customers.City_ID, Customers.State_ID, Customers.Doctor_ID, [Receivables Aging Report Query].Insurance_Hold
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order Date]) AND (([Receivables Aging Report Query].Insurance_Hold)=No));
and here is the subreport:
SELECT DISTINCTROW Orders.CustomerID, [Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME] AS Name, [total sale]+[sum of tax] AS TotalSale, RecAgingQryBal.[Sum Of Tax], RecAgingQryBal.[Total Sale], RecAgingQryBal.Current, RecAgingQryBal.[31-60 Days], RecAgingQryBal.[61-90 Days], RecAgingQryBal.[91+ Days], RecAgingQryBal.Balance
FROM (Customers INNER JOIN RecAgingQryBal ON Customers.CustomerID = RecAgingQryBal.CustomerID) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID, [Customers]![FIRSTNAME] & " " & [Customers]![MI] & " " & [Customers]![LASTNAME], RecAgingQryBal.[Sum Of Tax], RecAgingQryBal.[Total Sale], RecAgingQryBal.Current, RecAgingQryBal.[31-60 Days], RecAgingQryBal.[61-90 Days], RecAgingQryBal.[91+ Days], RecAgingQryBal.Balance;
Thanks to all, your forum has helped me a lot.
