Access Query/Table Design Question

Hi and sorry if I am asking the wrong question here.  I'm sort of flailing around.  I used Access quite a bit a looooong time ago, but now I seem have forgotten everything I've learned.  Anyway here is my mission.

I have a set of data that I get from a customer which shows the locations and the qty of the widget they tested and the qty that failed.  This data comes once a month for the previous month.  I also have a set of data with all the details on each widget that failed and was tested by a field technician, which is provided as the testing is completed.  What I want to do is make sure that the total number of the tested units is equal to the total number reported as failed by the customer.

So I have two tables, as I described above and I am trying to make a query that will just show me line by line the qty tested and the qty reported by the customer.

The customer report table has the location, month being reported, model, input qty, and qty failed.
The details table has each individual widget and all it's failure and test information, among those are the date failed, location, serial number, model, etc.

Can someone show me how to do a simple query to accomplish my task?

And this is only the beginning because there are lots of other things I need to calculate after that.
Elena QuinnAsked:
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
1. Then first you need to tidy up your design here...
Please adopt a standard naming convention...  See these links for some guidance: al

2. Then decide on more intuitive names for your Primary and Foreign keys

3. Establish Referential integrity between the Customer and CustomerReports table.

4. <I can't give you any specific data unfortunately.>
What was asked for was "Sample data", that closely resembles your specific data.
Without this data it is hard to determine if your tables are properly normalized.

5. So as you can see here, ...if your first statement is:
    "I used Access quite a bit a looooong time ago, but now I seem have forgotten everything I've learned."
Then your first mission should be to determine if your design is sound.

6. In a nutshell you can use the Dcount() Function to count the number of specific products, from a specific customer, during a specific time period,
...for both tables and compare the results.

But again, we need to be sure the design is such that this information is easy to determine, ...and that your data is consistent with the design.

Dale FyeCommented:
Would be helpful if you could provide some sample data or at least your data structure for the two tables.
Elena QuinnAuthor Commented:
Here is a screen shot of the fields and relationships.  I can't give you any specific data unfortunately.
Screenshot of fields and relationships
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Elena QuinnAuthor Commented:
The MonthReported in the details and the MonthReported2 in the Customer Reports are both calculated based on the fail date and the reported month respectively.  They both have the format mmm-yy.
Elena QuinnAuthor Commented:
Your question prompted me to examine my primary and foreign keys and that helped.  I didn't have any basically.  So I created a primary key in the main table, and the foreign key in the details table.  Then set the relationship as one-to-many and referential integrity on.  Then my query worked like I expected.
Jeffrey CoachmanMIS LiasonCommented:

Great, ...and just so you don't think that my post was all about "criticizing" your design, was not.

Again, if you are getting "re-acquainted" with Access,...many times the table design takes a back seat to the user interface.

To be sure, you can use Dcount to count values from two tables and compare the two, with something roughly like this expression:
IIF(Dcount("YourField","Table1",[All tree of your criterion here])=Dcount("YourField","Table2",[All tree of your criterion here]),"They Match,"They Do Not match")


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.

All Courses

From novice to tech pro — start learning today.