converting from acces frontend to sql queries....help please

[unknown]

We have some sql databases that we have always used access as the
front end and have a lot of queries that work fine.
Now they have decided to convert all this to just sql with no front
end.
I have never tried to convert access queries to sql.
We have queries that might use 5 other queries to compile data.
How do you even start to try and do this?

ps some of these are crosstab queries and I have never seen one in
sql.

no expert I can do a lot in sql but nothing like this

[mcnewsxp]

Post by unknown
We have some sql databases that we have always used access as the
front end and have a lot of queries that work fine.
Now they have decided to convert all this to just sql with no front
end.
I have never tried to convert access queries to sql.
We have queries that might use 5 other queries to compile data.
How do you even start to try and do this?
ps some of these are crosstab queries and I have never seen one in
sql.
no expert I can do a lot in sql but nothing like this

in general - you can pop all those queries into to one stored procedure. just put them in there in the same order that you would call them. do a little googling to see how you can use variables that take values form the query sections as they execute and use them in the next section.

[unknown]

I might have started at the wrong spot.
I was hoping to have 15 or so queries and just cut and paste them I
didn't know that a lot of the commands in access were not supported by
sql



the frist one was more than I could figure out

qry_Medications

SELECT dbo.PatientMedications.PatientID,
dbo.PatientMedications.VisitDate, dbo.Drugs.abbreviation,
dbo.MedicationStatusLookup.Abbreviation
FROM (dbo.PatientMedications LEFT JOIN dbo.Drugs ON
dbo.PatientMedications.DrugID = dbo.Drugs.drugId) LEFT JOIN
dbo.MedicationStatusLookup ON dbo.PatientMedications.StatusID =
dbo.MedicationStatusLookup.StatusLookupID
WHERE (((dbo.Drugs.abbreviation)='abat' Or
(dbo.Drugs.abbreviation)='adal' Or (dbo.Drugs.abbreviation)='allo' Or
(dbo.Drugs.abbreviation)='aur' Or (dbo.Drugs.abbreviation)='aza' Or
(dbo.Drugs.abbreviation)='chlq' Or (dbo.Drugs.abbreviation)='colch' Or
(dbo.Drugs.abbreviation)='ctz' Or (dbo.Drugs.abbreviation)='cya' Or
(dbo.Drugs.abbreviation)='etan' Or (dbo.Drugs.abbreviation)='glm' Or
(dbo.Drugs.abbreviation)='hcq' Or (dbo.Drugs.abbreviation)='i-mtx' Or
(dbo.Drugs.abbreviation)='infl' Or (dbo.Drugs.abbreviation)='lefl' Or
(dbo.Drugs.abbreviation)='mtx' Or (dbo.Drugs.abbreviation)='pred' Or
(dbo.Drugs.abbreviation)='ritux' Or (dbo.Drugs.abbreviation)='ssz' Or
(dbo.Drugs.abbreviation)='tcz'))
ORDER BY dbo.Drugs.abbreviation;

qry_Medications_Crosstab

TRANSFORM Last([dbo.MedicationStatusLookup].[Abbreviation]) AS Expr
SELECT qry_Medications.PatientID, qry_Medications.VisitDate
FROM qry_Medications
GROUP BY qry_Medications.PatientID, qry_Medications.VisitDate
PIVOT qry_Medications.dbo.Drugs.Abbreviation;



============================================
Abbreviation in Drugs table
abbreviation in Medication table

that makes it fun.

and when I saw that transform was not supported an pivot were not
going to work this way I knew I was in over my head.





On Tue, 8 Jan 2013 07:06:48 -0800 (PST), mcnewsxp

Post by mcnewsxp

Post by unknown
We have some sql databases that we have always used access as the
front end and have a lot of queries that work fine.
Now they have decided to convert all this to just sql with no front
end.
I have never tried to convert access queries to sql.
We have queries that might use 5 other queries to compile data.
How do you even start to try and do this?
ps some of these are crosstab queries and I have never seen one in
sql.
no expert I can do a lot in sql but nothing like this

in general - you can pop all those queries into to one stored procedure. just put them in there in the same order that you would call them. do a little googling to see how you can use variables that take values form the query sections as they execute and use them in the next section.

[Bob Barrows]

