Results 1 to 7 of 7
  1. #1
    HarryZ is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2019
    Posts
    4

    Need assistance with generating a report

    Aloha! First post here. I have a little bit of Access knowledge gained from working with it for some time, but I need help with a new project.



    I am the registrar for a car show. I gather entrants data (first name, last name, car make, model, year, etc.) in an Excel spreadsheet, and use Access to generate reports to help run the show. I would like to add a new report which will require input from a second Excel spreadsheet and I have no idea how to do this.

    The first spreadsheet has the Car Number plus the details about the car (owner name, make, model, year, etc). The second spreadsheet has the winner for each class and has just two columns: Class Name and Car Number. The report I want to generate will list the winner for each class and also provide the owner and car information. Something like this:

    Best Engine - John Doe, 1969 Boss 429, Blue with white interior

    But, I have no idea how to do this. Any assistance would be greatly appreciated.

    Harry Z

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    Why is Excel even involved?

    How are you accessing the Excel data? Are you setting links to worksheets?

    Linked sheets can be used like tables. Build a query with JOIN on Car Number.
    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.

  3. #3
    HarryZ is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2019
    Posts
    4
    Aloha June7. Thanx for the quick reply.

    There are a couple of reasons why Excel is involved:
    1) I'm pretty proficient with Excel and know almost nothing about Access. I'm just using access to generate reports.
    2) The entrants register for the show using our website which has a database plugin to store all the data. The only option to export the data is as a .csv file and I can easily convert these to .xlsx files and work with them.
    3) The data needs to "scrubbed" - e.g.: some people enter their name in UPPER CASE, some only in lower case, some use Normal Capitalization. I edit the data to get them all to normal capitalization. I also add a column with the entry number as the database system does not offer this capability. No idea how to do any of this in Access.

    As for the join you mentioned - if I knew how to do this, I would not be asking for assistance.

    Mahalo,

    Harry Z

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,874
    Suggest reviewing a tutorial on building queries. Access has built-in tools to aid with building queries. The one you want is very basic.

    https://www.makeuseof.com/tag/quick-...t-access-2007/

    https://support.office.com/en-us/art...7-d254561f8bce

    First, have to get data into Access. So I presume you have links already set?
    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.

  5. #5
    HarryZ is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2019
    Posts
    4
    I struggled with this for a while. Finally found this page

    https://support.office.com/en-ie/art...6-09571c7064a9

    Used the instructions in this section "Use the Query Wizard to build a query from a primary table and a related table" to define a relationship between the two tables. This was the piece I was missing.

    I then used the Query Wizard to get the data I wanted from the two tables, and then created a report from this.

    Mahalo for your assistance.

    Harry Z

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,877
    If you care,
    - Access can deal with csv data
    - Proper case is also possible to enforce in a report or form without needing to worry about how anyone enters their name
    - an entry number "column" (field) is also very doable in any database

    Maybe something to check out during the "off" season, assuming there is one.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    HarryZ is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2019
    Posts
    4
    Quote Originally Posted by Micron View Post
    If you care,
    - Access can deal with csv data
    - Proper case is also possible to enforce in a report or form without needing to worry about how anyone enters their name
    - an entry number "column" (field) is also very doable in any database

    Maybe something to check out during the "off" season, assuming there is one.
    Aloha Micron,

    Yeah, I have a list of things to do during the "off season", but never seem to have time to do them. Not too surprised that Access can handle these things but really don't want to spend the time on it.

    Mahalo for the response!

    Harry Z

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

Similar Threads

  1. Generating A Count In A Report
    By McArthurGDM in forum Reports
    Replies: 3
    Last Post: 04-15-2015, 11:26 AM
  2. Query and report assistance
    By POS120 in forum Queries
    Replies: 1
    Last Post: 03-18-2012, 04:48 PM
  3. Help on Generating a Report!
    By ETCallHome in forum Reports
    Replies: 10
    Last Post: 06-22-2011, 01:08 PM
  4. Requesting Materials Report Assistance
    By pkott in forum Reports
    Replies: 2
    Last Post: 10-21-2010, 08:49 AM
  5. Generating Report from Form
    By mwabbe in forum Reports
    Replies: 8
    Last Post: 08-30-2010, 12:25 PM

Posting Permissions

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