how to build this kind of relationship in table and auto populate based on user entry

[hanns.g]

I have a access table for which i want to design a form, where user will enter the following info.

User will enter this info on weekly basis
Period No	Week No	Canadian Dollar rate	US Dollar Rate	British Pound Rate
1	1	10%	10%	10%
1	2	20%	20%	20%
1	3	30%	30%	30%
1	4	40%	40%	40%
2	5	50%	50%	50%

Based on above input. Another table will get populated like below.

Date	Period	Year	Working Week of Year	Candian Rates	US Dollar Rates	British Pound Rates
1/1/2014	1	2014	1	10%	10%	10%
1/2/2014	1	2014	1	10%	10%	10%
1/3/2014	1	2014	1	10%	10%	10%
1/4/2014	1	2014	1	10%	10%	10%
1/5/2014	1	2014	1	10%	10%	10%
1/6/2014	1	2014	2	20%	20%	20%
1/7/2014	1	2014	2	20%	20%	20%
1/8/2014	1	2014	2	20%	20%	20%
1/9/2014	1	2014	2	20%	20%	20%
1/10/2014	1	2014	2	20%	20%	20%
1/11/2014	1	2014	2	20%	20%	20%
1/12/2014	1	2014	2	20%	20%	20%
1/13/2014	1	2014	3	30%	30%	30%
1/14/2014	1	2014	3	30%	30%	30%
1/15/2014	1	2014	3	30%	30%	30%
1/16/2014	1	2014	3	30%	30%	30%
1/17/2014	1	2014	3	30%	30%	30%
1/18/2014	1	2014	3	30%	30%	30%
1/19/2014	1	2014	3	30%	30%	30%
1/20/2014	1	2014	4	40%	40%	40%
1/21/2014	1	2014	4	40%	40%	40%
1/22/2014	1	2014	4	40%	40%	40%
1/23/2014	1	2014	4	40%	40%	40%
1/24/2014	1	2014	4	40%	40%	40%
1/25/2014	1	2014	4	40%	40%	40%
1/26/2014	1	2014	4	40%	40%	40%
1/27/2014	2	2014	5	50%	50%	50%
1/28/2014	2	2014	5	50%	50%	50%
1/29/2014	2	2014	5	50%	50%	50%
1/30/2014	2	2014	5	50%	50%	50%
1/31/2014	2	2014	5	50%	50%	50%
2/1/2014	2	2014	5	50%	50%	50%
2/2/2014	2	2014	5	50%	50%	50%

So far I have create 2 tables in access and stuck. what to do next.

[John_G]

Your tables contain too much redundant data.

1) You don't need the three rates columns in the second table - you already have them in the first one.
2) You don't really need the Year column - it is implicit in the date.
3) You don't need the Period column in the second table - you already have it in the second one.

Your second table can be related to the first one using the week number.

Users can enter data into the first table without referring to or affecting the second one.

Now, if the rates change from one day to the next within the SAME working week, then what I said above will not work, and in this case you might not need the first table at all.

Can you provide a bit more detail on what you are trying to do?

John

[hanns.g]

Thanks John

I have redesign my tables. Below is updated design.

User_Entry_Table

Year Period No Week No Canadian Dollar rate US Dollar Rate British Pound Rate 2014 1 1 10% 10% 10% 2014 1 2 20% 20% 20% 2014 1 3 30% 30% 30% 2014 1 4 40% 40% 40% 2014 2 5 50% 50% 50%

Calendar Table

Date	Period	Year	Working Week of Year
1/1/2014	1	2014	1
1/2/2014	1	2014	1
1/3/2014	1	2014	1
1/4/2014	1	2014	1
1/5/2014	1	2014	1
1/6/2014	1	2014	2
1/7/2014	1	2014	2
1/8/2014	1	2014	2
1/9/2014	1	2014	2
1/10/2014	1	2014	2
1/11/2014	1	2014	2
1/12/2014	1	2014	2
1/13/2014	1	2014	3
1/14/2014	1	2014	3
1/15/2014	1	2014	3
1/16/2014	1	2014	3
1/17/2014	1	2014	3
1/18/2014	1	2014	3
1/19/2014	1	2014	3
1/20/2014	1	2014	4
1/21/2014	1	2014	4
1/22/2014	1	2014	4
1/23/2014	1	2014	4
1/24/2014	1	2014	4
1/25/2014	1	2014	4
1/26/2014	1	2014	4
1/27/2014	2	2014	5

Rates don't change in same working week. It is once per week.
Date calendar is created by formula that says: Each month has 4 or 5 period to aligned everything to analyse year or year data.

[hanns.g]

my end results that I want is to merge user entry data to calendar data and populate rates for that entire week.

[John_G]

Hi -

That should work. All your users need to do is update the first table, and you can make a query to join the two tables on week number (and year if necessary).

If you want to populate the second table automatically, I think you will need to use VBA to add 7 records (7 days) each time. You might want to consider having the user also input the week number and the period number as well. The "next" date to use could be determined automatically.

HTH

John