Post by unknown
I might have started at the wrong spot.
I was hoping to have 15 or so queries and just cut and paste them I
didn't know that a lot of the commands in access were not supported by
sql
the frist one was more than I could figure out
qry_Medications

Nothing here that would give T-SQL any problems. You will need to use CREATE
VIEW in your SQL Server database to create the equivalent of the Access
saved query in your database.
CREATE VIEW qry_Medications AS
/*
same sql that I snipped - except for the ORDER BY clause
which is illegal in views
*/

Post by unknown
qry_Medications_Crosstab
TRANSFORM Last([dbo.MedicationStatusLookup].[Abbreviation]) AS Expr
SELECT qry_Medications.PatientID, qry_Medications.VisitDate
FROM qry_Medications
GROUP BY qry_Medications.PatientID, qry_Medications.VisitDate
PIVOT qry_Medications.dbo.Drugs.Abbreviation;

Look up "pivot" in Books Online. The syntax is different but the outcome is
similar.

[Erland Sommarskog]

Post by unknown
We have some sql databases that we have always used access as the
front end and have a lot of queries that work fine.
Now they have decided to convert all this to just sql with no front
end.

No front end? Are end users going to use Managment Studio or what?

Post by unknown
ps some of these are crosstab queries and I have never seen one in
sql.

So here is the scoop: writing a crosstab query in standard SQL is not
a problem - as long as you know what columns you will produce. A result
set in SQL (and I mean SQL in general, not just T-SQL) is a table, and
a table has a fixed number of columns with a fixed data type. This is
not strange at all, since each column is supposed to model a unique
attribute.

Say that you want to show sales per month for a year per product and
region. This the way to do it:

SELECT ProductID, Region,
Jan = SUM(CASE WHEN datepart(MONTH, SaleDate) = 1 THEN Amt END),
Feb = SUM(CASE WHEN datepart(MONTH, SaleDate) = 2 THEN Amt END),
...
Dec = SUM(CASE WHEN datepart(MONTH, SaleDate) = 12 THEN Amt END)
FROM Sales
WHERE SaleDate >= '20120101'
AND SaleDate < '20130101'
GROUP BY ProductID, Region

In this example I assume that you needed to sum anyway, but if you just want
to display you can use MIN or MAX. Everything else is the same.

Bob Barrows mentioned the PIVOT keyword, but I recommend that you stay
away from it. For an example like the above, the code gets a little
shorter, but it is less flexible. For instance, if you want to add a
total column for the entire year, this is a no-brainer with the example
above. Furthermore, the above pattern works on any DBMS, while PIVOT
is proprietary to SQL Server.

However, in many cases, you don't know beforehand how many columns or what
the names of the columns will be. Access has a solution for this in the
TRANSFORM keyword. There is no built-in solution for this in SQL Server,
and it is a concept that is entirely non-relational.

It pops up in the forums about daily, and a search on "Dynamic Crosstab"
will give you both good and bad solutions. I have some examples and a
stored procedeure (that I got from SQL Server MVP Itzik Ben-Gan) here:
http://www.sommarskog.se/dynamic_sql.html#Crosstab

However, as I said, this is non-relational, and there is reason to put
this in the frontend if possible. I am told that SSRS (SQL Server Reporting
Services) has it built-in, but I am not an SSRS guy myself.

[unknown]

what I tried was this

