converting sql query to work in Access

M

mgm

This query gives me an error in Access because the subqueries return more
than 1 record however in query analyzer with a sql table it works:

Select certexpiry.[organization name] AS OrgName
, '2' as CertVer2
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsLoaded
, '1' as CertVer1
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsLoaded
From CertExpiry
Group by certexpiry.[organization name]


I'm basically rotating a table (view); do you know how i can make this work?

This is how i have it in SQL:

Select OrgName
,C2 = 2
,NewExpDate = (Select expdate From CertExpiry where CertVersion = 2
and OrgName = C.OrgName)
,NewIsActive = (Select IsActive From CertExpiry where CertVersion = 2
and OrgName = C.OrgName)
,NewIsLoaded = (Select IsLoaded From CertExpiry where CertVersion = 2
and OrgName = C.OrgName)
,C1 = 1
,OldExpDate = (Select expdate From CertExpiry where CertVersion = 1
and OrgName = C.OrgName)
,OldIsActive = (Select IsActive From CertExpiry where CertVersion = 1
and OrgName = C.OrgName)
,OldIsLoaded = (Select IsLoaded From CertExpiry where CertVersion = 1
and OrgName = C.OrgName)
From CertExpiry C
Group by OrgName
 
D

Duane Hookom

I expect SQL would give you exactly the same error message. I got this same
error with one of my SQL Server queries this morning. You can change the
subqueries to return the Count([Expiration Date]) etc to get a count for
troubleshooting.

--
Duane Hookom
MS Access MVP


mgm said:
This query gives me an error in Access because the subqueries return more
than 1 record however in query analyzer with a sql table it works:

Select certexpiry.[organization name] AS OrgName
, '2' as CertVer2
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsLoaded
, '1' as CertVer1
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsLoaded
From CertExpiry
Group by certexpiry.[organization name]


I'm basically rotating a table (view); do you know how i can make this work?

This is how i have it in SQL:

