Table Design

[erog]

Hello,

I'm having trouble deciding how to design this simple database. The main problem is that I have separate tables with the field 'fiscal year' but I want them to be joined into one when querying.

The goal of this is to create a database to store project information where users will be able to create reports on project funding, status, etc. I've created an 'entry form' with subforms for contacts, partner, fte, status, and funding. Users can enter information into FTE fields, Status fields and Funding fields based on a fiscal year... This is no problem.

Contacts and Partners are 1-1 because there is only one record associated per project - we chose to break up contacts and partners into two separate tables because it was getting rather long. FTE, Status and Funding are 1-M because there are multiple records associated per project because each record is based on fiscal year.

The problem is as follows:
When I do a query, I'd like to be able to select a fiscal year... and I'd like to get all the information from FTE, Status and Funding. Should I create a separate table Fiscal Year to join all the fiscal years from FTE, Status and Funding? Where would the fiscal year go and how would this be designed (lookup table, etc)?

I'm using Microsoft Access 2002

[Stewart Ross]

Hi. You don't need a separate table for this. The normal way to do it would be to have a drop-down box, say, in a userform which allows the user to select the fiscal year, then if a report is being generated to apply a filter based on the chosen fiscal year to the report when it is opened.

If I give an example this may be a bit clearer. I don't know the names of your tables, queries and fields, so bear this in mind when reading the skeleton below.

Assuming you have an unbound combo box on your form named cboSelectFiscalYear and that the fiscal year value is text (not a number):

DoCmd.OpenReport "rptYourReportName", acViewPreview, , "Fiscal_Year like '*" & Nz(cboSelectFiscalYear) & "'"

All that this does is to filter the report by the value of the fiscal year selected by the user in the combo. The Nz function referred to in the example substitutes an empty string if the combo has no current value (no fiscal year selected), and the use of LIKE with an opening wildcard character of '*' is to ensure that all years are returned if no entry is made.

If the fiscal year is just a number LIKE cannot be used and the comparison would be a little different - please advise what really applies in your case.

-Stewart