Get last sold price for item

[Ashfaque]

Hi,

I have 2 tbls called T_SOHeader & T_SOFooter1 with one to many relationship which is InvNum.

My main invoice form called "F_SOHeader" with its footer form both are based on the above 2 tbls.

In footer form I am calling product info by inputing productcode in a combo. Till here everything is working smoothly. (Temporary I am displaying custcode on header form)

What I am looking for is:

I want to display last (latest) price in a text box called TxtOldPrice. If the previous record is avalable for this customer for the selected item entered in T_SOFooter1, it would display in this TxtOldPrice......just for reference only.

Therefore I wrote below :

......
.........

Set rst1 = CurrentDb.OpenRecordset("select T_SOHeader.CustCode,T_SOFooter1.productcode, " & _
"T_SOFooter1.Max(InvNum), T_SOFooter1.salesitemprice " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum = T_SOFooter1.InvNum " & _
"Where T_SOHeader.CustCode='" & Forms!F_SOHeader!CustCode & "'")

If rst1.EOF And rst1.BOF Then
Me.TxtOldPrice = 0

Else
.....
.......

But gives run-time error 3079.

The specified fild InvNum could refer to more than one table listed in the FROM clause in your SQL statement.

Can someone help me please...

Thanks,

 

[jdraw]

You may want to store a DateOfSale with each item sold and price.

You can get the latest Date and from that the latest Price.

Just a thought.

 

[Ashfaque]

You are correct JDraw. I have already stored date in header table (SODate). This field I can add into sql statement.

How abour mentioned error?

Regards,
Ashfaque

 

[pbaldy]

The error is quite descriptive in this case. The same field exists in both tables, so you have to include the table in your Max function. You'll then run into the aggregate functions error I suspect, and my gut is this won't return what you want anyway. Does this help?

http://www.baldyweb.com/LastValue.htm

 

[Ashfaque]

Thanks pbaldy,

Your idea is helpful but it as based on one single table and in my case I have 2 diff tables which are related with one similar field called InvNum. Another thing, the latest invoice date (SODate) is entered in header table only while the salesitemprice are in footer table.

Any other thought.

Ashfaque

 

[jdraw]

Please post a jpg of your tables and relationships.

 

[pbaldy]

It's the concept I think you need. First get the latest date/invoice, then the associated price. Did you add the table identifier?

 

[Ashfaque]

Ha...Ha.. I never used tbl identifer....even I didnt go that side....

 

[Ashfaque]

Hi,

Attached is jpg of tbl relationship....

And off course, if the item didnt sell to that customer previously, it would bring as 0 to the text box which I can manage.

Thanks,
Ashfaque

 

[Ashfaque]

Hi,

I have attached my invoice form (header and footer form) for you to give a very clear idea....

I hope someone will surely help as always.

Thanks,
Ashfaque

 

[Ashfaque]

Hello,

After efforts below code is returning price :

Set rst1 = CurrentDb.OpenRecordset("Select Max(T_SOHeader.SODate) AS MaxDate, T_SOHeader.CustCode, " & _
"Max(T_SOHeader.InvNum) AS MaxInv, T_SOFooter1.productcode, T_SOFooter1.salesitemprice " & _
"FROM T_SOHeader LEFT JOIN T_SOFooter1 ON T_SOHeader.InvNum = T_SOFooter1.InvNum " & _
"Where T_SOHeader.CustCode =" & Forms!F_SOHeader!CustCode & _
" GROUP BY T_SOHeader.SODate, T_SOHeader.CustCode, T_SOHeader.InvNum, T_SOFooter1.productcode, T_SOFooter1.salesitemprice")

If rst1.EOF And rst1.BOF Then
Me.TxtOldPrice = 0
Else
Me.TxtOldPrice = rst1!SalesItemPrice
End If
.....
........

But it returns first price in the sequence (I mean first record for this customer for prod code 0101) which was 15 for the corresponding customer. But 15 was its old price and later we reduced to 12 and there are enteries of 12 many times in the footer tbl. Therefore it should display 12 and not 15

Can someone help please..

Regards,
Ashfaque

 

[JHB]

How about changing the "LEFT JOIN" to "INNER JOIN"?

 

[Ashfaque]

I tried with INNER JOIN but it is returning same value (I mean 15)

Regards,
Ashfaque

 

[pbaldy]

Run that SQL in a query and see what you get. I don't think it's what you expect.

 

[Ashfaque]

Do you mean to say I need to make normal query and not vba?

 

[pbaldy]

No, I'm saying that SQL is probably not returning what you think it is.

 

[Ashfaque]

SQL is returning first record value (salesprice) from footer table for the desired customer which is on screen while entering data. But I want the last entered price.

There are many entries of sales price for the prodcode 0101 customer 'Trass' and I want the latestly sold price.

Ashfaque

 

[JHB]

Is InvNum a numberfield or a textfield?
Try to do the query simple, try first to get the Max(T_SOHeader.SODate) and see if it is what you expected.

 

[Ashfaque]

1. InvNum is Number field.
2. I tried already with Max(T_SOHeader.SODate) and it returns first transaction date

I think If I whould have stored SODate or CustomerCode field into the footer table also other than InvNum so that I would not have messed up with this logic..

Regards,
Ashfaque

 

[JHB]

Hmm - Max(T_SOHeader.SODate) should return the latest/newest date.
Have you run the query (Max(T_SOHeader.SODate) with the T_SOHeader table only?

I think If I whould have stored SODate or CustomerCode field into the footer table also other than InvNum so that I would not have messed up with this logic..

I do not think it has made ​​any difference. I think when you achieve to get the latest/newest date then you get the correct information from the T_SOFooter1 table.

Another thing, are you sure that "SODate" is a DateTime field in the table, else you'll get the record with highest ASCII number in the 1. character. Example: "31-12-2000" is higher as "01-01-2013".