Database Design problem
I am designing a database for our in-house admin that works fine - or at least, I thought it did. At the moment it is intended to collect information for a range of clients so one client (Tax Entity) can have many tax returns. I have the field "Tax Year" in the Table "Tax Return" and display this together with all the other fields from this Table in a Tab of a layout based on "TaxEntity".
However, I can't work out how to select only information from a specific year for that client. It must be possible to swap between Tax Years for any client. I am concerned that if I add a drop down menu with alternative years (eg 2008, 2009, 2010 etc) my solution at the moment only changes the Tax Year value but leaves all the other data as is.
Somehow I need to be able to create a new tax return for a different tax year for the client, so that I don't overwrite the info from last years work but fill in blank fields instead.
Because we sometimes work on two successive Tax Years at the same time I also need a way to select the year to be worked on somehow.
Can anyone please help me? My brain's hurting with thinking about it too much and I need a fresh perspective: I'm sure it's quite simple but as this is only my second database my experience level doesn't help me much! I don't know if it is a Relationship design problem, a layout design problem, or if I need some scripting...