Help retrieving data correctly and displaying correctly

I need to pull information from three tables which would generally not be a problem.



I need to pull these fields from the Dealer_Profile table

Dealer_Number

Dealer_Name

Address1

City

State

Zip

Phone

Fax

Mfg_Id





Manufacturer2 table -- joins dealer profile table on the mfg_id

ManufactureID

ManufactureName



The data is kinda 'whack' to say it nicely as I don't believe the tables are fully relational. My issue is that the database structure was designed wrong in the first place over 10 years ago.



I would like one row per Dealer if possible, even if they have more than one manufacturer (which most of them do) Possible?





Desired Layout which is my question at hand:



Dealer Info....,Manf1, Manf2, Manf3...





Make sense? Any thoughts?

Here is an example that came from the Dealer Profile table:





600132 77 Bunton Adams Outdoor Power Equipment 2103 W Rte 120 McHenry IL 60050 815-385-0434 815-385-0453

600132 94 Ariens Adams Outdoor Power Equipment 2103 W Rte 120 McHenry IL 60050 815-385-0434 815-385-0453



So, this dealer offers two manufacturers: 77 --Bunton and 94 --Ariens)





So, I would want to display it on one record if possible such as:



600132 77 Bunton 94 Ariens Adams Outdoor Power Equipment 2103 W Rte 120 McHenry IL 60050 815-385-0434 815-385-0453





Is that possible?

So you can have a dealer with one manufacturer:

(Chapman motors sells Dodge trucks)



Or you can have a dealer that sells various manufacturers:

(Mark Mitsubishi, Mark Kia, Mark Hyundai, etc)



like that?



But for the "Mark" motor company, you want one entry in your dealer table, right?



Sounds like a perfect opportunity for a many-to-many relationship and introducing a third table.



If, for example, our dealer table had dealers:



and our manufacturer table had makers:



you would introduce a table to store your dealer_manufacturer relationships like this:





In order to query this table and get results of all dealers and offered manufacturers as a single rowset, I'm not sure how such a query would be constructed.



I think it could only return each dealer_manufacturer combination as a row the way I described.



To get what you ideally want, you will probably have to add the columns to your dealer table and be limited to the number of columns you define in that table. Searches based on manufacturer will be much more cumbersome that way.