with basequery as(
SELECT dbo.PatientMedications.PatientID as IDnum,
dbo.PatientMedications.VisitDate as VDate, dbo.Drugs.abbreviation as
drugA, dbo.MedicationStatusLookup.Abbreviation as medA
FROM (dbo.PatientMedications LEFT JOIN dbo.Drugs ON
dbo.PatientMedications.DrugID = dbo.Drugs.drugId) LEFT JOIN
dbo.MedicationStatusLookup ON dbo.PatientMedications.StatusID =
dbo.MedicationStatusLookup.StatusLookupID
WHERE (((dbo.Drugs.abbreviation)='abat' Or
(dbo.Drugs.abbreviation)='adal' Or (dbo.Drugs.abbreviation)='allo' Or
(dbo.Drugs.abbreviation)='aur' Or (dbo.Drugs.abbreviation)='aza' Or
(dbo.Drugs.abbreviation)='chlq' Or (dbo.Drugs.abbreviation)='colch' Or
(dbo.Drugs.abbreviation)='ctz' Or (dbo.Drugs.abbreviation)='cya' Or
(dbo.Drugs.abbreviation)='etan' Or (dbo.Drugs.abbreviation)='glm' Or
(dbo.Drugs.abbreviation)='hcq' Or (dbo.Drugs.abbreviation)='i-mtx' Or
(dbo.Drugs.abbreviation)='infl' Or (dbo.Drugs.abbreviation)='lefl' Or
(dbo.Drugs.abbreviation)='mtx' Or (dbo.Drugs.abbreviation)='pred' Or
(dbo.Drugs.abbreviation)='ritux' Or (dbo.Drugs.abbreviation)='ssz' Or
(dbo.Drugs.abbreviation)='tcz'))
)
select
abat,adal,allo,aur,aza,chlq,colch,ctz,cya,etan,glm,hcq,[i-mtx],infl,lefl,mtx,pred,ritux,ssz,tcz
FROM basequery
PIVOT (count(medA) for IDnum in
([abat],[adal],[allo],[aur],[aza],[chlq],[colch],[ctz],[cya],[etan],[glm],[hcq],[i-mtx],[infl],[lefl],[mtx],[pred],[ritux],[ssz],[tcz]))
as abbrevi


it works but will not give them what they want.

they have records such as

patientid Visitdate Drug Type DrugFlag
0000000 01/01/2001 abat N
0000000 01/01/2001 ssz T


they are wanting
patientid abat cya ssz
0000000 N T


a pivot must be an aggrigate and will not use first or last so this
runs but only produces 0 in all positions but I might have something
else wrong.



thanks for your help this is more than I expected (sure wish I could
have just cut and pasted) lol


On Tue, 08 Jan 2013 21:38:15 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by unknown
We have some sql databases that we have always used access as the
front end and have a lot of queries that work fine.
Now they have decided to convert all this to just sql with no front
end.

No front end? Are end users going to use Managment Studio or what?

Post by unknown
ps some of these are crosstab queries and I have never seen one in
sql.

So here is the scoop: writing a crosstab query in standard SQL is not
a problem - as long as you know what columns you will produce. A result
set in SQL (and I mean SQL in general, not just T-SQL) is a table, and
a table has a fixed number of columns with a fixed data type. This is
not strange at all, since each column is supposed to model a unique
attribute.
Say that you want to show sales per month for a year per product and
SELECT ProductID, Region,
Jan = SUM(CASE WHEN datepart(MONTH, SaleDate) = 1 THEN Amt END),
Feb = SUM(CASE WHEN datepart(MONTH, SaleDate) = 2 THEN Amt END),
...
Dec = SUM(CASE WHEN datepart(MONTH, SaleDate) = 12 THEN Amt END)
FROM Sales
WHERE SaleDate >= '20120101'
AND SaleDate < '20130101'
GROUP BY ProductID, Region
In this example I assume that you needed to sum anyway, but if you just want
to display you can use MIN or MAX. Everything else is the same.
Bob Barrows mentioned the PIVOT keyword, but I recommend that you stay
away from it. For an example like the above, the code gets a little
shorter, but it is less flexible. For instance, if you want to add a
total column for the entire year, this is a no-brainer with the example
above. Furthermore, the above pattern works on any DBMS, while PIVOT
is proprietary to SQL Server.
However, in many cases, you don't know beforehand how many columns or what
the names of the columns will be. Access has a solution for this in the
TRANSFORM keyword. There is no built-in solution for this in SQL Server,
and it is a concept that is entirely non-relational.
It pops up in the forums about daily, and a search on "Dynamic Crosstab"
will give you both good and bad solutions. I have some examples and a
http://www.sommarskog.se/dynamic_sql.html#Crosstab
However, as I said, this is non-relational, and there is reason to put
this in the frontend if possible. I am told that SSRS (SQL Server Reporting
Services) has it built-in, but I am not an SSRS guy myself.

[Bob Barrows]

Post by unknown
select

abat,adal,allo,aur,aza,chlq,colch,ctz,cya,etan,glm,hcq,[i-mtx],infl,lefl,mtx
,pred,ritux,ssz,tcz

