Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2019
    Posts
    1

    Total Newbie - Creating Reports/Queries from Multiple Tables

    Greeting Access Experts!

    I was using Excel to do this, but now that I have over a year of data, I want to be able to do some more with my data, like producing year over year comparisons, and I'm getting to the point where I have a lot of data.

    I am really rusty with Access. I've created my tables. I've created some relationships between my tables. I want to generate some reports, but it's not working.

    I want a report that shows me practically all the data from my tables. I want to short the main data from Table 1, a couple fields from Table 2 that would match up with Table 1, and one field from table two that would match up with data from Table 1.

    Ugh - I don't even know if I'm explaining this right...

    I'm trying to generate reports or queries or both for the Account Receivables in my office.

    From Table 1, I need:
    SSN, Name, PT, PP, Issue Data, AR #, Net, Unit

    From Table 2, I want it to look at Table 1 and match off of AR # and provide the Amount for each AR (or a blank if there is no match)

    From Table 3, I want it to look at Table 1 and match off of Unit and provide the Tech for each line

    Is this something that can be done? Am I better off just sticking with excel?


    At the end of the day, I want to be able to create some graphs based on the data. I would like a graph to show:
    By month, total ARs issued by Tech (Would love to be able to compare to 2018 data as well)


    By month, total ARs paid by Tech (Would love to be able to compare to 2018 data as well)
    By month, total ARs Issued and paid by Tech with a Year over Year comparison (In excel, I do a combo graph - bar for issue - line for payments)... I would love to have 2018 data next to 2019 data. I don't have any 2018 data in my sample database, or even more than 1 month.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    youre almost there, join the tables in a query,
    bring in the fields you need,
    change the query type to OUTER Join in order to see all AR. (read up on this join)
    save the query.

    Now you can make it flexible by adding parameters from boxes on a form, so make a form with txtStartDate, txtEndDate
    make the query read these:
    select * from tAR where [TransDate] between forms!myFrom!txtStartDate and
    forms!myFrom!txtEndDate

    base the report off the query.

    for year comparison, I have 4 text boxes (the 2 above and txtPrevStart, txtPrevEnd
    2 queries, qsCurrDate, qsPrevDate
    then stick both these queries into a 3rd to compare.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Looks like a fairly simple report using a query that joins related tables. Don't use INNER JOIN.

    Graphing, however, can get rather complicated and it's not as easy nor versatile as Excel. If you need more help with that, provide db for analysis. Follow instructions at bottom of my post.

    I hear that Access 2016 has new graphing features but since I have only 2010, will not be able to advise on.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-27-2017, 05:29 PM
  2. Replies: 13
    Last Post: 02-08-2017, 04:11 PM
  3. Replies: 3
    Last Post: 10-29-2015, 11:15 AM
  4. Replies: 3
    Last Post: 06-02-2015, 06:48 PM
  5. Reports on multiple tables / queries
    By drnld in forum Access
    Replies: 5
    Last Post: 07-02-2014, 09:04 AM

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