Access Report is creating multiple records from combining combo boxes??

[Casey Sanders]

I have a database set up for parts for automobiles. I have the products in a table and the makes/models set up in another table. I have a another table set up to link the products to each make/model in a many-to-many relationship (products can be for multiple makes/models and makes/models can be linked to many products). I have a report set up to do the following: Group on Make; Group on Model; Group on Start_Date; Sort by Product_Code.

This was working fine until I had to add a sub-category to the Models. So basically, I added a new table for Models_Sub. Take, for instance, an early Alfa Spider...the make is "Alfa Romeo", the model is "Spider (105/115 Series)", the sub-model could be "Series 1: 2000 Veloce", "Series 1: 1750 Veloce", "Series 2: 1300 Junior", etc...there are 13 sub-models for the Alfa Romeo Series 105/115 Spider.

Everything in the Form and Table is working properly for me to enter the information and store it. I altered the report to do the following: Group on Make; Group on Model; Group on Model_Sub; Group on Start_Date; Sort by Product_Code. The expected output would be something similar to this:

Alfa Romeo
Giulietta
(no sub models, so it just starts grouping the start date)
1955-1959
Product 1
Product 2
1960-1962
Product 3
Product 4
Spider 105/115 Series
Series 1: 1600 Duetto
1966-1967
Product 5
Product 6
Series 1: 1750 Veloce
1967-1969
Product 7
Product 8
Series 2: 2000 Veloce
1971-1982


Product 9
Product 10
....and so on.....

