Return month data when a particular date is given

[nick1408]

I have setup the following query:

RICT Month is a date set as dd/mm/yyyy (e.g. 01/12/2016) while Week Ending is the same format but as the label suggests is a week ending date (e.g. 10/12/16)

The above query is not returning anything. but if I leave Week Ending out I get the following:

As a reference, here are the two tables:

 

[plog]

That's because no [RICT Month] field meets your criteria. And it never will.

[RICT Month] is a date and you are applying numeric criteria to it. Year() and Month() both return numbers. So, even if [RICT Month]=1/1/2016 and [Week Ending]=1/1/2016 your query will not show it because Month([Week Ending])=1 and Year([Week Ending])=2016 neither of which equals the date 1/1/2016.

You need to further explain what you are trying to do with your criteria, as it stands now its not logical--you are comparing dates to numbers and that will always be false.

 

[nick1408]

That's because no [RICT Month] field meets your criteria. And it never will.

[RICT Month] is a date and you are applying numeric criteria to it. Year() and Month() both return numbers. So, even if [RICT Month]=1/1/2016 and [Week Ending]=1/1/2016 your query will not show it because Month([Week Ending])=1 and Year([Week Ending])=2016 neither of which equals the date 1/1/2016.

You need to further explain what you are trying to do with your criteria, as it stands now its not logical--you are comparing dates to numbers and that will always be false.

Ok, thanks for the explanation.

I am looking to have the user enter a date [Week Ending]. Once this is entered I would like the return to be compared to [RICT Month] and return [RICT Per Week]. The rest is from calculations but relies upon the query returning the correct month's [RICT Per Week] depending on the actual date entered.

The endgame is to break a monthly figure down to a weekly figure.

 

[nick1408]

I tried using DatePart('yyyy',[Week Ending]) And DatePart('m',[Week Ending]) as the criteria but that didn't work either.

 

[plog]

I'm super lost now:

I am looking to have the user enter a date [Week Ending].

So far so good.

Once this is entered I would like the return compared to...

Huh? What is 'the return'? Did you instead mean 'it'? As in 'I would like it compared to...'?

... [RICT Month] and return [RICT Per Week].

Again, huh? How/where are you expecting [RICT Per Week] returned? I mean you are running a query, so I don't see a place to return data other than the query.

The rest is from calculations...

Even more lost. What is 'the rest'? And calculations? That query has no calculated field, it returns data from the underlying data sources.

...but relies upon the query returning the correct month's [RICT Per Week] depending on the actual date entered.

Again, the data in the query will be the only thing 'returned'. This isn't a function, it has no means to return data other than what the query produces.

I think the best way to help me understand what you are trying to accomplish is by data. Provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results from A. Show me what you expect the query to return when you feed it the data from A.

 

[nick1408]

Above are my two tables.

In [NPS Baseline] is a column called [RICT Month] (dd/mm/yyyy).
In [RICTweek] is a column called [Week Ending]. I have formatted this into dd/mm/yyyy since taking the screenshot but there are no other changes.

Above is the query I am trying to get working:
[Week Ending] will be adjustable by the user
All the % columns will be adjustable by the user
The third, fourth and fifth columns are calculated by in the following manner - [RICT Per Week] * [% NPS of RICT]. Each of the three columns are calculated in the same way.

When the user selects a date for [Week Ending] (for example, 26/11/16 - dd/mm/yyyy format) I want the return in the [RICT Month] to be 01/11/2016 and the [RICT Per Week] to be 466.69. The user can then adjust the % columns to suit themselves.

[RICTweek].[Week Ending] is added to the table via a form I have working. The query is the last piece of the puzzle.

I'm really sorry I didn't explain myself well. I am still learning all the jargon

 

[plog]

No need for jargon, provide data.

Looks like your last post supplied A, now give me B. Using the data you posted for NPS Baseline and RICTweek, show me what data your query should return. To reiterate (yes, I am actually reiterating, not just iterating), show me what data your query should return. Don't tell me about how to get there, show me the data you want returned.

 

[nick1408]

Hopefully this shows what I'm after:

25/11/2016|01/11/2016|466.69|....
18/11/2016|01/11/2016|466.69|...
02/12/2016|01/12/2016|67.83|...
09/12/2016|01/12/2016|67.83|...

after the ... is working so I left that bit out. The first 3 columns is what I am working on. Essentially it will look like [RICTweek] table in the picture above but with a few extra columns.

 

[plog]

Now its time for explanations. Using the data in post #6 you want these results:

25/11/2016|01/11/2016|466.69|....
18/11/2016|01/11/2016|466.69|...
02/12/2016|01/12/2016|67.83|...
09/12/2016|01/12/2016|67.83|...

Tell me exactly how you end up with 25/11/2016 for the first fields value? I mean the value in the starting data for the first record is 26/11/2016. Maybe its just a typo?

Then explain to me the second value of 1/11/2016. Where does that come from? Then the second row, first value of 18/11/2016? That can't be a typo--it doesn't appear anywhere in the sample data you started with in post #6.

I really don't see how your expected results are related to your starting sample data in post #6.

 

[nick1408]

Apologies, it was pure laziness at 2am here.

I'll try again:

26/11/2016|01/11/2016|466.69|....
03/12/2016|01/12/2016|67.83|...
10/12/2016|01/12/2016|67.83|...
17/12/2016|01/12/2016|67.83|...

For the data in post 6 I should be able to enter 31/10/2017 in the next record on the tableand expect back 01/10/2017 and 1294.3 in the query.

 

[plog]

Your data doesn't make sense. Your expected results:

26/11/2016|01/11/2016|466.69|....
03/12/2016|01/12/2016|67.83|...
10/12/2016|01/12/2016|67.83|...
17/12/2016|01/12/2016|67.83|...

Out of the 11 records you have in your sample data you only expect ID=1, 2, 3, 5 returned? How come ID=4 isn't returned. How come ID=11 isn't returned?

 

[nick1408]

[/IMG]

Ok, I've done this poorly so I'll start again.

Above is what I expect to see (done in Excel). I've colour-coded them into where the data comes from. The orange-shaded cells are user-adjustable (i.e. the user can adjust the % columns and add/adjust the [Week Ending] column.

The NPS Baseline columns are entered as a per month amount on [NPS Baseline] table. The actual dd in dd/mm/yyyy doesn't matter, it could simply be read as mm/yyyy

I need to break this monthly figure down into weekly amounts which I have done on the [NPS Baseline] table

This weekly amount needs to be assigned to each week within the month, hence where this query comes in.

 

[plog]

Well, I'm done. If anyone else wants to take a crack, feel free to jump in.

 

[nick1408]

Well, I'm done. If anyone else wants to take a crack, feel free to jump in.

Sorry mate, I just don't know how to explain it any better. Thanks for trying

 

[nick1408]

I tried (DatePart("m",[RICT Month]) And DatePart("yyyy",[RICT Month]))=(DatePart("m",[Week Ending]) And DatePart("yyyy",[Week Ending])) as the criteria but this doesn't seem to separate the dates either.

All I want is the data that is aligned to January come up when the a particular week in January is selected. I can get it to match a month or year but not both at the same time. Something like "match mm/yyyy, I don't care about dd"

 

[nick1408]

Ok, I got it working!

Criteria:

DatePart("yyyy",[RICT Month])=DatePart("yyyy",[Week Ending]) And DatePart("m",[RICT Month])=DatePart("m",[Week Ending])