I have a table called "People" which has all sorts of info about each member of an organization. Name, address, etc. One portion of the table deals with whether or not they are an officer. That's a Yes/No field. If a person is an officer, I need to keep track of the year they became one. Instead of placing a field in the People table to simply store the year, I have used a second table called "FiscalYear" to keep track of the actual start and end date. In my People table I have the ID of the record in the FiscalYear table to determine when they became an officer. Hopefully that makes sense.

My question is how to show the StartYear field from the FiscalYear table on the form for People. I have the text box on the form, but right now it will just show the ID of the fiscal year. I simply need to look up the StartYear using that ID.

Do I need to use a subform for this since the actual data I want to display comes from the FiscalYear table, and not the People table?

Very simple question I'm sure, so thanks in advance for any help.

Yes, but you're doing it backwards. In the FiscalYear table you should have the ID of the person along with the years he/she served as an officer. You probably should not need a Yes/No field in the People table. In the FiscalYear table, the ID of the person, the office he held, and the start date and end date of his term.

Then a form/subform will do the trick. You'll have one record in the FiscalYear table for each office a person has held.

I'm actually using fiscal year for a variety of things in this database. When the person became a member of the organization, when they became an officer, as well as other areas like receipts of donations.

If we take the year the person joined the organization as a simpler example:

Person
...
FiscalYearID (to represent when they joined the organization)
...

FiscalYear
FiscalYearID
StartYear*
EndYear

* This is what I'd like to show on the form with data about each person.

Does this make sense, and is this the right approach? As I said, I need to use Fiscal Year elsewhere in the database as many things relate to it.

Ah, just dawned on me that a combo box showing defined fiscal years would be more appropriate in this case.

New question is how to just show the date portion of the start date field in the combo box? My select statement will return something like "7/1/2004", but I really just want to show the "2004" part.

Thanks.

Edited by: lkelly on Mon Sep 26 11:29:09 EDT 2005.

I believe you can use the Year function to return just the year:

Year([DateFieldNameGoesHere])

Where would this Year(...) formatting go? Around the select statement for the combo box? In the query itself. I'm unsure about the syntax.

Thanks.

I would put it in the query for the combo box.

Thanks, but could you help with the syntax?

Right now it has the standard:

SELECT Fiscal_Year.Start_Year, Fiscal_Year.End_Year FROM Fiscal_Year;

SELECT Year(Fiscal_Year.Start_Year) As SY, Fiscal_Year.End_Year FROM Fiscal_Year

You're heading for trouble. The recommended way to store data is that each table should store data about exactly one thing. You should have for sure a separate table for donations, probably for other entities as well. Otherwise, you're going to have to use code or extremely complex queries to get what you want from the fiscal year table. The bottom line is that you will have to work very extremely harder than you would otherwise have to work.

Read up on normalization. Any good database reference book can tell you about it. This site also has loads of posts about normalization.

Thanks, that works great.

Last question is formatting it to show "StartYear - EndYear" (i.e. 2002-2003) in the combo box.

Appreciate the help.

SELECT Year(Fiscal_Year.Start_Year) As SY, Year(Fiscal_Year.End_Year) As EY, Year(Fiscal_Year.Start_Year) & " - " & Year(Fiscal_Year.EndYear) As SYEY FROM Fiscal_Year

You'll probably want to hide the first two columns but use them instead of the displayed column. I no longer remember why you are doing this. LOL