Find Min and Max

[adrianvasile]

I have a huge table that represents orders that a SM puts into our system. basically, it lists each account that the SM visited during a day with address, phone etc, date of order, time of order and what was order - a record is generated for each product ordered. So, normally I have about 50 records for each account that the SM visits. What I would like to do is get the first account visited and the last account. I tried to use the Group By but no luck.
ANy help will be appreciated.
Thank you.

 

[PHV]

What is your table layout ?
What is your actual SQL code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adrianvasile]

Well I have 2 tables: one called ORDERS that includes all orders for each account with all the details an dthe second one called BRATT which includes account information such as address, phone etc.
I can create a querry based on ORDERS that will list me the minimum for each account and SM for a day but then I need to use that querry again with BRATT table so that I can get a final querry which will list me the account info also.
So basically I will get the first stop from the ORDERS table and based on the account number from that table I will use that with BRATT table to get address information.
This is where I get stuck.

 

[PHV]

Again, what is your actual SQL code ?

 

[adrianvasile]

I am using Design View to create the querry but this is what I have if I switch to SWQL view:
- for the first query : SELECT ORDERS.finaldate, Min(ORDERS.FinalTime) AS MinOfFinalTime, ORDERS.[ORSM#]
FROM ORDERS
GROUP BY ORDERS.finaldate, ORDERS.[ORSM#];
- for the second query (qrygetMin): SELECT qryGetMin.finaldate, qryGetMin.MinOfFinalTime, qryGetMin.[ORSM#], VIPDTAB_BRATT.CMACCT, VIPDTAB_BRATT.CMDBA, VIPDTAB_BRATT.CMSTR
FROM qryGetMin INNER JOIN VIPDTAB_BRATT ON qryGetMin.[ORSM#] = VIPDTAB_BRATT.CMBSM1
WHERE (((qryGetMin.finaldate)=#1/15/2009#));

I do get the right time but it will list too many accounts. Normally i would like to list only the account information for which the SM number is the same.