Solved Working with Dates

[Space Cowboy]

@The_Doc_Man

Thank you for that clear explanation, after only a few short weeks I am starting, with the help received from your good self and others, to understand the basics of this new language that I have embarked on trying to learn. It is ok reading the books and watching you tube but the advice received on particular stumbling blocks is irreplaceable.

 

[The_Doc_Man]

You are quite welcome.l

 

[Space Cowboy]

Good Evening Good People,

I have hit another snag, I have (I think) tried every combination of solutions that I know about.

I have adapted the previous DCOUNT formula's into another query.

But rather than search by SalesRep I am doing a sales analysis by store.


DCount("*","MRH1","SumOfrprize1>=" & [SumOfrprize1] & " AND odate = " & Format([MRH1].[odate],"\#yyyy-mm-dd\#")) AS DayRank


DCount("*","AnalysisBaseMRH","rprize1 >= " & [AnalysisBaseMRH].[rprize1] & "and cname='" & [AnalysisBaseMRH].[cname] & "'" & " AND odate = " & Format([AnalysisBaseMRH].[odate],"\#yyyy-mm-dd\#")) AS PrizeRank


All the basic stuff is coming out correctly, the store sales are totaled and rankings are correct. The problem is that all store sales (from separate dates)are lumped together.

I am thinking I need something that sticks the store sales total to each date.. I think I need to distinct the store by date in the first clause. but maybe totally off track(as usual).

It could be, what to your good-selves, is a glaring error.
All solutions gratefully received.

The store field is cname.

 

[Gasman]

Please start a new thread.
Noone wants to have to wade through to post 43 to see the new problem.

Help us to help you.

 

[Space Cowboy]

Please start a new thread.
Noone wants to have to wade through to post 43 to see the new problem.

Help us to help you.

Your post prompted me to have another attempt at solving this problem.
Have query 1 with date, query 2 based solely on query 1 (inc date) query 3 was the dcounts referencing 1&2.
In one of the dcounts I referenced the date from query 1 when I had already referenced date in query 2.
Thus everything was messed up.

Thank you for helping to increase my knowledge.

 

[Pat Hartman]

Please mark this thread as solved and when you have new questions, start a new thread. If it is a follow on to a previous discussion, you can link to the old thread if you think that might help people but as Gasman pointed out, no one wants to read dozens/hundreds of posts to understand where you are not. Life is too short.