I will try to break this down as best I can. Please help!
I am working with an Access Database that has individuals data in it (such as SSN, Name, Employer, etc.). The database is used to track when money is distributed and WHY it was distributed. There are multiple tables (with basically the same fields). The way information is kept is broken up into "chucks" of time. For Example:
Table A - This lists all distributions, by person, by type (meaning if they had two different ones there would be two listing with different "reason" codes) for years 2000-2004
Table B - Lists same thing as table A, but for years 2005-2009.
Table C - Lists same thing as table A&B, but for years 2010-2014
etc. etc.
What I would like to do is have a form that just shows the fields "Social Security #", "Name", "Employer". Then, when you look up a particular employee on the form you can click a button to run a report for that employee only and it would look in ALL tables (for all years) and provide a list of any/all distributions that happened with "reason code" Hardship. I figured I could create a query that would link all the tables, only pull in those that have "reason code" Hardship, that way when I would look up an individual by their Social Security # it would only show me a result IF they had a hardship, then the report created by clicking a button would list the following fields (found in EACH table): Year, Distribution Amount, Employer.
I think I can figure out how to create the report, but I have no idea where to start on the query or how to link the tables in a way that would work for me.
Please help!!
Thanks, Ashley