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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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")


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.