Linking # of days to Period and Year

[KrispyData]

Let's say I have a huge dataset with the following as an example of the
columns:

Territory: West Coast
Sales: $250,000
Fiscal Year: 2008
Fiscal Period: 1

Each Period has a different number of days. For example, Period 1 in 2008
might have 20 days but Period 1 in 2007 might have 18 days. I want to be
able to add a column to my existing data sheet to show the number of days for
each Period/Year so I can calculate the average sales per day. Should I
create a table that lists the number of days per period? But then, how do I
solve the problem with the Year and Period being in two seperate columns?
Would access know to link 20 days to Period 1 for 2008 but not 2007?

 

[John W. Vinson]

Let's say I have a huge dataset with the following as an example of the
columns:

Territory: West Coast
Sales: $250,000
Fiscal Year: 2008
Fiscal Period: 1

Each Period has a different number of days. For example, Period 1 in 2008
might have 20 days but Period 1 in 2007 might have 18 days. I want to be
able to add a column to my existing data sheet to show the number of days for
each Period/Year so I can calculate the average sales per day. Should I
create a table that lists the number of days per period? But then, how do I
solve the problem with the Year and Period being in two seperate columns?
Would access know to link 20 days to Period 1 for 2008 but not 2007?

Yes, you should create such a table; it should have fields for the FiscalYear,
FiscalPeriod and number of days. You'ld join on BOTH the FY and FP fields - a
query does not need to join on only one field, it can join on up to ten.

If it's a "huge" table (millions of rows?) then by all means put a nonunique
Index on the combination of FiscalYear and FiscalPeriod so the join is more
efficient; it's less critical but probably helpful to put such an index
(unique in this case) in your new table too.

 

[KrispyData]

Thanks for the info, John! So, just to make sure I get it -- I will create a
table with columns: FY, FP, Days in Period (we'll call this the DaysTable).
I will join that to the SalesTable by FY and FP. Can you tell me more about
adding a "unique index"? Is this something I will do the SalesTable or the
DaysTable? or both?

 

[John W. Vinson]

Thanks for the info, John! So, just to make sure I get it -- I will create a
table with columns: FY, FP, Days in Period (we'll call this the DaysTable).
I will join that to the SalesTable by FY and FP. Can you tell me more about
adding a "unique index"? Is this something I will do the SalesTable or the
DaysTable? or both?

Certainly to the DaysTable, since you don't want a given date to have two
records. Open the table in design view and ctrl-click both the FY and FP
fields. Then click the Key icon to make these two fields the joint two-field
Primary Key.

If your SalesTable should have only one record for any combination of FY and
FP - which I sort of doubt, but might be! - then use the Indexes tool in table
design view. It looks like lightning hitting a datasheet. Put some distinctive
name in the left column - UniqueFYFP perhaps; select FY in the right column,
and then select FP in the second row of the right column (leave the left
column blank). Check the unique index button and save the index.

 

[KrispyData]

Hi John:

In my DaysTable I have the FY and FP fields as a primary key. I now join
the FY field from SalesTable to the DaysTable. However, when I go to join
the FP field to the DAysTable it says there is a relationship already
created. Is this why I was supposed to do the UniqueFYFP thing you
mentioned?

 

[John W. Vinson]

Hi John:

In my DaysTable I have the FY and FP fields as a primary key. I now join
the FY field from SalesTable to the DaysTable. However, when I go to join
the FP field to the DAysTable it says there is a relationship already
created. Is this why I was supposed to do the UniqueFYFP thing you
mentioned?

You need *ONE* relationship with *TWO* fields. Join FY to FY, FP to FP;
enforce RI on both of them. You may get a warning message but if so ignore it.