Microsoft Access Coding

Joseph S
Joseph S used Ask the Experts™
First time working with Access. I have a form that has 176k records. Basically is actually 9810 records 545x18. Then the data is being multiple again by another table is 176k. There 18 locations that have 545 records each. The selection form has 2 combo box and a list box. The first combo you choose a region, then it cascades to the second combo box to show locations, and last it cascades from the 2nd combo to the list more specific locations  to choose. When I click the submit button to go on to new form or page it filters from 176k to 9810, when it suppose to filter to 545. Is what I did so far

Is there any codes that someone can create as an example?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

The problem is with the tables.  They are not related or not related correctly.  When I looked at your schema, you had two SEPARATE 1-m relationships in the same query.  If you remove one of the tables, you will see that the filter reduces the data to the expected row count.

Perhaps you will be able to understand the results by creating a sample data base that has data for only ONE of the 18 "somethings" in all the tables.  Then open the query in DS view and manually apply the three filters.  When you look at the remaining records, you might be able to see what I am talking about.  Just because three tables all have one common field, doesn't mean that a query that contains the three tables will make any sense.  In my opinion, you need to separate this data into two queries, one for each set of data.

An example of this that everyone should be able to relate to is - Students take classes and students have vehicles.  All three tables contain StudentID but if you join these tables, you end up with "duplication"
The three tables are:
Joe, Moped
Joe, Car
Pat, Car
Pat, Bicycle
Pat, Pickup
Elvis, Car
Elvis, Limo
Joe, Math
Joe, English
Joe, Biology
Pat, Math
Pat, Access
Elvis, Singing
Elvis, Guitar

Joe, Moped, Math
Joe, Moped, English
Joe, Moped, Biology
Joe, Car, Math
Joe, Car, English
Joe, Car, Biology
Pat, Car, Math
Pat, Car, Access
Pat, Bicycle, Math
Pat, Bicycle, Access
Pat, Pickup, Math
Pat, Pickup, Access
Elvis, Car,Singing
Elvis, Car,Guitar
Elvis, limo,Singing
Elvis, Limo,Guitar

Notice that the query results do not give an accurate picture of either the classes or the vehicles.  This is what you are trying to do and that is why there appears to be "duplication".


@PatHartman, Thank you for the example. I found out what was causing the multiplication. It is the table with the practices, without that table the data gets filter to 545. Can I create a query just with just the practices in it?
Distinguished Expert 2017

Yes.  Just display that data on a separate form since each form can have only one RecordSource.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial