Need some help A.S.A.P.

Hi Folks,
I'm having a bit of trouble updating my script. I have a
script used for MS Access and I need to transfer it to be
compatible with MS SQL Server. When I run the script it
gives the error
''ODBC driver does not support the requested
properties.''
This is the line of the script
that I have to change to work with access:
''oRS.Open SqlTxt , oConn'' and the sql text is:
''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
Could anybody please help me out in solving this problem?
Cheers!
Hi
Maybe you can conect to the MS SQL database using MS Access? i don't know?
maybe diverent ODBC driver?
I'm still new to this but just trying to gif you some things you may wanna
try.
Greatings,
Crazy Pete
"Will" <anonymous@.discussions.microsoft.com> schreef in bericht
news:2396c01c45ebe$550b2f10$a301280a@.phx.gbl...
> Hi Folks,
> I'm having a bit of trouble updating my script. I have a
> script used for MS Access and I need to transfer it to be
> compatible with MS SQL Server. When I run the script it
> gives the error
> ''ODBC driver does not support the requested
> properties.''
> This is the line of the script
> that I have to change to work with access:
> ''oRS.Open SqlTxt , oConn'' and the sql text is:
> ''Sqltxt = "SELECT MasterServer, Max(JobId) AS MaxJobId
> FROM Jobs GROUP BY MasterServer ORDER BY MasterServer;".
> Could anybody please help me out in solving this problem?
> Cheers!
>
sql

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below i
s
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below
is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328'
,
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, bu
t
I should be able to follow your logic and get rid of those. Thank you so muc
h.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified t
o
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESC
I don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC
|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id = sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id = SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:
> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id => sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC

Need help with Update query to populate new field

Hi folks:

I have added a new field to an established table, and am having trouble figuring out how to populate its values:

Two tables are involved: Jobs and Parts

There is a one-to-one relationship between each JobID and its PartID

Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.

The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:

UPDATE Jobs

SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))

WHERE (JobID = [the job in question])

My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,

Matt

which is the parent table?

|||assuming that the jobs table has a foreign key that links to the parts table, the update query should look like this

UPDATE J
set j.JobPrice = p.PartPrice
from Jobs J
join Parts p on (p.PartID = j.PartID)|||

Thanks for your replies. I don't have any knowledge about foreign keys, etc. I attempted to set one up between the Jobs and Parts tables, but SQL Server Mgt Studio is still throwing errors when I attempt your query, carlop.

Thank you both anyway for throwing a pearl or two before this swine.

m