Results 1 to 4 of 4
  1. #1
    lqangel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    6

    Report based on a form, based on a query, based on multiple tables w/ similar fields

    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

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are multiple tables (with basically the same fields). The way information is kept is broken up into "chucks" of time.
    OK, this is only MY opinion...


    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)
    You will need to create a union query to get the data from all of the tables.


    But you have the problem of the next time "chunk": 2015 - 2018 (or whatever the years are).
    What are you going to do? You are going to have to add another table, THEN redesign the forms, queries and reports. This is what makes it a bad design. A good design would not require a redesign every time you wanted to add more year chunks.

    I think you need to rethink your table design/structure.
    There should be a table for "individuals data" and one table for "money distribution", but with an additional field for the time frame (chunk).

  3. #3
    lqangel is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2015
    Posts
    6
    Unfortunately I don't have the power to change the system to that extent (trust me when I say I'm dying to). This is how I came into it, and how it was always done. Now people just "do it that way because that's how they've done it before".

    I will look into the union query and see how difficult it would be to add the "chunk" they create next time.



    Thank you for your input.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe do it on your own and demo a "better" system?? Show them how the system could be better?? Less maintenance???


    Good luck with your project....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to Join Similar Fields Based on a Third Field?
    By Inaccessable in forum Queries
    Replies: 1
    Last Post: 07-12-2013, 03:56 PM
  2. Replies: 5
    Last Post: 03-27-2013, 05:10 PM
  3. Report based on Multiple Date Fields
    By viper210 in forum Reports
    Replies: 11
    Last Post: 09-27-2012, 07:07 AM
  4. Form Based Query for Multiple Fields
    By sureelsaraf in forum Access
    Replies: 0
    Last Post: 03-28-2011, 06:14 PM
  5. Report based on multiple tables
    By Pimped in forum Reports
    Replies: 1
    Last Post: 12-16-2009, 04:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums