TonyReid
Posts: 159
|
| Posted: 01/17/2006, 4:40 AM |
|
I am workign with a rather large database and am using SQL to join tables.
This means that my 'Control Source' dropdown has all the fields for all tables.
When I have two fields in different tables with the same name - they both appear in the 'ControlSource' as identical.
For instance.
------------
Employee
------------
ID
Description
------------
Facility
------------
ID
Description
If I do...
Select * from Employee,Facility;
Then the control source will display
ID
ID
Description
Description
Which is annoying.
It would be better to prefix or suffix in brackets - the table name.
Either
Employee.ID
Facility.ID
Employee.Description
Facility.Description
Or
ID (Employee)
ID (Facility)
Description (Employee)
Description (Facility)
_________________
-----------
PHP/indy Game Developer - http://www.AbsoluteBreeze.co.uk |
 |
 |
Walter Kempees
|
| Posted: 01/17/2006, 10:26 AM |
|
Tony, what version CCS are you using?
You post several questions about VQB, I am willing to try and answer them,
but which version?
"TonyReid" <TonyReid@forum.codecharge> schreef in bericht
news:2243cce5ccd3172@news.codecharge.com...
>I am workign with a rather large database and am using SQL to join tables.
>
> This means that my 'Control Source' dropdown has all the fields for all
> tables.
>
>
> When I have two fields in different tables with the same name - they both
> appear in the 'ControlSource' as identical.
>
> For instance.
>
> ------------
> Employee
> ------------
> ID
> Description
>
>
> ------------
> Facility
> ------------
> ID
> Description
>
>
> If I do...
>
> Select * from Employee,Facility;
>
> Then the control source will display
>
> ID
> ID
> Description
> Description
>
> Which is annoying.
>
> It would be better to prefix or suffix in brackets - the table name.
>
> Either
>
> Employee.ID
> Facility.ID
> Employee.Description
> Facility.Description
>
> Or
>
> ID (Employee)
> ID (Facility)
> Description (Employee)
> Description (Facility)
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
TheunisP
Posts: 342
|
| Posted: 01/18/2006, 2:28 AM |
|
T, that statement does not contain a join, and I must say - I can't see why you would want to do it like that - if done right the control source will display identical fields with their correct tables - are you sure you are joining the tables?
|
|
|
|
Walter Kempees
|
| Posted: 01/18/2006, 3:07 AM |
|
Theunis,
He never replied to my Q.
He is building his SQL outside VQB, copying it in.
At least thats what I take from this and the other posting.
He is definitly not using VQB or these Q's qould not be here, notice how VQB
automatically aliases fields when duplicate named?
Walter
"TheunisP" <TheunisP@forum.codecharge> schreef in bericht
news:2243ce186708900@news.codecharge.com...
> T, that statement does not contain a join, and I must say - I can't see
> why you
> would want to do it like that - if done right the control source will
> display
> identical fields with their correct tables - are you sure you are joining
> the
> tables?
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
TonyReid.
|
| Posted: 01/18/2006, 4:05 AM |
|
Hi Walter, Theunis.
Im using CCS3 :)
I realised my mistake last night - I wasnt aliasing the tables.
The above was just an example - here is my real query which works correctly.
SELECT dbo.Bwip_Documentation.PolicyType, dbo.Bwip_Documentation.NumberofCopies, dbo.Bwip_Documentation.DatetoPSO,
dbo.Bwip_Documentation.SentToPSOBy, dbo.Bwip_Documentation.DateSignedbyPSO, dbo.Bwip_Documentation.DatePolicyToClient,
dbo.Bwip_Documentation.SentPolicyToClientBy, dbo.Bwip_Documentation.DateCoverNoteToClient,
dbo.Bwip_Documentation.SentCoverNoteToClientBy, dbo.Bwip_Documentation.Comments, dbo.Bwip_Documentation.DatetoPSOSeal,
dbo.Bwip_Documentation.SentToPSOSealBy, dbo.Bwip_Documentation.DateRecdSeal, dbo.Bwip_Documentation.Responsibility,
dbo.Bwip_Documentation.DocumentationID, Bwip_Employees_3.FirstName AS FN_PTC, Bwip_Employees_3.Extension AS TEL_PTC,
Bwip_Employees_2.FirstName AS FN_STPSO, Bwip_Employees_2.LastName AS LN_STPSO, Bwip_Employees_2.Extension AS EXT_STPSO,
Bwip_Employees_1.FirstName AS FN_STPSOS, Bwip_Employees_1.LastName AS LN_STPSOS, Bwip_Employees_1.Extension AS EXT_STPSOS,
dbo.Bwip_PSO.Name, dbo.Bwip_Documentation.RiskID, Bwip_Employees_3.LastName, Bwip_Employees_4.FirstName AS FN_SCNTC,
Bwip_Employees_4.LastName AS LN_SCNTC, Bwip_Employees_4.Extension AS TEL__SCNTC, dbo.Bwip_Employees.FirstName AS FN_Response,
dbo.Bwip_Employees.LastName AS LN_Response, dbo.Bwip_Employees.Extension AS LN_Tel
FROM dbo.Bwip_Documentation INNER JOIN
dbo.Bwip_PSO ON dbo.Bwip_Documentation.PSOID = dbo.Bwip_PSO.PSOID INNER JOIN
dbo.Bwip_Employees ON dbo.Bwip_Documentation.Responsibility = dbo.Bwip_Employees.EmployeeID INNER JOIN
dbo.Bwip_Employees Bwip_Employees_1 ON dbo.Bwip_Documentation.SentToPSOSealBy = Bwip_Employees_1.EmployeeID INNER JOIN
dbo.Bwip_Employees Bwip_Employees_2 ON dbo.Bwip_Documentation.SentPolicyToClientBy = Bwip_Employees_2.EmployeeID INNER JOIN
dbo.Bwip_Employees Bwip_Employees_3 ON dbo.Bwip_Documentation.SentToPSOBy = Bwip_Employees_3.EmployeeID INNER JOIN
dbo.Bwip_Employees Bwip_Employees_4 ON dbo.Bwip_Documentation.SentCoverNoteToClientBy = Bwip_Employees_4.EmployeeID
I did start out using the SQL in the datasouce - but it became a bit of a headache(as you can see above) - so endedup using the MSSQL snap in to build queries using the view builder(hence the dbo prefix above)
I think I have it working fine now though.
Thanks for sharing your thoughts on this :)
Tony
|
|
|
|
Walter Kempees
|
| Posted: 01/18/2006, 9:49 AM |
|
welcome.
"TonyReid." <TonyReid.@forum.codecharge> schreef in bericht
news:2243ce2f1d67a67@news.codecharge.com...
> Hi Walter, Theunis.
>
> Im using CCS3 :)
>
> I realised my mistake last night - I wasnt aliasing the tables.
>
> The above was just an example - here is my real query which works
> correctly.
>
>
> SELECT dbo.Bwip_Documentation.PolicyType,
> dbo.Bwip_Documentation.NumberofCopies, dbo.Bwip_Documentation.DatetoPSO,
> dbo.Bwip_Documentation.SentToPSOBy,
> dbo.Bwip_Documentation.DateSignedbyPSO,
> dbo.Bwip_Documentation.DatePolicyToClient,
> dbo.Bwip_Documentation.SentPolicyToClientBy,
> dbo.Bwip_Documentation.DateCoverNoteToClient,
> dbo.Bwip_Documentation.SentCoverNoteToClientBy,
> dbo.Bwip_Documentation.Comments, dbo.Bwip_Documentation.DatetoPSOSeal,
> dbo.Bwip_Documentation.SentToPSOSealBy,
> dbo.Bwip_Documentation.DateRecdSeal,
> dbo.Bwip_Documentation.Responsibility,
> dbo.Bwip_Documentation.DocumentationID,
> Bwip_Employees_3.FirstName AS FN_PTC, Bwip_Employees_3.Extension AS
> TEL_PTC,
> Bwip_Employees_2.FirstName AS FN_STPSO,
> Bwip_Employees_2.LastName AS LN_STPSO, Bwip_Employees_2.Extension AS
> EXT_STPSO,
>
> Bwip_Employees_1.FirstName AS FN_STPSOS,
> Bwip_Employees_1.LastName AS LN_STPSOS, Bwip_Employees_1.Extension AS
> EXT_STPSOS,
> dbo.Bwip_PSO.Name, dbo.Bwip_Documentation.RiskID,
> Bwip_Employees_3.LastName, Bwip_Employees_4.FirstName AS FN_SCNTC,
> Bwip_Employees_4.LastName AS LN_SCNTC,
> Bwip_Employees_4.Extension AS TEL__SCNTC, dbo.Bwip_Employees.FirstName AS
> FN_Response,
> dbo.Bwip_Employees.LastName AS LN_Response,
> dbo.Bwip_Employees.Extension AS LN_Tel
> FROM dbo.Bwip_Documentation INNER JOIN
> dbo.Bwip_PSO ON dbo.Bwip_Documentation.PSOID =
> dbo.Bwip_PSO.PSOID INNER JOIN
> dbo.Bwip_Employees ON
> dbo.Bwip_Documentation.Responsibility = dbo.Bwip_Employees.EmployeeID
> INNER
> JOIN
> dbo.Bwip_Employees Bwip_Employees_1 ON
> dbo.Bwip_Documentation.SentToPSOSealBy = Bwip_Employees_1.EmployeeID INNER
> JOIN
> dbo.Bwip_Employees Bwip_Employees_2 ON
> dbo.Bwip_Documentation.SentPolicyToClientBy = Bwip_Employees_2.EmployeeID
> INNER
> JOIN
> dbo.Bwip_Employees Bwip_Employees_3 ON
> dbo.Bwip_Documentation.SentToPSOBy = Bwip_Employees_3.EmployeeID INNER
> JOIN
> dbo.Bwip_Employees Bwip_Employees_4 ON
> dbo.Bwip_Documentation.SentCoverNoteToClientBy =
> Bwip_Employees_4.EmployeeID
>
>
> I did start out using the SQL in the datasouce - but it became a bit of a
> headache(as you can see above) - so endedup using the MSSQL snap in to
> build
> queries using the view builder(hence the dbo prefix above)
>
> I think I have it working fine now though.
>
> Thanks for sharing your thoughts on this :)
>
> Tony
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
|
