Help Please Structuring a Subquery

[M_S_Jones]

Hello all,

I'm trying to build a query that sounds simple in concept, but I'm having problems getting my head around it and would like some advice. I have a table that stores stock transactions and so has records that consist of a transaction date, the product's ID, the quantity and transaction type. There are two particular transaction types that I am trying to work with. These are when a product is moved to and from our factory floor. This is simple enough as these two transaction types each have a unique ID, and so I have easily produced a query that shows a list of every one of these transactions within a specified date range and is sorted by the product name and then the date. So it looks something like:

Product 1 10/05/2009 Issued to Factory Floor
Product 1 11/05/2009 Returned from Factory Floor
Product 2 11/05/2009 Issued to Factory Floor
etc.

There is one last attribute that I would like to display with these values, and it is the difficult part, the quantity in stock at the time. This can be calculated from the other transactions in the table. Each transaction record has a received quantity field and an adjusted quantity field, only one of which will be in use in any given record (not the best design ever, I know.) So to calculate the stock quantity for any given date, I simply run a query over all of the data where the transaction date is <= the given date, group by product number and sum the received quantity and the adjusted quantity. I can then place another query over the top to add the two summed values together and I have a snapshot of how much stock we had of any given product on my specified date.

However, I am having problems linking this kind of structure to the above query. It would mean that the two queries (one to group and sum, the other to add the results,) would need to be run for each row in the query results, to give the stock quantity of the given product, on that transaction date. I have tried writing a subquery to go into a new field on the first query, but I'm not sure how I would do so, I want to select records from the table where the transaction date and product ID match that of the current record, group and sum them, and then add the results of the sum together. I'm really struggling with where to start on the syntax of this, any help would be greatly appreciated.

Thanks,

Matthew

P.S. This is the SQL of the grouping and summing query, if I could link this in as a subquery I think I would be onto the right track:

SELECT tabStock_Transaction.Ticket_ID, Sum(tabStock_Transaction.Received_Qty_Mtrs) AS SumOfReceived_Qty_Mtrs, Sum(tabStock_Transaction.Adjusted_Qty_Mtrs) AS SumOfAdjusted_Qty_Mtrs
FROM tabStock_Transaction
WHERE (((tabStock_Transaction.Transaction_Date)<=#9/29/2008#) AND ((tabStock_Transaction.Trans_Error)<>-1) AND ((tabStock_Transaction.Cancel)<>-1))
GROUP BY tabStock_Transaction.Ticket_ID;

But even to link these two fields (received and adjusted quantities) separately to my query, what sytax would I use? I actually want to use this query as a data source, not just for criteria. I have used subqueries before where I use another select query in the where clause, but I want the above in the from clause really.

 

[namliam]

Just drag and drop in the query design???

You can add queries to the design window just as you do tables

 

[M_S_Jones]

Thanks for your reply Mailman. I can see what you are suggesting but even that brings problems, I need to enter the transaction date of my main query as part of the criteria in the summing query in order to get the sum of the transactions up until that date. As my summing query is a totals query, it won't let me show the transaction date unless I group by it (I tried displaying it with a where total, but it generated an error,) so I can't add it to the criteria of the main query in design view. It's a start though, since I can link product IDs now and get totals for a date specified in the summing query and link it to my main query's product ID, even though the transaction dates are independent of each other.

There must be a way of doing this, surely running totals of stock over a range of dates is a common requirement? Short of generating a temporary table and populating it with the calculated stock of every product on every date within the user-specified date range, and then looking these up for each line in my main query, I'm not sure how to do it.

 

[namliam]

I am not quite understanding...

Are you putting a constraint on a different date than you are showing??
If it is the same date, there shouldnt be a problem.

If all else fails, drop back to basics...
Make Query 1
Limit this on what ever you want your query to be limited....

Make query 2
Your Totals query based on query 1

Have your original query and make query 3
Where you join up your Original and Query 2.

Doubt that would be necesarry but... if all else fails... drop down to basics.

 

[M_S_Jones]

Good morning Mailman,

Thank you for your post. The solution that you have suggested is what I tried initially. My problem is that in my first query, let's call it Query 1, which is the one that I gave the following example of:


Product 1 10/05/2009 Issued to Factory Floor
Product 1 11/05/2009 Returned from Factory Floor
Product 2 11/05/2009 Issued to Factory Floor

has a date for each transaction. Now for each of the above transactions, I need a stock quantity at the time of the transaction, which involves running another query (Query 2,) to get the sum of the quantities of all of the transactions for each product at its transaction date. So in the above example, for the first line, I need to run Query 2 to get the sum of the transactions for Product 1 up until and including the 10/05/2009. That will be the stock quantity at the time of the first of the above lines. I will then need to run Query two again for the second line of Query 1's results, the sum of all of Product 1's transactions which have transaction dates up until and including the 11/05/2009. I will then need to do the same for Product 2 up until and including the 11/05/2009 and so on for each of the results lines of Query 1.

I can link the product ID of Query 2 to that of Query 1, but I can link the transaction dates, because Query 2 is a totals query. I have set the transaction date field of Query 2 to be a 'Where' field, which means that I cannot make it visible in the results, and so I cannot add it to Query 1 in query builder. This means that the criteria of Query 2's transaction date can be hard coded, so that I can for example specify where transacation date is <=10/05/2009, and link the Product IDs of Query 1 and Query 2. This will allow me to display the stock of each product on the 10/05/2009, which doesn't help me for the third line of my example, as that requires the stock on the 11/05/2009. The criteria of Query 2 therefore depends upon the results of Query 1, which needs to get its quantities from Query 2.

Does that make my problem clearer? Sorry, it makes perfect sense in my head!

Regards,

Matthew

 

[namliam]

Aha!

What you are looking for is a "running sum", this is kindoff hard to do in access but possible.

have a search around for "running sum" and see if that gets you started...

 

[M_S_Jones]

Okay, thanks for the advice Mailman, I'll look that up and post back how I get on.

Thanks again.

 

[M_S_Jones]

Thank you very much Mailman, I've used a DSUM (it's a little slow but this isn't a query that will be run frequently.) It all works fine. I had a few teething problems with date formats, but I just specified "dd-mmm-yyyy" to bring some text in to get it to link to the American one. Thank you so much for all of your help.

Matthew