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

    Summary Quarry Question

    Hey guys,

    I am building a simple database that tracks the parts used and the work done when a customer sends in a product to be repaired and returned (typical RMA). I have my tables and forms setup and working but now I need to make a query that summarize the total number of unique parts used in a given month, for external inventory purposes. The RMA form stores each part that is used in a given repair to an individual field.

    Table Structure:
    -Table for customers
    -Table for testing, work done, and notes
    -Table for all the part numbers (300+) and their descriptions


    -Table to store all the parts that are used in each individual RMA (30 fields: part 1, part2...)

    My query needs to add up all the unique parts used for a month and give the totals of each part used. I am stuck and looking for advise. Here is a picture of how my data looks in the test query.
    Click image for larger version. 

Name:	1.JPG 
Views:	16 
Size:	41.7 KB 
ID:	25312

    How would I go about making this query do what I want? I will rep all who help, thank you very much!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Your table is designed wrong.
    You should not have 30 fields, it should be 3
    code,person ,part.

    The list will extend down vertically, NOT horizontally w 30 fields.
    Queries search top,down, not left right.
    the # of fields is limited.
    the # of records is infinite.

  3. #3
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by ranman256 View Post
    Your table is designed wrong.
    You should not have 30 fields, it should be 3
    code,person ,part.

    The list will extend down vertically, NOT horizontally w 30 fields.
    Queries search top,down, not left right.
    the # of fields is limited.
    the # of records is infinite.
    Ok, I thought I was doing something wrong this makes more sense, thank you. Just to be sure, you are suggesting multivalued fields correct?

    like this: https://support.office.com/en-us/art...c-6de9bebbec31

    How would I go about picking parts from over 300 unique part numbers to occupy this field?

  4. #4
    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 agree with ranman --your design doesn't support your business.

    Here's a quick link via Google on Return Material Authorization as seen by Oracle. I'm sure something at the link will be helpful for concepts and ideas.

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Not 1 multivalue field,
    Many single value records. Query can then sum,count,analyze what you need.

  6. #6
    dbmiller5 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Quote Originally Posted by ranman256 View Post
    Not 1 multivalue field,
    Many single value records. Query can then sum,count,analyze what you need.
    You have me confused now. I accept that my table design is incorrect, however, I don't yet understand how to implement the method you are suggesting.

    I believe that you are suggesting a table with 3 fields, and to use many records for each RMA instead of trying to store 1 RMA with many parts fields. Every part picked for each repair would be a new record in the table. The ID field would link each part to the corresponding RMA, I get that part. I don't understand how to implement this when I picking parts from the RMA form. Maybe its the correct approach, but it really doesn't seem like the easiest to implement for a beginner. Can you offer a suggestion of how to fill the table, aka, how to do this via the RMA form.

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

Similar Threads

  1. Summary Report
    By balajigade in forum Access
    Replies: 4
    Last Post: 02-05-2016, 09:24 PM
  2. Quarry attendance with employee list
    By Salehin31 in forum Queries
    Replies: 2
    Last Post: 09-28-2014, 11:34 PM
  3. How to get just a summary?
    By Russellh in forum Queries
    Replies: 1
    Last Post: 06-18-2014, 09:06 PM
  4. Tenure Summary Report Question
    By srmezick in forum Reports
    Replies: 3
    Last Post: 10-31-2011, 01:40 PM
  5. Replies: 7
    Last Post: 06-14-2011, 10:37 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