SQL Join Problem 1

[LadyDev]

I have four tables: (all information consolidated into one table named tblAll

TblHouse
House_ID
House_Descr
House_Type
Image_Loc
Loc_ID
Combo_ID

TblLoc
Loc_ID
Location
House_ID

TblOwner
Owner_ID
Owner_Name
Owner_Addr
House_ID

TblCombo
Combo_ID
Combo_Name
House_ID
Loc_ID
Owner_ID

SELECT tblAll.HOUSE_DESCR, tblAll.House_ID, tblAll.House_Type, tblAll.Image_Loc, tblAll.Location, tblAll.Owner_ID, tblAll.Owner_Name, tblAll.Owner_Addr
FROM tblAll
WHERE (((tblAll.COMBO_ID)=[Forms]![frmDocView]![cmboChoice]));

The problem I am having is that the result only displays one house_type per owner and one location – it should display multiple locations and owners per house_type – provided the house has been owned by more than one person.

The SQL is wrong, but where? Help!

 

[LadyDev]

Right, but thid is the SQL I came up with after your initial post. It keep giving a "missing parameter" error.

tblCombo (COMBO_ID, COMBO_NAME)- Names of Parts, Furniture, etc
tblOption (OPTION_ID, OPTION_NAME) - Names of the Option Group; Furniture, Parts, etc

Nothing is physically entered. Option Group selection populates the Combo Box. Combo Box selection, for instance if I select Furniture from the combo box then Furniture Type, Description and Picture are displayed with the corresponding Parts used (except ppicture)to make up that piece of Furniture and vice versa for the Part. If Part is selected the Part Type, Description, and Picture are displayed with the corresponding information (except Picture about the Furniture that Part was used to build. Right, the information to be displayed is based on combo box selection.

 

[lespaul]

I guess I don't understand what the ComboID and OptionID are for? You say that Combo is names of parts and names of furniture, but you have the part_name field in the parts table and the furniture name field in the furntiture table. Do they have more than one name? Please provide some sample data from these tables, thanks.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[LadyDev]

tblOption (OPTION_ID, OPTION_NAME)-- Option Group
001 FURNITURE
002 PARTS
003 ETC
004 ETC
005 ETC

tblCombo (COMBO_ID, COMBO_NAME) -- Combo Box
(If Funiture is chosen from Option Group this list is presented in the Combo box)
001 WESTINGHOUSE
002 O'SULLIVAN
003 BRYANT
004 SIMMONS

If you select 'Simmons' from the Combo Box, the subform should be populated with a name and *picture of the 'Simmons' furniture, description of the 'Simmons' furniture and location of 'Simmons' furniture, and the 2nd subform should populate with the corresponding names of the Furniture that use that 'side panel', description of that Furniture.


(If Parts is chosen from Option Group this list is presented in the Combo box)

005 **SIDE PANEL
006 **SIDE HINGE
007 **BACK PANEL
008 TOP
009 ETC

If you select 'side panel' from the Combo Box, the subform should be populated with a name and *picture of the 'side panel', description of the 'side panel' and location of 'side panel, and the 2nd subform should populate with the corresponding names of the Furniture that use that 'side panel', description of that Furniture.

*Picture is presented elsewhere on the form -- not in the data grid.

** Side Panel, etc has different nonmenclatures. Example: all side panels are group under "SIDE PANEL", but are broken down in the data display grid (Side Panel A, Side Panel B) Different panels are used by same company, but on different pieces of furniture.

***In the next Phase I want to be able to select "Side Panel A, in the subform, and have that further breakdown specific pieces of furniture it's used in (and vice versa)

As it stands now I am just trying to identify which part goes with which piece of furniture and vice versa.

If I could send you a visual of what I have, I beleive it would be less confusing. My techn writing skills and terminology are limited.

Thanks!

 

[lespaul]

Again, you should normalize your database before you go any farther, it's only going to get harder and harder to get the information you want as you want more detail.

If you set up your tables like this:

Furniture:
Furniture_ID
Furniture_Name
Furniture_Desc
Furniture_Pic
Furniture_Location
Manufacturer_ID

Parts:
Parts_ID
Parts_Type
Parts_Description
Parts_Location
Parts_Pic

Furniture_Parts
Furniture_ID
Parts_ID
Qty_Needed

Manufacture_Table
Manufacture_ID
Manufacture_Name
Active (boolean Y/N)??

Then on your form you can use the selection for option to determine what table to query from (if they select parts then you will select from the parts table, if they select furniture, then select from the furniture table) to populate your combo box with the active manufacturers then you can put the source of the combo box to a query:

SELECT DISTINCT MANUFACTURER_NAME FROM MANUFACTURE WHERE ACTIVE = 'Y'

this will give you a list of all the manufacturers that are active.

Once the person has selected the manufacturer then you can run a simple query

SELECT * FROM PARTS INNER JOIN FURN_PARTS ON PARTS.PARTID = FURN_PARTS.PARTID
INNER JOIN FURNITURE ON FURN_PARTS.FURNITUREID = FURNITURE.FURNITUREID
INNER JOIN MANUFACTURER ON FURNITURE.MANUFACTUREID = MANUFACTURE.MANUFACTUREID
WHERE MANUFACTURENAME = [Forms selection]

But queries don't work well when the table is not normalized. I don't think with your existing table structure you will be able to extract any useful information with out recreating the database each time you need dat, like creating the TblAll - it works ok for now, but what happens when there are tons of records in the parts table? your application will get slower & slower.

I would seriously recommend redesigning now.

After drawing a visual of your tables I can't see any way for you to extract the information you want. Sorry.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!