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

[Casey Sanders]

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.

...that makes sense and is very doable.....the only thing that concerns me there is that we would like to have our catalog separate the sub-models....for example, have a sub-section (group) on the report that contains all the sub-models...would we still be able to separate that info on the report? ....like I showed with the Alfas? where it would break into a new section or group that states the following are all Alfa Romeo 105/115 Series Spiders? ...if not, it may be simpler to just make all of the sub-models regular models and have the sub-model info in the model name, like this:

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
Spider (105/115 Series) Series 1: 1750 Veloce 1967-1969
Spider (105/115 Series) Series 1: 1300 Junior 1968-1969
Spider (105/115 Series) Series 2: 1750 Veloce 1970-1973
Spider (105/115 Series) Series 2: 1300 Junior 1970-1973
Spider (105/115 Series) Series 2: 2000 Veloce 1971-1982
Spider (105/115 Series) Series 2: 1600 Junior 1972-1981
Spider (105/115 Series) Series 3: Aerodinamica 2000 1982-1989
Spider (105/115 Series) Series 3: Aerodinamica 1600 1983-1989
Spider (105/115 Series) Series 3: Quadrifoglio Verde 1985-1989
Spider (105/115 Series) Series 4: Type 4 2000 1990-1993
Spider (105/115 Series) Series 4: Type 4 1600 1990-1992
Spider (916 Series) 1995-2006
Spider (939 Series) 2006-2010


...as far as the year, that is entered on "tbl_Products_By_Model" ...since it is for a year range and not a single year, I have it as "Start_Date" and "End_Date" ....so essentially, This product fits this Make/Model between Year A and Year B ....and I have a separate table called "tbl_Years", which has all years in a list from 1900-2013 and I only use this to populate the combo boxes on the form so I can select a year instead of typing it

[June7]

Looks like Series should be another field in the sub model table. That field would then be available as grouping criteria. Trying to build too much information into the sub model name. It is easier to combine fields in queries than to parse a single field into discreet data.

I originally suggested in post 2 a single table that relates Make/Model/ModelSub data and then link that table to tbl_Products_By_Model. In that structure would not save ID values from lookup tables but instead save the actual descriptive text (Ford, Dodge, Romeo, Focus, Dakota, Spider). Eliminates joins to lookup tables in queries.

[Casey Sanders]

Do you mean have the tables set up the way they are and create another table in between linking all the data from the 3 that are already there? Or do you mean, create a new table with the fields "ID, Make, Model, Model_Sub", then link that single table to "tbl_Products_By_Model" (deleting the 3 original tables "tbl_Makes", "tbl_Models", "tbl_Models_Sub") ....I'm not sure if the latter will work the way I have the form set up to enter the data...basically on the product entry form, you enter the product info and there is a sub form where you select the make/model/year from linked combo boxes

[Casey Sanders]

Here is another shot of the relationships

[June7]

Yes, I mean one table and I expect form setup would be different.

Could still have separate tables as sources for names but they would not have any relationships. Those tables would simply be sources for combobox lists as an aid to creating the Make/Model/ModelSub/Series records to assure consistency in spelling. Or have one table as source of names. It would have two fields: Category (Make, Model, ModelSub, Series), ItemName. Or do what I do for one of my forms, the data table is it's own source for combobox lists. In this option the comboboxes are not limited to list and entry is free typing so spelling is not tightly controlled.

Once this all-in-one table is populated probably won't have to visit it often for adding records - maybe once a year for new models. This all-in-one table will become the table that has the associations of Make/Model/ModelSub/Series entities and would link to tbl_Products_By_Model.

[Casey Sanders]

OK, I was able to get my initial issue resolved. Since I already had so much completed creating the forms, reports, etc., a complete re-design wasn't really an option for me (due to time restraints for the company I work for). The other reason why I needed to keep those tables separate but linked is because I have to attach images and detail information that will need to be included in the report (For example detailed installation instructions or a warning that may effect an entire make, model or sub-model. Example would be if all of one particular model needed a precise measurement for the part, etc.) However, I did want to post the outcome in case it may help someone in the future. The problem seemed to be in how the relationships were drawn for the report query (my database relationships have not changed). So I unlinked tbl_Makes, tbl_Models and tbl_Model_Subs from each other, but kept their links to tbl_Products_By_Model. I will post a screen shot of my database relationships and the report query relationships to show the difference. Once this was done, everything seemed to work properly and everything is showing on the report as I need it to.

I do thank you for all of your help and quick responses and really feel I couldn't have gotten this fixed without your insight. I love the site and have gotten alot of help here without even having to post and this will certainly be the first place I return to for help in the future! Thank you for everything!

Database Relationships:


Report Query Relationships:


...and this is the generated SELECT statement that works:

SELECT tbl_Products_By_Model.Product_ID, tbl_Products_By_Model.Start_Date, tbl_Products_By_Model.End_Date, tbl_Makes.Make, tbl_Models.Model, tbl_Models_Sub.Model_Sub, tbl_Products.Product_Code, tbl_Products.Description, tbl_Products.Bow_Height, tbl_Products.Install_Time, tbl_Products.[SGR-Maxbond], tbl_Products.EWP FROM tbl_Models INNER JOIN (tbl_Models_Sub INNER JOIN (tbl_Makes INNER JOIN (tbl_Products INNER JOIN tbl_Products_By_Model ON tbl_Products.[Product_ID] = tbl_Products_By_Model.[Product_ID]) ON tbl_Makes.ID = tbl_Products_By_Model.Make) ON tbl_Models_Sub.ID = tbl_Products_By_Model.Model_Sub) ON tbl_Models.ID = tbl_Products_By_Model.Model;

[Casey Sanders]

I also forgot to note a tweak needed to make it work (that was your idea).... Once I fixed the relationships, and set everything up as described above, the only records showing on the report were ones that had Sub-Models....so I had to create an entry in tbl_Models_Subs for ALL MODELS leaving the last column (Model_Sub) blank. Now on my form, once I select Start_Date, End_Date, Make and Model, I MUST select the sub-model (even if none truly exists, I just select the blank one created)