Compare Workday 2 workday

[WKRJ]

I have a table with 2 columns named ‘TodaysDate’ and ‘Amount’. I would like to get the average amount for the past 5 workdays and compare that to the average amount for the 5 workday prior to that, so I’m always using the past 10 records. I don’t have a clue as to where to start and would appreciate some guidance! Thank you in advance

 

[jdraw]

Did you pick Access for this? Have you considered Excel?
Is there more that you haven't told us?

 

[Mihail]

An idea:
Create 2 queries. Sort ZA by date field.
Show Top 5 records in the first query and Top 10 records in the second one.

Sum the Amounts (DSum function)
Sum5 = DSum(...) using first query
Sum10 = DSum(...) using scond query

Calculate averages
Avg1 = (Sum10-Sum5) / 5 'First 5 workdays
Avg2 = Sum5 / 5 'Last 5 workdays

 

[WKRJ]

Good Idea! Thanks! I hope this is the last question about this. I tried to use =dsum("price", "qryTotaltest") as the control source on a form but get #error. I created a 3rd query using the qryTotaltest query but get the same error. I looked up dsum and I think I'm using it correctly but I must be doing it wrong. Do you see what it is?

and no, all of this was given to me in access. The person bailed, I volunteered and management wants results yesterday!!

Thank You
wkrj

 

[Mihail]

Show us the query SQL

 

[David R]

How are you with VBA? You could probably step through an ordered recordset five times to get the opening and starting dates, and then sum over your now-known range...

 

[Mihail]

How are you with VBA? You could probably step through an ordered recordset five times...

... and sum directly. No more need to retrieve the date range.
Or, if you already know the dates, you can DSum with a WHERE clause.
Or you can use a Total query with parameters.
And... so on.

 

[namliam]

Do you want to compare them side by side, in 1 rowor do you want 2 records?

Assuming you only have workdates in your table.... how about a simple self join?
select ... from yourtable where datefield > date() -7
join this query back into the table "On Query.Datefield - 7 = Table.Datefield"
and then simply average it

 

[WKRJ]

I got it to work, finally but now I have another question. Can I have multiple control sources on one form. I have 2 queries and would like to display the information. Also, is there a site online that list all the commands available within access?

Thank You
wkrj

 

[namliam]

msdn.microsoft.com

If you want to display two queries, you make 2 subforms and display them on one main form