SQL Statment Help Needed

Q

Query Help

I have 22011 records in table "tblProcChargCounty " when I create a query
between this table and tblFacility table, I have 22011 records, but I try to
add "tblEquipType" table I get bigger number. Now table tblEquipType &
tblProcChargCounty have one-to-Many relationship with tbleFacility table.

I need help with this please

SELECT CODE, OpenFacility, FACILITY, FacilityType, COUNTY
FROM tblFacility
UNION All
Select Year, BeginningDate, EndingDate, County AS tblProcChargCounty_County,
PETProc
FROM tblProcChargCounty

SELECT Code, EquipType, [Replacement/Upgrade], [Active?], DateAquired,
[Owned/Leased], [Fixed/Mobile], [Shared?]
FROM tblEquipType;
 
K

KARL DEWEY

It appears that tblProcChargCounty joins tblFacility on COUNTY but I do not
see a common field to join tblEquipType. How is it related?
 
Q

Query Help

All tables are join to tblfacility on Code, even tblEquipType.

KARL DEWEY said:
It appears that tblProcChargCounty joins tblFacility on COUNTY but I do not
see a common field to join tblEquipType. How is it related?

--
KARL DEWEY
Build a little - Test a little


Query Help said:
I have 22011 records in table "tblProcChargCounty " when I create a query
between this table and tblFacility table, I have 22011 records, but I try to
add "tblEquipType" table I get bigger number. Now table tblEquipType &
tblProcChargCounty have one-to-Many relationship with tbleFacility table.

I need help with this please

SELECT CODE, OpenFacility, FACILITY, FacilityType, COUNTY
FROM tblFacility
UNION All
Select Year, BeginningDate, EndingDate, County AS tblProcChargCounty_County,
PETProc
FROM tblProcChargCounty

SELECT Code, EquipType, [Replacement/Upgrade], [Active?], DateAquired,
[Owned/Leased], [Fixed/Mobile], [Shared?]
FROM tblEquipType;
Click to expand...
Click to expand...
 
K

KARL DEWEY

Use a LEFT JOIN on CODE.
SELECT tblEquipType.Code, tblEquipType.EquipType,
tblEquipType.[Replacement/Upgrade], tblEquipType.[Active?],
tblEquipType.DateAquired, tblEquipType.[Owned/Leased],
tblEquipType.[Fixed/Mobile], tblEquipType.[Shared?],
tblFacility.OpenFacility, tblFacility.FACILITY, tblFacility.FacilityType,
tblFacility.COUNTY, tblProcChargCounty.Year,
tblProcChargCounty.BeginningDate, tblProcChargCounty.EndingDate,
tblProcChargCounty.County AS tblProcChargCounty_County,
tblProcChargCounty.PETProc
FROM (tblEquipType LEFT JOIN tblFacility ON tblEquipType.Code =
tblFacility.CODE) LEFT JOIN tblProcChargCounty ON tblEquipType.Code =
tblProcChargCounty.CODE;

--
KARL DEWEY
Build a little - Test a little


Query Help said:
All tables are join to tblfacility on Code, even tblEquipType.

KARL DEWEY said:
It appears that tblProcChargCounty joins tblFacility on COUNTY but I do not
see a common field to join tblEquipType. How is it related?

--
KARL DEWEY
Build a little - Test a little


Query Help said:
I have 22011 records in table "tblProcChargCounty " when I create a query
between this table and tblFacility table, I have 22011 records, but I try to
add "tblEquipType" table I get bigger number. Now table tblEquipType &
tblProcChargCounty have one-to-Many relationship with tbleFacility table.

I need help with this please

SELECT CODE, OpenFacility, FACILITY, FacilityType, COUNTY
FROM tblFacility
UNION All
Select Year, BeginningDate, EndingDate, County AS tblProcChargCounty_County,
PETProc
FROM tblProcChargCounty

SELECT Code, EquipType, [Replacement/Upgrade], [Active?], DateAquired,
[Owned/Leased], [Fixed/Mobile], [Shared?]
FROM tblEquipType;
Click to expand...
Click to expand...
Click to expand...
 
Top