--

Hi everyone,
I'm a new user to access, so sorry if this question seems trivial. I'm trying to create a small database for a small tax firm.

Basic scenario: Client database (email is primary key) with basic client info table, client income table, amount of tax return table.

I have all the above tables drawn up, but am not sure how I would allow the database to accept multiple years for a particular client. It needs to capture the changing income table data, tax refund amount table data, etc. for clients who have filed for 2004, then perhaps file again in 2005 and 2006 (repeat customers, so to speak).

Need to be able to capture how many repeat customers there are, what their tax return amount and income was for each of the separate years, etc.

Hope I don't have to create more tables, as it might get difficult to manage.

Any help much appreciated, thanks!

Can you post an image of you tables and the relationships. Sounds like you need a table for years.

???

Can you post an image of you tables and the relationships. Sounds like you need a table for years.

???

I'm still drawing it up actually, but something simple like this for example:

Client Table:
-email
-firstname
-lastname

Client Income:
-email
-income

Refund Table:
-email
-refund amount




--Help appreciated.

Maybe I should just add a DATE field in the Income and Refund Amount tables, and for clients with multiple years, the Income and Refund Amount table fields will contain multiple records for that client?

Is this the best way?

Sorry, you need another table. Separate the client data into 2 tables. One to hold information that only occurs once. The second table will hold the financial information and contain one record per client per year.

I don't see a reason to have a separate table for refunds. You should have one field with a positive or negative amount depending on whether the client ows money or will get a refund.

Using email address as the primary key is not a good idea. Use an autonumber and just keep the email address as a separate field in the client table.

When you get the tables finished, make sure that the primary keys are defined. The financial table will have a two-part primary key - ClientID plus ReturnYear. The final step is to define relationships and enforce referential integrity. Select the Cascade Delete option so that financial records will be deleted when you delete a client record.

First - I applaud you for wanting to get this part done right before you do anything else!!!

The first thing I can see is that I would recommend you not use email as the key(s). I would use an autonumber field. Technically I guess it would work unless a client didn't have an email address. Other than that, there really isn't enough info here to make any major design recommendations.

You should start with an itemized list of exactly what you want this database to do...

???

Thx a lot for ur help, Pat and KenHigg.

I've added a lot more fields/tables since last week. Re. my question above, I went ahead and created another table, with ClientID and Contact Year being the combined primary key for tables that have yearly changing data for repeat customers.

Moving on to forms shortly :)