[dba-SQLServer]ADP form based on 1-to-many SQL statement

Arthur Fuller artful at rogers.com
Wed May 7 15:25:10 CDT 2003


Send me the CREATE TABLE statements for the relevant tables off-list and
I'll begin experimenting. Even better would be a few INSERT statements to
populate them with a row or three each.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: May 7, 2003 3:47 PM
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]ADP form based on 1-to-many SQL statement


Further to my note last night - Here is the SQL from the view that I am
using -

SELECT   DISTINCT  tblCustomers.TradingName END AS CustName,
                       tblCustomers.Residential, tblCustICP.ICPNumber, 
tblCustICP.AllocationGrp, tblCustomers.PAddress1, tblCustomers.PAddress2, 
tblCustomers.PSuburb,
                       tblCustomers.PCity, tblCustomers.PPostCode, 
tblCustICP.GateIDNo, tblCustMeter.MeterNo, tblCustMeter.MeterModelIDNo, 
tblCustMeter.Location,
                       tblCustMeter.NoOfDials, tblCustMeter.DigitsRead, 
tblCustMeter.Pressure, tblCustMeter.LastYrConsumption, 
tblCustMeter.DateMeterLastRead,
                       tblCustMeter.OldRetLastMeterRead, 
tblCustMeter.ActualEstimateOld, tblCustMeter.OldRetailMultiplier, 
tblCustMeter.InstallID,
                       tblCustMeter.DateMeterInfoRecd, 
tblCustMeter.LastMeterReadDate, tblCustMeter.LastMeterReading, 
tblCustMeter.MthEstimateUse,
                       tblCustMeter.PressureKPA, 
tblCustMeter.PressureFactor, tblCustMeter.Corrector, 
tblCustMeter.MeterMult, tblCustMeter.ConversionFactor,
                       tblCustMeter.NextMeterReadDate, 
tblCustMeter.ConxnNRDDate, tblCustomers.AccStatus, tblCustomers.InvNumber, 
tblCustomers.CurrentBillDate,
                       tblCustomers.CustomerID
FROM         tblCustomers INNER JOIN
                       (tblCustICP INNER JOIN
                       tblCustMeter ON tblCustICP.ICPID = 
tblCustMeter.ICPIDNo) ON tblCustomers.CustomerID = tblCustICP.CustIDNo

Any suggestions?


Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205  

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list