Record Plus record in access query

  • Thread starter Thread starter Xuehong
  • Start date Start date
X

Xuehong

In My query, there are record

Dates Model Line Qty
2008/9/1 A LA 8
2008/9/2 A LA 2
2008/9/3 A LA 3
2008/9/4 A LA 5

I want to result to be:
Dates Model Line Qty
2008/9/1 A LA 8
2008/9/2 A LA 10
2008/9/3 A LA 13
2008/9/4 A LA 18

Means, for the records, if model and line is the same, Sum all the qty when
the date is <= current record's date,
Thank you,
 
i resolved it and want to have a sharing,
if my query name is Qrymain, then copy the query to another one qrymain1
then use this sentence:

SELECT Qrymain.*, (select sum(qty) from Qrymain where
Qrymain1.dates>=Qrymain.dates and Qrymain1.model=Qrymain.model and
Qrymain1.line=Qrymain.line) AS Sumaccqty
FROM Qrymain1;
 
There is no real reason to make a copy of the query. You can use two
instances of one query - simply assign a new name (alias) to the second
instance of the query.

SELECT QryMain.*
, (SELECT Sum(qty) FROM QryMain as Temp
Temp.Dates <= QryMain.Dates And
Temp.Model = QryMain.Model And
Temp.Line = qryMain.Line) as SumAccQty
FROM QryMain

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Hi, John,
I have another question,if i do like this, then when i use this query to
generate a crosstab query,it will pop up error,".....doesn't recognize the
qrymain.dates as a valid field name......", so could you point me what can i
do if i want to geneate a crosstab base on such queries?
 
Please copy and post the SQL of your non-working query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Hi, John, first, thanks for your concern,
my first query sql is (which works well),named qryaccmain:

SELECT qrymain.*, (select sum(actqty) from qrymain as temp where
temp.dates<=qrymain.dates and temp.line=qrymain.line and
temp.project=qrymain.project) AS accqty
FROM qrymain;

when i use this query to generate a crosstab, the error pop up,the crosstab
sql as below:

TRANSFORM Sum(qryaccmain.accqty) AS SumOfaccqty
SELECT qryaccmain.Line, qryaccmain.Project
FROM qryaccmain
GROUP BY qryaccmain.Line, qryaccmain.Project
PIVOT qryaccmain.Dates;
 
Strange.

As a test, I guess I would try listing the required field names in the select
clause of qryAccMain.

SELECT qryMain.Line, qryMain.Dates, qryMain.Project
, (select sum(actqty) from qrymain as temp where
temp.dates<=qrymain.dates and temp.line=qrymain.line and
temp.project=qrymain.project) AS accqty
FROM qrymain;

What is in qryMain? Does it use any parameters?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
No, Never used, it is only a summary query base on a simple table, i think
the problem is that we use a where condition "where
temp.dates<=qrymain.dates and temp.line=qrymain.line and
temp.project=qrymain.project", if i remove this condition it works, but the result is not what i want,
Click to expand...

Thanks,
 
Back
Top