Completing Data Set Dates for Calculation

[Lior123]

I am trying to use a moving average formula but I am getting an error which I assume is because dont have data for all the days.

My dataset excludes dates for players on days that they did not show up, but I want to calculate that day as a 0 for that day.

I can calculate the all the dates I need by grouping by "Date" but how can I complete the dataset and add the missing dates - when I add the missing date the the value(score) would be 0.

For example:

This dataset:
Date Score
1/1/2011 5
1/2/2011 5
1/3/2011 5
1/6/2011 5
1/8/2011 5

Would become this dataset:
Date Score
1/1/2011 5
1/2/2011 5
1/3/2011 5
1/4/2011 0
1/5/2011 0
1/6/2011 5
1/7/2011 0
1/8/2011 5

Thanks.

 

[Joe4]

Create a separate table that lists all your possible dates and link that in to your other dataset via a query. You can use the NZ function to return a zero for the dates which don't match (do a left outer join from the tables listing all possible dates).

 

[Lior123]

Thanks for the reply Joe but I dont know exactly how to write the query to create this complete list.

Can you please write the query.

 

[Lior123]

I wrote this one but I am not getting all the days for all the names:

SELECT [Full Date Range].Days, Scores14days.Name, Nz([Scores14days]![Scores)]) AS Score
FROM [Full Date Range] LEFT OUTER JOIN Scores14days ON [Full Date Range].Days = Scores14days.Days
GROUP BY [Full Date Range].Days, Scores14days.Name, Nz([Scores14days]![Scores)]);

 

[Joe4]

Thanks for the reply Joe but I dont know exactly how to write the query to create this complete list.

What I am saying is to first create a table that has all the dates in it. So you need one record for each day you want. There isn't really a way to create this automatically (without some VBA). I suggest creating the list in Excel (where you can enter a date in cell A1 then in A2 you can enter the formula =A1+1 and copy down as far as you want), then importing that Excel file into an Access table.

With the NZ function, to return 0 where there is no match, you would write it like this:

Nz([Scores14days]![Scores)],0)+0

I am not sure why you are grouping in your query, unless you can have duplicate entries for some days (if so, wouldn't you want to sum those values?).

If you are still having problems writing the query, let me know the exact field names in each table that you are including and what they represent

 

[Lior123]

Hi Joe,

<b>Step 1:</b>
I am assuming there will be at least one player with a score in each of the dates - so I am taking the [scores14days] table and grouping the dates to get all the dates in the range and I am calling that [Full Date Range]. So I have my table with all my dates in it.

<b>Step 2:</b>
I am taking my table with the players, dates and scores - table: [scores14days] which has three columns: The date of the game [Days], Player Name [Name], Their Score [Score]

The issue is that a player did not show up for all days so he/she does not have a record in the [scores14days] table, so I am trying to figure out how to add their name to the list on days they weren't there, and on that day give them a score of "0".

Let me know if this clear it up.

Thanks,

 

[Joe4]

You should have three tables:

1. Player Names
2. All Dates
3. Player Scores

If you do a query between 1 and 2 WITHOUT a join, it will create a Cartesian product, which means it will create a list of all dates for all player names.

Then, use this query with a Player Scores, doing a Left Outer Join on Name and Date. You will use the NZ function like I showed you to return 0 for records with no matches.

 

[Lior123]

My table can contain (20,000+ names) X (60 days) at some times, I would think this is not very efficient for Access.

Is this the only way to do it?

 

[Joe4]

My table can contain (20,000+ names) X (60 days) at some times, I would think this is not very efficient for Access.

Is this the only way to do it?

From how you described it, you want to show all 60 days for all 20,000 names, correct? That is how I would do it. What is inefficient about? Don't confuse volume with inefficiency.

You should only have to set up these tables once, and make changes if you have additions/deletions.

If your volume gets too large that it affects Access performance, then you might need to look into bigger database programs like SQL or Oracle.