Difficulty Sorting. Want to keep group together.

[wdbyrd43]

I am having difficulty grouping and sorting the way our bosses want to see our database results. We are a job shop and have multiple 'jobs' (or production releases in reality) for each customer with multiple delivery dates. I want the report to group and show the customers name and then all of the jobs we have for that customer arranged by date. All of this is relatively easy for me and I have completed the task to this point. The difficulty begins because they THEN want it to organize each complete group by date and still keep the entire group together. In short, I want to group by customer then sort by the earliest delivery date from ALL of the entries AND keep the group together. At first it was just reporting it alphabetically by customer so I added the project number to proceed each and it now reports it by project number of course. I am able to group it by date, but then it reports back several entries for the Customer Group. Not what I am needing. I have attached the first page of the report, closest to the format that I can get.



This report comes back sorted by customer number which I would like to be able to delete once I get the group to stay together.
What I need for example from this page is to have it in this order...3602, 3669, 3664, 3663, 3661 so we discuss projects as their deliveries are becoming due but still see that there are additional delieveries in the future.

I should add that I am not proficient in code writing so I will have to use whatever I can point and click on in Access, I am a master of point and click.
Thanks in advance for any help on this......

Pg 1 Report.pdf

[Dal Jeanis]

You want the customers to be placed in order by the lowest delivery date, right?
So, you need to GROUP BY customer, JOB
But ORDER By MIN(DeliveryDate), then CUSTOMER, DeliveryDate, JOB

You may need to JOIN your above query to a query that just looks like this

SELECT Customer, Min(DeliveryDate)
FROM (whaever)
GROUP BY Customer;

If you want to post your current SQL code, (switch to design view, then SQL view, copy the SQL, and paste into a post) then we can help you sort it out.

[wdbyrd43]

Thanks...here is the current query SQL. Let me know what I ned to do from here to solve this.
I tried, unsuccessfully to rewrite the code myself....but like I said, I tried.
Much appreciated.
~Bill


SELECT Input_orders.Work_Order_ID, Input_orders.Customer_Name, Input_orders.Due_Date, Input_orders.Qty, Input_orders.Item_Number, Input_orders.Sales_Order, Input_orders.Item_Description
FROM Input_orders
ORDER BY Input_orders.Customer_Name, Input_orders.Due_Date;

[Dal Jeanis]

Try This:

SELECT 
   T1.CustFirstDue,
   T2.Customer_Name, 
   T2.Due_Date, 
   T2.Work_Order_ID, 
   T2.Qty, 
   T2.Item_Number, 
   T2.Sales_Order, 
   T2.Item_Description
FROM 
   (SELECT Customer_Name, MIN(Due_Date) As CustFirstDue
    FROM Input_orders
    GROUP BY Customer_Name
    ) AS T1
   INNER JOIN 
   Input_orders AS T2
   ON T1.Customer_Name = T2.Customer_Name
ORDER BY 
   T1.CustDueDate, 
   T2.Customer_Name, 
   T2.Due_Date;

This SQL assumes that customer_name uniquely identifies your customers.

PS. In a test version of your database, create a new query, switch to SQL view, and paste the above code into that window. Run it and see what happens. Don't risk messing up your original query or your production database by taking candy from a stranger on the internet.

[wdbyrd43]

THANKS! Worked like a charm. Just a little layout and hiding from the report view and I'm done!