Results 1 to 10 of 10
  1. #1
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10

    Simple Beginner Question

    Hey guys,

    I have what I think is a straightforward question but I cannot figure it out on my own.

    Some backround info: I am building an RMA database where customers send in a broken product and we use parts and labor to fix it. We need to run monthly reports on all parts used across all RMA's.

    Database structure: There are three tables which are Customer, RMA, and Parts. Currently, I have the RMA form setup so you can pick a part from the "Parts" table and it stores the PartID in the "RMA" table so we can associate all parts used for each individual RMA. That is all working, and the RMA table is storing all the PartID's (typically 3-15 parts per RMA) used for each RMA

    My question:


    I need to make a query that shows the total number of parts used each month across all RMA's. I need to show the item number, item description, and the total number used of each. I can make a querry that shows all the PartID's used each month, but I need to show the associated information such as description and item number. Can please you offer advise about how to achieve this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The RMA table doesn't have fields for each part, does it (Part1, Part2, etc)? That would be a design mistake if so, and make it difficult to get what you want. To your question, if the data is designed correctly, you'd simply join the RMA table to the parts table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I found this re: RMA (wikipedia)

    A return merchandise authorization (RMA), return authorization (RA) or return goods authorization (RGA) is a part of the process of returning a product in order to receive a refund, replacement, or repair during the product's warranty period. The purchaser of the product must contact the manufacturer (or distributor or retailer) to obtain authorization to return the product. The resulting RMA or RGA number must be displayed on or included in the returned product's packaging;[1] no returns are accepted without this number.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by orange View Post
    I found this re: RMA (wikipedia)
    I'm guessing the OP knew what it meant.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I know the OP knows. It's the readers who might try to help who don't necessarily understand the RMA jargon.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Create a 2nd query with the Parts table linked on PartID back to the query of all the Parts used that you say you already have to get the other specific parts fields? You might have to do some grouping to get rid of dups maybe.

  7. #7
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by pbaldy View Post
    The RMA table doesn't have fields for each part, does it (Part1, Part2, etc)? That would be a design mistake if so, and make it difficult to get what you want. To your question, if the data is designed correctly, you'd simply join the RMA table to the parts table.
    Yes it does, the RMA table has 20 fields (part1, part2...) to store the parts used. The Parts table has thousands of parts but each RMA would only need a max of 20 parts used. If this is an undesirable way to handle this issue, then what would be a better approach?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A related table with a record for each RMA/part combination. Review this, in particular the part below Figure 7:

    http://www.r937.com/Relational.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Excellent, this seems like a much more organized way of doing things. I have it setup as you suggested, however, now I have the problem of accessing a new table from an existing form. Is there a way to do this without a sub form? I am guessing there is a way to do this with the control source of a combo box for instance, but I cant figure it out. Is there a member operator function I can do in the control source, something like table.field?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure how you would do it without a subform, which is the normal way to present data from one-to-many related tables. It's like an invoice, where the header (form) contains the invoice number, customer, date, etc. The detail section (subform) contains a row (record) for each product purchased.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Beginner Question...
    By ddoumani in forum Access
    Replies: 5
    Last Post: 04-25-2016, 05:41 AM
  2. Beginner question, related to if statement.
    By nstasiak in forum Access
    Replies: 3
    Last Post: 05-11-2015, 05:56 PM
  3. Query question of beginner
    By Testar in forum Queries
    Replies: 8
    Last Post: 10-21-2014, 07:39 PM
  4. Replies: 5
    Last Post: 01-21-2013, 01:09 PM
  5. Beginner - basic question
    By kevinnice in forum Programming
    Replies: 3
    Last Post: 03-08-2012, 11:31 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