Post by unknown
FROM basequery
PIVOT (count(medA) for IDnum in

([abat],[adal],[allo],[aur],[aza],[chlq],[colch],[ctz],[cya],[etan],[glm],[h
cq],[i-mtx],[infl],[lefl],[mtx],[pred],[ritux],[ssz],[tcz]))

Post by unknown
as abbrevi
it works but will not give them what they want.
they have records such as
patientid Visitdate Drug Type DrugFlag
0000000 01/01/2001 abat N
0000000 01/01/2001 ssz T
they are wanting
patientid abat cya ssz
0000000 N T
a pivot must be an aggrigate and will not use first or last so this
runs but only produces 0 in all positions but I might have something
else wrong.

Yes, First and Last don't exist, but Max and Min do.

[unknown]

Thanks big time to everyone

with basequery as(
SELECT dbo.PatientMedications.PatientID as
IDnum,dbo.PatientMedications.VisitDate as VDate,
dbo.Drugs.abbreviation as drugA,
dbo.MedicationStatusLookup.Abbreviation as medA
FROM (dbo.PatientMedications LEFT JOIN dbo.Drugs ON
dbo.PatientMedications.DrugID = dbo.Drugs.drugId) LEFT JOIN
dbo.MedicationStatusLookup ON dbo.PatientMedications.StatusID =
dbo.MedicationStatusLookup.StatusLookupID
WHERE (((dbo.Drugs.abbreviation)='abat' Or
(dbo.Drugs.abbreviation)='adal' Or (dbo.Drugs.abbreviation)='allo' Or
(dbo.Drugs.abbreviation)='aur' Or (dbo.Drugs.abbreviation)='aza' Or
(dbo.Drugs.abbreviation)='chlq' Or (dbo.Drugs.abbreviation)='colch' Or
(dbo.Drugs.abbreviation)='ctz' Or (dbo.Drugs.abbreviation)='cya' Or
(dbo.Drugs.abbreviation)='etan' Or (dbo.Drugs.abbreviation)='glm' Or
(dbo.Drugs.abbreviation)='hcq' Or (dbo.Drugs.abbreviation)='i-mtx' Or
(dbo.Drugs.abbreviation)='infl' Or (dbo.Drugs.abbreviation)='lefl' Or
(dbo.Drugs.abbreviation)='mtx' Or (dbo.Drugs.abbreviation)='pred' Or
(dbo.Drugs.abbreviation)='ritux' Or (dbo.Drugs.abbreviation)='ssz' Or
(dbo.Drugs.abbreviation)='tcz'))
)
select *
FROM basequery
PIVOT (min(medA) for drugA in
([abat],[adal],[allo],[aur],[aza],[chlq],[colch],[ctz],[cya],[etan],[glm],[hcq],[i-mtx],[infl],[lefl],[mtx],[pred],[ritux],[ssz],[tcz]))
as abbrevi
order by IDnum


this worked like I needed on the output

000000 2002-06-28 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL O NULL NULL
NULL N O NULL NULL NULL
000000 2002-11-12 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2002-11-15 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2002-11-19 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL N NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2002-11-22 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL O NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2003-01-08 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL O NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2003-02-12 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL O NULL O NULL NULL
NULL O O NULL NULL NULL
000000 2003-03-11 00:00:00 NULL NULL NULL NULL NULL
NULL NULL NULL NULL O NULL O NULL NULL
NULL O O NULL NULL NULL

NOW I need to use this output in another query that was calling this
crosstab in access.

Can I just use this in a view and name it bob then call bob in the
other query?




On Wed, 9 Jan 2013 10:22:39 -0500, "Bob Barrows"

Post by unknown

Post by unknown
select

abat,adal,allo,aur,aza,chlq,colch,ctz,cya,etan,glm,hcq,[i-mtx],infl,lefl,mtx
,pred,ritux,ssz,tcz

Post by unknown
FROM basequery
PIVOT (count(medA) for IDnum in

([abat],[adal],[allo],[aur],[aza],[chlq],[colch],[ctz],[cya],[etan],[glm],[h
cq],[i-mtx],[infl],[lefl],[mtx],[pred],[ritux],[ssz],[tcz]))

