Report filtering again... (1 Viewer)

Darrell

Registered User.
Local time
Today, 14:17
Joined
Feb 1, 2001
Messages
299
I am trying to do a supply/demand shortages report and as such have tried valiantly to do a running sum calculation in a query but that was waaaaaay too painful and so I have given up and left the query reasonably simple.

On the report that is based on this query, I have a calculated field that specifies the number of days between 'today' and when demand outstrips supply (ie my running total of stock/supply/demand goes negative) inside of the planned delivery time for a given material.

If I run this report as is, it works fine but I now need it to only show me the materials where this is actually the case. 99% of the materials don't go negative inside of the planned delivery time and so I don't care about them.

Any help appreciated

dh
 

vbaInet

AWF VIP
Local time
Today, 14:17
Joined
Jan 22, 2010
Messages
26,374
If I run this report as is, it works fine but I now need it to only show me the materials where this is actually the case.
I'm guessing this is your question, but can you elaborate on what you mean by the highlighted statement.
 

Darrell

Registered User.
Local time
Today, 14:17
Joined
Feb 1, 2001
Messages
299
yeah sorry that wasn't very clear was it...

If the number of days for the planned delivery time for any given material is greater than the number of days where the running total goes negative, the calcuated field shows the number of days between now and when this will occur, otherwise it shows nothing.

What I need to do now is filter the report so that it is only showing me the materials that have a value output for this calculation displayed.
 

vbaInet

AWF VIP
Local time
Today, 14:17
Joined
Jan 22, 2010
Messages
26,374
Move the DateDiff() calculation to the query (as a new column) and put criteria under it.
 

Darrell

Registered User.
Local time
Today, 14:17
Joined
Feb 1, 2001
Messages
299
ok so how do I do a running total in a query...?
 

vbaInet

AWF VIP
Local time
Today, 14:17
Joined
Jan 22, 2010
Messages
26,374
I still don't see how the calculation is affected if you move it to the query. Can I see what you're doing at the moment? For example, I would like to see the SQL statement.
 

Darrell

Registered User.
Local time
Today, 14:17
Joined
Feb 1, 2001
Messages
299
Well I didn't have the running sum in the query because my brain couldn't get it to work.

However... I now have a working report. What I did was...

1. Take the output from my union query into a select query for sorting purposes.
2. Take the output of that into a table to get a unique number
3. Put the table into another query to do the running sum and calculate the datediff etc
4. Use that query and the table together in another query to filter only the records that I need
5. Pass the result to the report.

I'm sure this is probably not the most effective way of doing it but hey... I'm by no means an expert and in the end it works and isn't too slow.

Cheers

dh
 

vbaInet

AWF VIP
Local time
Today, 14:17
Joined
Jan 22, 2010
Messages
26,374
I think you mentioned that you did it before and it was slow. It is slow performing a running sum in a query especially if you're using the DCount() function. It will run slightly faster if you used an inline subquery.

Good to see that you got something working. It does sound like you're making a table everytime you run the query.

If you have time, throw me a db just showing how you've done it and I will see how it can be optimised.
 

Users who are viewing this thread

Top Bottom