...what is actually happening is really weird... in this example, say products 5,6,7,8,9,10 (only the ones associated with a sub-model) are being duplicated for EVERY possible combination of sub-model...in other words, product 5 would be listed under EVERY sub-model and year range associated the model (Spider 105/115 Series). For instance, product 10 (which in this example should only display under Series 2: 2000 Veloce for the date range 1971-1982, will display under all categories listed under Spider 105/115 Series and would end up looking like this:

Alfa Romeo
Giulietta
(no sub models, so it just starts grouping the start date)
1955-1959
Product 1
Product 2
1960-1962
Product 3
Product 4
Spider 105/115 Series
Series 1: 1600 Duetto
1966-1967
Product 5
Product 6
Product 7
Product 8
Product 9
Product 10
Series 1: 1750 Veloce
1967-1969
Product 5
Product 6
Product 7
Product 8
Product 9
Product 10
Series 2: 2000 Veloce
1971-1982
Product 5
Product 6
Product 7
Product 8
Product 9
Product 10
...and so on....

This is only a small scale of what is actually happening... with 30 products total associated within the Spider 105/115 Series (which should be divided between the sub-models), its duplicating them for all 13 sub-models, so instead of 30 products on the report, I have 390....in a database that has thousands of products, this is a nightmare.... can anyone help and let me know what may be going wrong??

[June7]

Not really understanding data structure. Seems to me parts should be associated with the ModelSub.

Do you have one table that associates Make/Model/ModelSub?

Then another table that associates Parts with MakeModelModelSub record?

[Casey Sanders]

I will take a few screenshots of relationships, etc. and try to explain a little better

[Casey Sanders]

...also to note, if I create a new report with the wizard I don't have this problem and no data is duplicated...however, I've spent alot of time formatting and coding, etc. and don't want to completely redo the report if I can avoid it..

[Casey Sanders]

Here is a screenshot of the relationships

[June7]

Examine the wizard generated report. What is the RecordSource it produces? Could it be used in your report?

My suggested table setup would mean redesign by linking tbl_Products_By_model to tbl_Models_Sub. Unless all the subs for each model use the exact same set of parts.

[Casey Sanders]

As you can see in the screenshot above, "tbl_Makes" has a list of all possible makes, "tbl_Models" has a list of all models (where many models can be linked to a single make), "tbl_Models_Sub" is then linked from "tbl_Models" to show there can be many sub-models to each model (but aren't necessary for all)..where every entry on the "tbl_Products_By_Model" must be associated with a Make/Model....it may or may not be a sub-model...sub-models are only used when a series of car needs to be broken down for further detail as described in my initial post.

The report is basically being generated from "tbl_Products_By_Model". It basically states "X" (product) is associated with "Y" (Make/Model or Make/Model/Sub Model) for "Z" (year range) ....it is just being arranged in a user-friendly catalog where if someone is looking to see what we have for their car, they would find the make, scroll down to the model (then sub-model if there is one), look for what year range their car falls into and then it will show what all part numbers are available for their car.

[Casey Sanders]

I can't use the record source because the generated report just uses "tbl_Products_By_Model" and the one I'm using (which I've customized) uses this statement:

"SELECT tbl_Makes.Make, tbl_Models.Model, tbl_Models_Sub.Model_Sub, tbl_Products_By_Model.Start_Date, tbl_Products_By_Model.End_Date, tbl_Products.Product_Code, tbl_Products.Description, tbl_Products.[PP/SS B&W], tbl_Products.[PP/SS CLR], tbl_Products.[SS SP CLR], tbl_Products.[PP SP CLR], tbl_Products.[SF/SR], tbl_Products.[ET/ES], tbl_Products.[SG TW/RP], tbl_Products.SG, tbl_Products.GR, tbl_Products.RO, tbl_Products.A5, tbl_Products.A5A, tbl_Products.[CO/CG], tbl_Products.[EV/BS], tbl_Products.CAB, tbl_Products.[CR B&W], tbl_Products.[TF JX LM], tbl_Products.MC, tbl_Products.TWV, tbl_Products.[LVNT BK], tbl_Products.MISC, tbl_Products.Bow_Height, tbl_Products.Install_Time, tbl_Products.[SGR-Maxbond], tbl_Products.EWP, tbl_Makes.ID AS tbl_Makes_ID, tbl_Models.ID AS tbl_Models_ID FROM (tbl_Products INNER JOIN ((tbl_Makes INNER JOIN tbl_Models ON tbl_Makes.[ID] = tbl_Models.[Make]) INNER JOIN tbl_Products_By_Model ON tbl_Models.[ID] = tbl_Products_By_Model.[Model]) ON tbl_Products.[Product_ID] = tbl_Products_By_Model.[Product_ID]) INNER JOIN tbl_Models_Sub ON tbl_Models.ID = tbl_Models_Sub.Model;"

....after copying & pasting this in, I noticed the "INNER JOIN" statements but do not remember putting those there when I coded this a while back...maybe something access did when I added the new field? ...is this possibly why it is doing what its doing?

[June7]

Will all subs for a model use the same set of parts?

I don't know what is different between the wizard report and your report but a subreport for the subs might be necessary.

If anything, the INNER joins could result in no records retrieved. But try changing the join types and see what happens.

[Casey Sanders]

I'm not sure if I explained the sub-model thing thoroughly... Basically the sub-model is only used to break down the models a little more...so the product would be associated with the sub-model ONLY and not the model and sub-model....same as it was without it...that the product was associated with the Model and not the Make... Take, for instance, the Alfa Romeo make. Below I'm putting a full structure of the Alfas:

Giulietta (750 Series) 1955-1959
Giulietta (101 Series) 1959-1962
Giulia (105 Series) 1962-1963
Giulia (105 Series) 1964-1965
2000 (102 Series- 2 pass) 1958-1959
2000 (102 Series- 2 pass) 1960-1961
2000 (102 Series- 4 pass) 1961
2600 (106 Series) 1962-1968
Spider (105/115 Series)
- Series 1: 1600 Duetto 1966-1967
- Series 1: 1750 Veloce 1967-1969
- Series 1: 1300 Junior 1968-1969
- Series 2: 1750 Veloce 1970-1973
- Series 2: 1300 Junior 1970-1973
- Series 2: 2000 Veloce 1971-1982
- Series 2: 1600 Junior 1972-1981
- Series 3: Aerodinamica 2000 1982-1989
- Series 3: Aerodinamica 1600 1983-1989
- Series 3: Quadrifoglio Verde 1985-1989
- Series 4: Type 4 2000 1990-1993
- Series 4: Type 4 1600 1990-1992
Spider (916 Series) 1995-2006
Spider (939 Series) 2006-2010

...see the last 2 Spiders only had one model for a small year range...whereas the older Spiders are broken down into sub-categories....so, for instance, our part number WC520 is a convertible top for all Series 1 Spiders, so that product would show up for the following:

Spider (105/115 Series)
- Series 1: 1600 Duetto 1966-1967
- Series 1: 1750 Veloce 1967-1969
- Series 1: 1300 Junior 1968-1969

...but it wouldn't specifically be for ALL of the sub-models of "Spider (105/115 Series)" ...which is why I have to break it down into sub-models because some parts are only for a small range and I need to be able to associate them that way

[Casey Sanders]

essentially, the sub-model should take the place of the model if there is a sub-model entered

[June7]

Not all models will have sub models? Parts can't be associated with both, has to be one or the other.

What model doesn't have sub models? Don't they all? Like XT or XLT or whatever?

[Casey Sanders]

UPDATE: So I linked the relationship of tbl_Models_Sub.ID with tbl_Products_By_Model.Model_Sub and now the report seems to be working the way it should. HOWEVER, I now have another problem (which I had before as well, but didn't notice until now). The only records that show in the report are those with sub-models selected. When I click on the Join Properties for these two fields, I have 3 options as seen below. I thought this may be causing it, so I tried #2 and I get the following error when trying to save it....

[Casey Sanders]

Not all models will have sub models? Parts can't be associated with both, has to be one or the other.

What model doesn't have sub models? Don't they all? Like XT or XLT or whatever?

...to answer this question, yes, most of them do...but for our purposes, we don't need to distinguish between XT, XLT, etc....nearly all of our parts will associate with make/model/year range alone....but several do require a sub-model like the Alfas mentioned above.

[June7]

I am suggesting you don't include Make and Model in tbl_Products_By_Model, that you instead only include model sub ID as foreign key. Every model will need a sub record, even if there is only one. The year throws another kink in this. I don't see a field for model year anywhere.