Design Question: Multiple years

[dcfrancis]

Good morning,



I understand the following question has been asked in different examples, but my Access knowledge is not yet at a standard where I can apply the solutions to my particular problem.

I have to set up a database at work that:
a) Contains all the relevant information about 250 companies, such as addresses, contact details etc.
b) Holds a variety of financial information for each company for various years (eventually this will be for 2009-2020).

So far I have two tables, one called "Company info" with "company name", "address", "contact number" etc, and another with the financial indicators.

However, on the second table, I have three axes, as it were. I have the companies, the financial variables, and the financial years. Do I just allow for multiple records for each company according to the financial year, or is there a better way to do this? If I do allow multiple records, does the primary key for this table become a combination of "Company name" and "Financial year", and if so, how do I link this up to the "company info" table? (I've tried setting a relationship but it says the data types are incompatible.)

Any help would be much appreciated!

[June7]

Right now the only field in both tables that can be used to link the two is the company name. This assumes the company name is spelled exactly the same in all occurrences in the financial table. Which fields did you try to set the relationship with?

You can set the company and year as compound primary key in the financial table. This will prevent the repetition of pairs, otherwise pk not needed.

[dcfrancis]

Thanks! I tried to set a relationship between the compound primary key of company and year, and then company name on the other table, but I can see now why that doesn't work.

Regarding linking the tables using company name, I though about using a lookup list to ensure the names are all spelled correctly and consistently. However, I have read that these lookup lists can cause problems. In this instance, would you recommend using one?

Thanks again.

[June7]

Lookup lists are not bad when understood and used properly. What most experienced developers don't like to do is set lookups at table level, only on forms, because developers set up apps so that users do not work directly with tables. When developers work with tables they want to see the actual value of fields, not the lookup alias.

In your case, a lookup list is not needed. The company name is a long primary key but it will work. Most developers would probably designate a much shorter value as pk, such as an autonumber datatype field. Modifying the pk/fk for your design would mean creating the autonumber field in customer info and a corresponding number field in financial table. Then update this fk field with the autonumber values with an UPDATE query that joins the two tables on the customer name field. Once the fk field is populated set the relationship link on the new pk/fk.

Use a form/subform arrangement for data entry/edit. Review http://office.microsoft.com/en-us/ac...010098674.aspx

[dcfrancis]

Thanks very much. This has been most helpful.