• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Making a query that counts

I need a query for the field,” defect category”,( that is a dropdown). All the values in the drop down should be broken down in their category . Then I need the totals of each category. Then I need to take that query and make a report. For example:

LOCATION        DEFECT CATEGORY
Korea                    broken
Korea                    broken
                             Total: 2
Colorado                 Leakage
                             Total: 1
I have enclosed my database for further explanation.
Inventory-db.accdb
0
gigifarrow
Asked:
gigifarrow
2 Solutions
 
TobiasCommented:
Dear gigifarrow,

I have modify your file.

Please let me know if it's was you expected.

Best Regards
Inventory-db.accdb
0
 
peter57rCommented:
You create a query that gives you the list of records to be reported.  Then use the report wizard to build your report from this query and include a Group by location and specify a count of Defect Category as you go through the wizard.
0
 
gigifarrowAuthor Commented:
thank you for your help. it is halfway right. I do apologize if I wasnt clear. I need a total of each defective category then a grand total all together. This one shows the quantity for each, but doesnt total for each defect.
0
 
Leo TorresSQL DeveloperCommented:
Just as a tip before you go on any further.. I would fix table design.. This design will cause you many pains going forward. Its best to fix it now. What I mean is the table relationships.. The tables have to be Normalized.

For example location and defects are what I see since I tried to do this query for you.
in the Inventory_tbl and Location_tbl should be linked by an ID in the Location_tbl. You have the location in the Inventory table which beats the point of having a location Table..
In the Inventory table you should have IDs that match back to location table..

The Same can be said for defects makes no sense to have a defect table if the dects description is in the inventory table. Place those descriptions in the defect table and reference them via the ID in the inventory table  

This is called 2NF (2nd Normal Form)
Look into Normalization of your database design
here is a link
http://www.youtube.com/watch?v=uO80f642LCY

Once that has been done your query building will fall in place
0
 
TobiasCommented:
Ok. I have made the change. but you lose the location. If it's not what you want please make again another example.
Inventory-db.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now