Help -- Query pbm

[sd110404]

Hi All,

I have the following tables:

Order (table)
-------------
OrderID OrderDate
O001 10/10/2004
O002 10/11/2004

Status (Table)
--------------
SID OrderID ReferID Date
1 O001 R3 10/10/2004
2 O001 R5 10/10/2004

Refer (Table)
-------------
ReferID ReferName ReferSort
R1 Quot 1
R2 Cancel 2
R3 Workorder 3
R4 Invoice 4
R5 Jobcomplete 5

And so on....

I want to have a query which will retreive the following:

orderID OrderDate StatusID ReferenceName
O001 10/10/2004 2 JobComplete

As you can see For orderID -> O001 , there are two statusID in status table, BUt i need to get the statusID based on the ReferSort.
eg: for orderID O001, R3 and R5 are two ReferID (see in status table), and there ReferSort is 3 and 5 (see in refer table), i need to get the max (ie 5).

Hope i was descriptive. Please if anyone could tell me how to do it, i will be really thankful.
thanks.

 

[GingerR]

First you have to get the MAX Refer Sort for each OrderID.
So make a query with tables Status and Refer, joined on ReferID. Make it a TOTALS query (from the menu pick VIEW+TOTALS). Bring OrderID and ReferSort into the query grid. Under ReferSort, change GROUPBY to MAX. Run the query. Now you have the max ReferSort for each OrderID.

Next, use this query to create your final query. Use this info and see how far you can get. Let us know if you need more help!

g

 

[sd110404]

Thanks Ginger for your reply.

Initially i had wrtten a query with Status and Refer table and was trying to link that with Order table. And wanted to call in my main form. But I was hviong some pbm with the below query.

Select Status.OrderID, Status.Date, Max(Refer.ReferSort) AS MaxReferSort from Refer Inner Join Status On Refer.ReferID = Status.ReferID

I am supposed to have it this way,
Status.OrderID Status.Date MaxReferSort
------------- ----------- -------------
O001 10/10/2004 5

But i dont know whether there is an error in the query, I am getting all the records...
eg:

Status.OrderID Status.Date MaxReferSort
------------- ----------- -------------
O001 10/10/2004 5
O001 10/10/2004 3

I dont know where i am wrong.

 

[GingerR]

when i tried to run your query i got an error:

You tried to execute a query that does not include the specified expression "OrderID" as part of an aggregate function.

You are missing any GROUPBY. Did you do as I suggested?

You also shouldn't group on DATE or else you'll get multiple records too.

Try again and let me know how it goes.

 

[sd110404]

I am really sorry, in my previous SQL i had forgot to put the Group By.

But thanks Ginger, I took out Date (as u said) and my query works great. And Now I got what I was initially trying to do.

Thanks a Lot for your time and patience.