Select OrgName
,C2 = 2
,NewExpDate = (Select expdate From CertExpiry where CertVersion = 2
Click to expand...
 
M

mgm

Actually it works in my sql table. Here's some sample data - build this table with the following data and try
the query.

create table CertExpiry
(OrgName varchar(20),CertVersion varchar(20), ExpDate varchar(20), IsActive varchar(20), IsLoaded varchar(20))
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('ARG', '2', '2006-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('ARG', '1', '2004-12-27', 'false' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('MS', '2', '2006-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('MS', '1', '2004-12-27', 'false' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CSCO', '2', '2006-12-27', 'true' , 'false')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CSCO', '1', '2004-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('DELL', '2', '2006-12-27', 'true' , 'false')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CIR', '2', '2006-12-27', 'true' , 'true')

QUERY:

Select OrgName
,V = 2
,NewExpDate = IsNull((Select left(expdate,11) From CertExpiry where CertVersion = 2 and OrgName =
C.OrgName),'--')
,NewIsActive = IsNull((Select IsActive From CertExpiry where CertVersion = 2 and OrgName = C.OrgName),
'--')
,NewIsLoaded = IsNull((Select IsLoaded From CertExpiry where CertVersion = 2 and OrgName = C.OrgName),
'--')
,V = 1
,OldExpDate = IsNull((Select left(expdate, 11) From CertExpiry where CertVersion = 1 and OrgName =
C.OrgName), '--')
,OldIsActive = IsNull((Select IsActive From CertExpiry where CertVersion = 1 and OrgName = C.OrgName),
'--')
,OldIsLoaded = IsNull((Select IsLoaded From CertExpiry where CertVersion = 1 and OrgName = C.OrgName),
'--')
From CertExpiry C
Group by OrgName


thanks.

I expect SQL would give you exactly the same error message. I got this same
error with one of my SQL Server queries this morning. You can change the
subqueries to return the Count([Expiration Date]) etc to get a count for
troubleshooting.

--
Duane Hookom
MS Access MVP


mgm said:
This query gives me an error in Access because the subqueries return more
than 1 record however in query analyzer with a sql table it works:

Select certexpiry.[organization name] AS OrgName
, '2' as CertVer2
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsLoaded
, '1' as CertVer1
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsLoaded
From CertExpiry
Group by certexpiry.[organization name]


I'm basically rotating a table (view); do you know how i can make this work?

This is how i have it in SQL:

Select OrgName
,C2 = 2
,NewExpDate = (Select expdate From CertExpiry where CertVersion = 2
Click to expand...
 
D

Duane Hookom

Are you suggesting that this data and query should or shouldn't work? I
don't think any of the subqueries returns more than a single record. I tried
the records and sql in both Access and SQL Server and they both worked.

Did you have something specific you wanted to prove with the query?

--
Duane Hookom
MS Access MVP


mgm said:
Actually it works in my sql table. Here's some sample data - build this
Click to expand...
table with the following data and try
the query.

create table CertExpiry
(OrgName varchar(20),CertVersion varchar(20), ExpDate varchar(20),
Click to expand...
IsActive varchar(20), IsLoaded varchar(20))
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('ARG', '2', '2006-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('ARG', '1', '2004-12-27', 'false' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('MS', '2', '2006-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('MS', '1', '2004-12-27', 'false' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CSCO', '2', '2006-12-27', 'true' , 'false')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CSCO', '1', '2004-12-27', 'true' , 'true')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('DELL', '2', '2006-12-27', 'true' , 'false')
insert into certexpiry (OrgName, CertVersion, ExpDate, IsActive, IsLoaded)
values ('CIR', '2', '2006-12-27', 'true' , 'true')

QUERY:

Select OrgName
,V = 2
,NewExpDate = IsNull((Select left(expdate,11) From CertExpiry where CertVersion = 2 and OrgName =
C.OrgName),'--')
,NewIsActive = IsNull((Select IsActive From CertExpiry where
Click to expand...
CertVersion = 2 and OrgName = C.OrgName),
'--')
,NewIsLoaded = IsNull((Select IsLoaded From CertExpiry where
Click to expand...
CertVersion = 2 and OrgName = C.OrgName),
'--')
,V = 1
,OldExpDate = IsNull((Select left(expdate, 11) From CertExpiry where CertVersion = 1 and OrgName =
C.OrgName), '--')
,OldIsActive = IsNull((Select IsActive From CertExpiry where
Click to expand...
CertVersion = 1 and OrgName = C.OrgName),
'--')
,OldIsLoaded = IsNull((Select IsLoaded From CertExpiry where
Click to expand...
CertVersion = 1 and OrgName = C.OrgName),
'--')
From CertExpiry C
Group by OrgName


thanks.

I expect SQL would give you exactly the same error message. I got this same
error with one of my SQL Server queries this morning. You can change the
subqueries to return the Count([Expiration Date]) etc to get a count for
troubleshooting.

--
Duane Hookom
MS Access MVP


mgm said:
This query gives me an error in Access because the subqueries return more
than 1 record however in query analyzer with a sql table it works:

Select certexpiry.[organization name] AS OrgName
, '2' as CertVer2
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '2' and certexpiry.[organization name] =
certexpiry.[organization name]) as NewIsLoaded
, '1' as CertVer1
, (Select certexpiry.[Expiration Date] From CertExpiry where
certexpiry.[cert version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldExpDate
, (Select certexpiry.IsActive From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsActive
, (Select certexpiry.IsLoaded From CertExpiry where certexpiry.[cert
version] = '1' and certexpiry.[organization name] =
certexpiry.[organization name]) as OldIsLoaded
From CertExpiry
Group by certexpiry.[organization name]


I'm basically rotating a table (view); do you know how i can make this work?

This is how i have it in SQL:

Select OrgName
,C2 = 2
,NewExpDate = (Select expdate From CertExpiry where CertVersion =
Click to expand...
2
and OrgName = C.OrgName)
,NewIsActive = (Select IsActive From CertExpiry where CertVersion
Click to expand...
=
2
and OrgName = C.OrgName)
,NewIsLoaded = (Select IsLoaded From CertExpiry where CertVersion
Click to expand...
=
2
and OrgName = C.OrgName)
,C1 = 1
,OldExpDate = (Select expdate From CertExpiry where CertVersion = 1
and OrgName = C.OrgName)
,OldIsActive = (Select IsActive From CertExpiry where CertVersion
Click to expand...
=
1
and OrgName = C.OrgName)
,OldIsLoaded = (Select IsLoaded From CertExpiry where CertVersion
Click to expand...
=
1
and OrgName = C.OrgName)
From CertExpiry C
Group by OrgName
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trouble converting SQL subquery to stacked saved queries 4
SQL Server SQL to Access 2003 4
Set Field Value Based on Entry in another field 7
need help!! - running sql statements based on form inputs 8
How to generate a unique list based on more than one field? 4
Syntax Error in Line 1 of Stored Procedure 3
Returning multiple values with SQL query 4
Access 2000 Query very slow compared to SQL Query Analyzer 5

Top