yearly changing data help! how to capture?

[duckster]

--

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!

 

[KenHigg]

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

???

 

[duckster]

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.

 

[duckster]

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?

 

[KenHigg]

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...

???

 

[duckster]

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