SQL query for MS access database

[ankur_bhardwaj]

Hi,




I need a SQL query for below mentioned data. The scenario is :


I have two different path values of my product. one is from AC-ACAAAA and other one is from AD-ADAAAAAA. Based on that


First i want to find max value of QTY and then want to multiply multiply max value of QTY with every value of Rate in a new column. Then i want to take average of new column.

P.S. I want collective data between AL and SL in both paths

ITEM	LOCATION	PATH	PARENT	Category	ORIGIN	QTY	Rate
935306	LB00	AC	935306	AL	TW10	1	0.98
935306	LB00	ACA	340000	BP	0010036324@TW70	1	1
935306	LB00	ACAA	340000	CE	TW70	5025	0.93
935306	LB00	ACAAA	340000	SL	0010036324@TW70	1	1
935306	LB00	ACAAAA	340000	SL	TW70	1	1
935306	LB00	AD	935306	SL	E028	1	1
935306	LB00	ADA	340000	AL	TW10	1	0.98
935306	LB00	ADAA	340000	IC	TW70	1	0.94
935306	LB00	ADAAA	340000	CE	0010036324@TW70	1	1
935306	LB00	ADAAAA	340000	CE	TW70	1	1
935306	LB00	ADAAAAA	340000	PT	TW70	5025	0.96
935306	LB00	ADAAAAAA	340000	SL	E28	1	1

[ankur_bhardwaj]

@orange kindly help me with this

[orange]

???? I don't understand the request. Need vry specific instructions and sample data.
What is/are the tables involved?

[ankur_bhardwaj]

???? I don't understand the request. Need vry specific instructions and sample data.
What is/are the tables involved?

So here it is- I have a table in which there is two sets of data. As you can see from image, one data set is from AC to ACAAAAA and another one is from AD to ADAAAAAA(Path column). I want to calculate Max value of QTY according to ITEM field. As you can see from image it will be 5026. Once i get the max value of Qty, i want to multiply it with every value of rate column.
I tried something like this


Select
ITEM,
Location,
PATH,
PARENT,
CATEGORY,
origin,
(Produced*Rate) as EFF_Rate
(
SELECT ITEM, max(QTY) as ProducedQty
FROM Sheet1
Group By ITEM) as Produced
From
Sheet1

but i am getting an error
You have written a subquery that can return more than one field without using EXISTS reserved word in the Main query's FROM clause. Revise the SELECT statement of the subquery to request only one column.
I am newbie in SQL, so any help is highly appreciated

[orange]

I just mocked up a few records because you posted a graphic and I had to type a few records.

Your table was sheet1, mine is Ankur

This is the sql I tried

SELECT Ankur.ITEM
    ,Ankur.Location
    ,Ankur.PATH
    ,Ankur.PARENT
    ,Ankur.CATEGORY
    ,Ankur.origin
    ,Ankur.qty
    ,Ankur.rate
    ,(Produced * Rate) AS EFF_Rate
    ,(
        SELECT max(QTY) AS ProducedQty
        FROM ankur AS B
        GROUP BY ITEM
        ) AS Produced
FROM Ankur;

Here is my input:

item,location,path,parent, category, origin,qty, rate
935306,LB00,AC,935306,AL,TW10,1,.9811
935306,LB00,ACA,340000,BP,6324@TW70,1,1
935306,LB00,ACAA,340000,CE,TW70,5026, .9278

Here is the query result

ITEM	Location	PATH	PARENT	CATEGORY	origin	qty	rate	EFF_Rate	Produced
935306	LB00	AC	935306	AL	TW10	1	0.9811	4931.0086	5026
935306	LB00	ACA	340000	BP	6324@TW70	1	1	5026	5026
935306	LB00	ACAA	340000	CE	TW70	5026	0.9278	4663.1228	5026

[ankur_bhardwaj]

@orange

Thanks for your response. It works ! I have one more query. I want one more column which will provide me running multiplication. For example as per result of your query dataset:

For example if i multiply Max(Qty) i.e. 5026 by Rate it will give me 4931.0086. Now i want to multiply 4931.0086 with 1 (i.e. 2nd row value of rate field) and so on.

I just mocked up a few records because you posted a graphic and I had to type a few records.

Your table was sheet1, mine is Ankur

This is the sql I tried

SELECT Ankur.ITEM
    ,Ankur.Location
    ,Ankur.PATH
    ,Ankur.PARENT
    ,Ankur.CATEGORY
    ,Ankur.origin
    ,Ankur.qty
    ,Ankur.rate
    ,(Produced * Rate) AS EFF_Rate
    ,(
        SELECT max(QTY) AS ProducedQty
        FROM ankur AS B
        GROUP BY ITEM
        ) AS Produced
FROM Ankur;

Here is my input:

item,location,path,parent, category, origin,qty, rate
935306,LB00,AC,935306,AL,TW10,1,.9811
935306,LB00,ACA,340000,BP,6324@TW70,1,1
935306,LB00,ACAA,340000,CE,TW70,5026, .9278

Here is the query result

ITEM	Location	PATH	PARENT	CATEGORY	origin	qty	rate	EFF_Rate	Produced
935306	LB00	AC	935306	AL	TW10	1	0.9811	4931.0086	5026
935306	LB00	ACA	340000	BP	6324@TW70	1	1	5026	5026
935306	LB00	ACAA	340000	CE	TW70	5026	0.9278	4663.1228	5026