How to link tables to achieve following.

[hanns.g]

I am trying to design database in access for first time. Having trouble with it. Following is my scenerio



I have 2 tables.

Table 1 will have front end form where user can enter info like below:

	User will enter this info on weekly basis
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%

Table 2

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
1/28/2014	2	2014	5
1/29/2014	2	2014	5
1/30/2014	2	2014	5

How can i build relationship so that when i query the data, the user entered stuff gets populated with matching Year, Period & Week number in following table. The end result of query should look 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%

[June7]

Is Period the year quarter? Period, Year, Week do not have to be saved in table, they can be calculated from the date.

Why are the rates incrementing by 10% each week? What happens when it reaches 100%?

Why duplicate table1 fields in table2? If you want to maintain two tables, then table2 only needs fields for:

Date
Table1 record ID

[hanns.g]

period field is pre poluated in table via another program, period is calculated based on 4445 method. Hence I want to pre populate it instead of designing the logic in access.

Rates are just example it could be what users enters, for example i used 10%.

Yes i realized i had duplicate field, i have posted the question again, removing duplicate field.

[June7]

User will enter data that applies to a full week then you want to create daily records in table2?

VBA code can run INSERT sql action to create records.

Use an autonumber field as primary key in table1 and number field in table2 with the table1 key value.

Or eliminate table1 and just save it all into table2. Yes, I know it's not fully normalized but you are already duplicating values between the tables. The rate fields aren't a lot of data. Have user input the info into unbound controls on a form and use code to create the daily records.

It is a balancing act between normalization and ease of data entry/output.

1 table or 2, the code for creating the daily records is basically the same but 1 table eliminates having to join tables to retrieve related data.