select problems

[steve stevo]

strSearch is a dynamically created search string

Copy of select statement below

sql = "SELECT
Master.Master_K ey,Master.First _App,Original_I nfo_Date,Last_I nfo_Date,Area.A r
ea_Name
AS Area_Name, State_Cntry.Sta te_Cntry_Name, Company.Oper_Na me1 AS
Oper_Name1, Company.Oper_Na me2 AS Oper_Name2, " _
& " Company.Oper_Na me3 AS Oper_Name3, Location.City AS City,
Projects.Projec t_Name AS Project_Name, " _
& " Expansion.Expan sion_Code AS Expansion_Code,
Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost AS
Cost, " _
& " Master.Status AS Status, Master.Estimate d_Year_Complete d
AS Estimated_Year_ Completed " _
& " FROM Master INNER JOIN " _
& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _
& " Company ON Master.Company_ Key = Company.Company _Key INNER
JOIN " _
& " Location ON Master.Location _Key = Location.Locati on_Key
INNER JOIN " _
& " Expansion ON Master.Expansio n_Key =
Expansion.Expan sion_Key INNER JOIN " _
& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _
& " Projects ON Master.Project_ Key = Projects.Projec t_Key
INNER JOIN " _
& " State_Cntry ON Location.State_ Cntry_Key =
State_Cntry.Sta te_Cntry_Key " _
& " WHERE " & strSearch _
& " ORDER BY Area.Area_Key, State_Cntry_Nam e,Oper_Name1,Ci ty"
The table I need to access is called LEC

LEC_Key (PK)
Master_Key - Database Master Key
Company_Key - matches the company
LEC_Type_Key - 3 types per company (values L,E or C)

Each Master Key will have 3 values in the table, one each of L,E and C

Hope this makes some sort of sense

Thanks in advance

Simon Christie

[Simon Hayes]

"steve stevo" <st***@stevoste ve.fsnet.co.uk> wrote in message news:<bn******* ***@news6.svr.p ol.co.uk>...

strSearch is a dynamically created search string

Copy of select statement below

sql = "SELECT
Master.Master_K ey,Master.First _App,Original_I nfo_Date,Last_I nfo_Date,Area.A r
ea_Name
AS Area_Name, State_Cntry.Sta te_Cntry_Name, Company.Oper_Na me1 AS
Oper_Name1, Company.Oper_Na me2 AS Oper_Name2, " _
& " Company.Oper_Na me3 AS Oper_Name3, Location.City AS City,
Projects.Projec t_Name AS Project_Name, " _
& " Expansion.Expan sion_Code AS Expansion_Code,
Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost AS
Cost, " _
& " Master.Status AS Status, Master.Estimate d_Year_Complete d
AS Estimated_Year_ Completed " _
& " FROM Master INNER JOIN " _
& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _
& " Company ON Master.Company_ Key = Company.Company _Key INNER
JOIN " _
& " Location ON Master.Location _Key = Location.Locati on_Key
INNER JOIN " _
& " Expansion ON Master.Expansio n_Key =
Expansion.Expan sion_Key INNER JOIN " _
& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _
& " Projects ON Master.Project_ Key = Projects.Projec t_Key
INNER JOIN " _
& " State_Cntry ON Location.State_ Cntry_Key =
State_Cntry.Sta te_Cntry_Key " _
& " WHERE " & strSearch _
& " ORDER BY Area.Area_Key, State_Cntry_Nam e,Oper_Name1,Ci ty"
The table I need to access is called LEC

LEC_Key (PK)
Master_Key - Database Master Key
Company_Key - matches the company
LEC_Type_Key - 3 types per company (values L,E or C)

Each Master Key will have 3 values in the table, one each of L,E and C

Hope this makes some sort of sense

Thanks in advance

Simon Christie

What is your actual question/problem? By the way, you'll probably get
a better response if your SQL is just SQL, with no VB or other
language around it - that makes it a lot easier to read. Including
CREATE TABLE statements for the relevant tables is also considered
helpful.

Simon

[John Bell]

Hi

Hope this makes some sort of sense

Unfortunately it doesn't! Although you have posted pseudo DDL for your
LEC table it is better to use the scripting options in Enterprise
Manager or Query Analyser to produce actual Create table statements.
Example data as insert statements for each table would be useful,
along with the expected output you require from that data.

Regarding the SELECT statement, it would make it shorter and easier to
read if you used table aliases and removed the unnecessary column
aliases. Not all you columns are pre-fixed with the table name which
could cause you problems.

HTH

John
"steve stevo" <st***@stevoste ve.fsnet.co.uk> wrote in message news:<bn******* ***@news6.svr.p ol.co.uk>... strSearch is a dynamically created search string

Copy of select statement below

sql = "SELECT
Master.Master_K ey,Master.First _App,Original_I nfo_Date,Last_I nfo_Date,Area.A r
ea_Name
AS Area_Name, State_Cntry.Sta te_Cntry_Name, Company.Oper_Na me1 AS
Oper_Name1, Company.Oper_Na me2 AS Oper_Name2, " _
& " Company.Oper_Na me3 AS Oper_Name3, Location.City AS City,
Projects.Projec t_Name AS Project_Name, " _
& " Expansion.Expan sion_Code AS Expansion_Code,
Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost AS
Cost, " _
& " Master.Status AS Status, Master.Estimate d_Year_Complete d
AS Estimated_Year_ Completed " _
& " FROM Master INNER JOIN " _
& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _
& " Company ON Master.Company_ Key = Company.Company _Key INNER
JOIN " _
& " Location ON Master.Location _Key = Location.Locati on_Key
INNER JOIN " _
& " Expansion ON Master.Expansio n_Key =
Expansion.Expan sion_Key INNER JOIN " _
& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _
& " Projects ON Master.Project_ Key = Projects.Projec t_Key
INNER JOIN " _
& " State_Cntry ON Location.State_ Cntry_Key =
State_Cntry.Sta te_Cntry_Key " _
& " WHERE " & strSearch _
& " ORDER BY Area.Area_Key, State_Cntry_Nam e,Oper_Name1,Ci ty"
The table I need to access is called LEC

LEC_Key (PK)
Master_Key - Database Master Key
Company_Key - matches the company
LEC_Type_Key - 3 types per company (values L,E or C)

Each Master Key will have 3 values in the table, one each of L,E and C

Hope this makes some sort of sense

Thanks in advance

Simon Christie