Post by unknown
as abbrevi
it works but will not give them what they want.
they have records such as
patientid Visitdate Drug Type DrugFlag
0000000 01/01/2001 abat N
0000000 01/01/2001 ssz T
they are wanting
patientid abat cya ssz
0000000 N T
a pivot must be an aggrigate and will not use first or last so this
runs but only produces 0 in all positions but I might have something
else wrong.

Yes, First and Last don't exist, but Max and Min do.

[Bob Barrows]

Post by unknown
Can I just use this in a view and name it bob then call bob in the
other query?

No reason not to, just don't name it "bob" ;-)

[unknown]

too late ROFL
have about 3/4 of this stuff done and working

select SELECT PatientID, VisitDate,
(case when [bob] IS NULL then 0 else [bob] end+
case when [fred] IS NULL then 0 else [fred] end)/3

as carl

JUST KIDDING this is a short example of a massive query
MADE UP NAMES for example

the only problem is bob and fred are variables from another view
and carl should show something like 6.66666
I get 6

in the new column, named carl is it auto defined as char or ?
is there a way to set it to float or real

tried declare but it still shows 6

On Wed, 9 Jan 2013 14:26:55 -0500, "Bob Barrows"

Post by Bob Barrows

Post by unknown
Can I just use this in a view and name it bob then call bob in the
other query?

No reason not to, just don't name it "bob" ;-)

[Bob Barrows]

You need to use CAST or CONVERT in the query that does the calculation. By
the time you are looking at the output, it's too late.

Post by unknown
too late ROFL
have about 3/4 of this stuff done and working
select SELECT PatientID, VisitDate,
(case when [bob] IS NULL then 0 else [bob] end+
case when [fred] IS NULL then 0 else [fred] end)/3
as carl
JUST KIDDING this is a short example of a massive query
MADE UP NAMES for example
the only problem is bob and fred are variables from another view
and carl should show something like 6.66666
I get 6
in the new column, named carl is it auto defined as char or ?
is there a way to set it to float or real
tried declare but it still shows 6
On Wed, 9 Jan 2013 14:26:55 -0500, "Bob Barrows"

Post by Bob Barrows

Post by unknown
Can I just use this in a view and name it bob then call bob in the
other query?

No reason not to, just don't name it "bob" ;-)

[Erland Sommarskog]

Post by unknown
NOW I need to use this output in another query that was calling this
crosstab in access.
Can I just use this in a view and name it bob then call bob in the
other query?

Yes, but don't.

As I said, a dynamic pivot is non-relational. Does not this mean that a
static pivot is relational? Not really, but you are not breaking the
fundamental rule that a table has a fixed set of columns. But you are
breaking the rule that each column represents a unique entity.

Access and SQL Server are two very different products. What works well in
Access, may not work well in SQL Server.

As a general rule, the pivoting should always be the last step in your
computations. Keep data row-based as long as you can.

And stay away from the PIVOT keyword, and do the pivoting the way I
showed you. The day you need to mix different kinds of sums etc PIVOT
will break down. With CASE in aggregates, you will not even see the problem.

[unknown]

Good point I can do the pivot on the final query I think.
I will give it a shot when I get all the other tables created.
all this to save 2 minutes running it in sql instead of access.
ouch

I don't want to think of what will be involved when they want to
change something.
hope I don't get in on it.
I have never seen this before just here put this in sql.
I ain't that good LOL
and when I find variables in access called FUNCTION I get scared.
how many reserve words have I found. 3 so far.


On Wed, 09 Jan 2013 22:03:05 +0100, Erland Sommarskog

Post by Erland Sommarskog

Post by unknown
NOW I need to use this output in another query that was calling this
crosstab in access.
Can I just use this in a view and name it bob then call bob in the
other query?

Yes, but don't.
As I said, a dynamic pivot is non-relational. Does not this mean that a
static pivot is relational? Not really, but you are not breaking the
fundamental rule that a table has a fixed set of columns. But you are
breaking the rule that each column represents a unique entity.
Access and SQL Server are two very different products. What works well in
Access, may not work well in SQL Server.
As a general rule, the pivoting should always be the last step in your
computations. Keep data row-based as long as you can.
And stay away from the PIVOT keyword, and do the pivoting the way I
showed you. The day you need to mix different kinds of sums etc PIVOT
will break down. With CASE in aggregates, you will not even see the problem.