Time Series Calculation in Access (1 Viewer)

[gjm2120]

Hi everyone,

Very newbie at Access here, was wondering if anyone could point me in the right direction with a problem I have.

Let's say that you have a cookie jar that's to be shared with two children, let's call them Jack and Jill.

You know that to start with (t = 0), there are 100 cookies in the cookie jar

Now the strange thing about Jack and Jill is that they start eating cookies on different days and that their cookie appetite depends on the number of cookies in the jar when they start, Specifically

Jack has an appetite of 10% of the total cookie jar and he starts eating on day one.

Jill eats after Jack and she has an appetite of 5% of the jar.

So what this would look like is:

Day 0 - Cookie Jar has 100 cookies
Day 1 - Jack eats, Cookie Jar has 100 - 100(10%) = 90 cookies
Day 2 - Jill eats, Cookie Jar has 90 - 90(5%) = 85.5

Problem:

1. Given that I have a table containing the starting number of cookies e.g.:
CookieTable
Startingcookies
100

2. Given that I have a table containing a record for Jack and a record for Jill, each with two fields: Eating Start date, Appetite % e.g.:

AppetiteTable
Child, EatingDay, Appetite%
Jack, 1, 10%
Jill, 2, 5%


What will the query look like that:

1. Calculate the number of cookies in the cookie jar over time e.g.
Day 0, Day 1, Day 2
100, 90, 85.5

2. Calculate the total number of cookies eaten by Jack and Jill e.g.
Child, Cookies Eaten
Jack, 10
Jill, 4.5

Thanks!

 

[Uncle Gizmo]

This is Wrong:
Day 0 - Cookie Jar has 100 cookies
Day 1 - Jack eats, Cookie Jar has 100 - 100(10%) = 90 cookies
Day 2 - Jill eats, Cookie Jar has 90 - 90(5%) = 85.5


Should be:
Day 0 - Cookie Jar has 100 cookies
Day 1 - Jack eats, Cookie Jar has 100 - 10(10%) = 90 cookies
Day 2 - Jill eats, Cookie Jar has 90 - 4.5(5%) = 85.5