Solved

# SQL Query, last price paid paid by customers

Posted on 2006-05-19
588 Views
I need to know the last price paid by customers for every specific PART_ID  that they ordered in the last 365 days.  Below are the 2 tables where I get the data from.

TBL :customer_order
ID
order_date
sales_rep_ID

TBL: customer_order_line
customer_order_ID
line_no
part_ID
unit_price
0
Question by:gringotani

LVL 44

Expert Comment

Do you not have a Customers table?  And a Customer Foreign Key in your Order table?
0

LVL 44

Expert Comment

Or do you not care about the customers - just the data of the Parts (rather than per customer)?

Does your [customer_order_line] table at least have a Primary Key of it's own?
Is that line_no?  Is it an ascending (auto) number?
0

Author Comment

Customer_order_line table does have the primairy key line #.  I dont see how that is relevenat?  Here I just want to kwo tha last  price a customer paid for a specific PART_ID.  SO if a customer ordered the same pn on 3 different dates, I just want to know the last price from th elast customer order
0

LVL 44

Expert Comment

SELECT COL.[part_ID], COL.[unit_price]
FROM ([customer_order] CO
INNER JOIN [customer_order_line] COL ON CO.ID = COL.[customer_order_ID])
INNER JOIN
(SELECT COL2.[part_ID], Max([order_date]) AS MaxDate FROM [customer_order] CO2 INNER JOIN [customer_order_line] COL2 ON CO2.ID = COL2.[customer_order_ID] WHERE CO2.[order_date] >= Date() - 365 GROUP BY COL2.[part_ID]) Temp
ON COL.[part_ID] = Temp.[part_ID] AND CO.[order_date] = Temp.[MaxDate]
0

LVL 44

Expert Comment

Try this:

SELECT a.ID, b.Part_id, Last(b.unit_price) FROM customer_order AS a INNER JOIN customer_order_line AS b WHERE
a.order_date BETWEEN Date()-365 and Date()
GROUP BY a.ID b.Part_id;
0

Author Comment

I tried to redo ur query with my table names.  and got an error message. I raised the points of this question to 150

error message:
Syntax error (missing operator) in query expression 'SYSADM_CUSTOMER_ORDER_LINE.[PART_ID]
0

LVL 42

Expert Comment

Don't know if it gives the right answer, but this fixes several syntax problems and adds the necessary order by clause so that "Last" is meaningful:

SELECT line.[PART_ID], Last(line.[UNIT_PRICE])
on order.ID = line.ID
WHERE order.ORDER_DATE > Date()-365
GROUP BY  line.PART_ID
order by order_date desc
0

LVL 42

Expert Comment

Slight correction on one of the column names:

SELECT line.[PART_ID], Last(line.[UNIT_PRICE])
on order.ID = line.customer_order_ID
WHERE order.ORDER_DATE > Date()-365
GROUP BY  line.PART_ID
order by order_date desc

But you said>
Customer_order_line table does have the primairy key line #.  I dont see how that is relevenat?  Here I just want to kwo tha last  price a customer paid for a specific PART_ID.  SO if a customer ordered the same pn on 3 different dates, I just want to know the last price from th elast customer order

The problem is that the above SQL and all other suggested SQL works across customers.  In other words, they try to give the last price paid by all customers.  To get an answer customer-by-customer, then we need a customer ID in the order table. Thus, LPurvis's first reply:

Do you not have a Customers table?  And a Customer Foreign Key in your Order table?

0

Author Comment

I am getting the error message "SYNTAX ERROR IN FROM CLAUSE".   and the word "order" is highlighted after I close the error message dialog box  (see the arrow)

l
l
v
0

LVL 44

Expert Comment

Have you even _tried_ my suggestion??
0

LVL 58

Expert Comment

Hello gringotani

ORDER is a reserved word. You should choose another alias.

SELECT
SCOL.PART_ID,
Last(SCOL.UNIT_PRICE) AS ANY_PRICE
ON SCO.ID = SCOL.CUSTOMER_ORDER_ID
WHERE SCO.ORDER_DATE BETWEEN Date()-365 and Date()
GROUP BY SCOL.PART_ID;

However, you cannot add an "ORDER BY" clause on the date, which would – if it were allowed – make this query meaningful. As it stands, the "Last(unit_price)" will not return the "latest" unit price, but the last in any arbitrary sort order.

At first sight, this query could in fact produce the same result as Leigh's exact answer {http:#16722672}, but that would be sheer luck, depending on the order in which the data has been entered, the choice of key fields, the last database compact, and some other factors, among which the answers to:
- Are several orders allowed on the same date?
- Are several customer_order_line's allowed for the same part_id?

Finally, the "by customers" element of the requisite has been dropped, lacking any customer-related fields.

(°v°)
0

Author Comment

I am getting the following error message:
"microsoft jet database engine cant find the input table or query 'SYSADM_customer_order_line'.  Make sure it exists and that it is spelled correctly"

plz help.  I am clueless
0

LVL 58

Accepted Solution

gringotani,

Do you have a "table or query [called] 'SYSADM_customer_order_line'"?

The name is from your own comment:
{http:#16722909} "I tried to redo ur query with my table names..."

(°v°)
0

LVL 58

Expert Comment

so be it, although it should have been LPurvis' {http:#16722672}, imho
good luck...
(°v°)
0

LVL 44

Expert Comment

Hey Markus.
Not to worry (but thanks for the mention :-).
Been an experience this one...

You might want to pop and have a look at it - as a (hopefully) reasonably robust answer to the question.
0

Author Comment

LPurvis,  I am sorry, I am very new to tis and barely know what I am doing. will gladly give u points, if u can tell me how.  Do u want me to post a null question 4u to answer?
0

LVL 44

Expert Comment

Hi gringotani
Like I said to Markus - not to worry.  I've no concerns about the points.
(I am curious though - did you just not see it, or the subsequent posts asking if you'd seen it? :-)

The PAQ is important, but if we just leave this (much as it was) pointing to the suggestion in question then future readers should find it and decide for themselves.

Have you been able to try it for yourself - and determine if it's effective?
0

## Featured Post

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…