result of a query in another query (1 Viewer)

[rythem]

can i use the result of one query to calculate something in another query

 

[The_Doc_Man]

In any place that you could have used a table as a recordsource, you can use a query as a recordsource. Therefore, if your proposed use was have the query become a recordsource, the answer is an unequivocal yes.

If you had some other use in mind, you will have to be more specific.

Queries work in place of tables in the table aggregate functions such as DSum, DLookup, DCount, DMax, DMin, etc. etc.

Queries can be recordsources for forms, reports, and datasheet views.

Queries can be recordsources for summation queries, union queries, maketable queries, etc.

The only time queries DON'T work the same is if there is something that cannot be tied back to a unique record or a unique combination of JOINed records when you want to do an update. (Can't attempt to do ambiguous updates or deletes.) This happens most often with summation queries but can happen in other ways too.

 

[rythem]

This is what i am trying

below is something that i am trying to do. I am not too sure if thats the best way or if there is a better or a simpler way to do it.

I have an inventory table with the fields such as stock quantity, threshold quantity. I have the requisition table with the fields such as quantity (this is the quantity that is requisitioned).
I have a report called the check stock report. The check stock report displays the difference between the stock quantity (inventory) and the quantity (requisition). This is done dynamically using a query called the check stock query.

Now I want to be able to use the result of the check stock query (that gives the updated or available quantity) and check this number against the threshold quantity from the inventory table. The available quantities of all the make/models below the threshold quantity must be displayed on the report. If there are none below their specified threshold then the report must not display anything.

i am trying to write a code under the reorder report_load event. but it does not seem to be working and gives me the same output as it would if i dont use the second query.

It would be great if you could give me any suggestion on this

 

[vengsiva]

Checking for threshold

Rythem
Please whether the attached db serves your purpose.As subquery is used it may be slow
Vengsiva

 

[rythem]

Question for the query

Hello,

Thankyou so much, thats what i exactly wanted.

In the query called Query1, where did the table a coem from. I cannot figure that out.

Please let me know

thanks

 

[vengsiva]

Using alias

Hi.In the query design view right click the table and in properties give the alias "a" for the table
vengsiva

 

[rythem]

Query Result

Hi,

Thanks a lot for the help.

These queries gives me the exact result that i wanted. They also give me individual entries for each transaction like multiple "Issues" and multiple "Receipts".
so when i use this query to display the report, it gives me multiple values of the transaction. I just want to be able to display the latest stock status based on the date. i know

Every time the quantity is below the threshold, it will display each instance of change in inventory after the quantity has reached the threshold. But i just want to display the latest status.

i tried writing a code but it does not seem to be working.

Thanks again for the help

 

[vengsiva]

Setting criteria or filter

Hello
Have you tried inserting the criteria DtTransaction=date in query1 and then running qrygetthreshold or setting the filter to dmax("dtTansaction","tbltransactions) for the report?
vengsiva

 

[rythem]

Date with product ID

Hi,

I did try doing this the output gives me the records for the date that fall under the condition.

I want to do it for each product id.
In my report, i need the most recent date transaction FOR each Product ID.

Hope you can tell me how do i do this
Thanks !!!

 

[vengsiva]

Last date transaction for each product

Hello rythem
Define a public function thus
Public Function DaysElapsed(TDate As Date)
'********************************************************************
'
' Comment: Get Days elapsed between transactiondate and Lastdate Transaction for each Productid
'
'********************************************************************

DaysElapsed = DateDiff("d", DMax("DtTransaction", "tbltransactions"), [TDate])
End Function

Include this as a field in query1 set the criteria for this field as 0.The SQL of the query would look like this
SELECT a.ProductId, a.DtTransaction, a.TransNature, a.Quantity, (Select sum(quantity)from tbltransactions where ProductId=a.Productid and DtTransaction<=a.DtTransaction and Transnature='Receipt') AS TotReceived, (Select sum(quantity)from tbltransactions where ProductId=a.Productid and DtTransaction<=a.DtTransaction and Transnature='Issue') AS TotIssue, nz([TotReceived]-[TotIssue],[TotReceived]) AS Balance, DaysElapsed([DtTransaction]) AS ElapsedDays
FROM tblTransactions AS a
WHERE (((DaysElapsed([DtTransaction]))=0))
ORDER BY a.DtTransaction;


I hope you get the needed output.
If this does not work I have to put up my hands in despair and shout for HELP
vengsiva

 

[vengsiva]

Another solution?

Hello rythem
Since the time of last posting I did some rethinking and came up with another query
SELECT Query1.ProductId, Max(Query1.DtTransaction) AS MaxOfDtTransaction, Last(Query1.Balance) AS LastOfBalance
FROM Query1 INNER JOIN qryGetBelowThreshol ON Query1.ProductId = qryGetBelowThreshol.ProductId
GROUP BY Query1.ProductId;
Hope this solves the problem
vengsiva

 

[rythem]

results

hi,

The query works for the most recent date.
All i need more now is to do it for every product ID and now it works fine.

Thanks a lot, you have been a great help