Variables inside a Query (Still unsolved)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Variables inside a Query (Still unsolved)

Author

Topic

glpita
Starting Member

17 Posts

Posted - 2009-09-18 : 16:31:10

Hello,

I have a general MS Access query that deals with a dB with columns(variables) such as [Date of Birth, Income, Health Expenditures, Visits to Doctor,...]. The query is working fine (thanks to help from this forum!) but I need to organize the results according to many combinations of those variables, i.e.:
a) Health Expenditure related to DOB and Visits to Dr
b) Visits to Dr based on Age and State,
c) etc

How can I avoid creating many similar queries changing variables names to get each one of the combinations?... Is there a way to declare variables or to create like a "query-function" to be called many times from a general query?

Thanks a lot...

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-21 : 11:29:00

Try using GROUP BY, also ROLLUPS will give you more combinations of details. Look into them both if you haven't already :)

You can declare temp tables or a variable to hold the data from one of your columns while you work with it. Or you could use a derived table, but these dont seem like they are what you are asking for.

If you need any person to person help rather than reading a guide let me know.

[ /fail at humor]

glpita
Starting Member

17 Posts

Posted - 2009-09-21 : 14:29:14

Hey Winterh,

I guess that those 2 commands are not accepted in MS Acess SQL... I'd be glad to accept your help

thx

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-22 : 11:27:08

I also have a query that is general use (also with much help from folks here!). I have up to 6 different parameters that can be selected.
My where cluse looks like:
WHERE (ED_Employee_HelpDesk.Rn_Descriptor = @ClosedBy OR @ClosedBy = ' ')
AND (ED_Employee.Rn_Descriptor = @EnteredBy OR @EnteredBy = ' ')
AND (ED_Employee_TeamLeader.Rn_Descriptor = @TeamLeader OR @TeamLeader = ' ')
AND (ED_Region.Rn_Descriptor = @Region OR @Region = ' ')
AND (ED_Branch.Rn_Descriptor = @Branch OR @Branch = ' ')
AND (ED_C_Department.Rn_Descriptor = @Dept OR @Dept = ' ')

So I can pass any of the 6 OR a blank will select all. I Select many fields from the same tables for the various needs.

John

"The smoke monster is just the Others doing barbecue"

glpita
Starting Member

17 Posts

Posted - 2009-09-23 : 16:04:44

Hey John,

I guess I've made a mistake applying your code because the program doesn't recognize the Emp.Rn_Descriptor variable (the one I want to change). See below:

SELECT DISTINCT Emp_Id, Emp.Year_Age, Emp.Rn_Descriptor, Inf.Diseases
FROM Database_1 AS Emp
LEFT OUTER JOIN Database_2 AS Inf
ON Emp.Emp_Id = Inf.Infid
WHERE (Emp.Rn_Descriptor = 'XXXXX') AND
(Emp.Year_Age BETWEEN 35 AND 45) AND Inf.Diseases <> ""
ORDER BY Emp_Id;

Any ideas?? thx

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-24 : 10:48:03

Sorry, I was using my table and column names as an example (Emp.Rn_Descriptor). What is the column name in table Emp that you want to select on?

John

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-24 : 10:52:14

If it was Emp.Name the resulting line would be:
WHERE (Emp.Name = @Name OR @Name=' ')
That way is you selected a particular name, it would return that record but if you left it blank it would return all records.

John

glpita
Starting Member

17 Posts

Posted - 2009-09-24 : 11:09:57

John,

Sorry. I'm not getting it. I want to have the freedom that instead of the field Emp.LastName I can choose, for example: MiddleName. I write as follows but then I get a message like "Enter Parameter Value @LastName". I'm using MS Access SQL.... What am I doing wrong?
Thanks

SELECT DISTINCT Emp_Id, Emp.Year_Age, Emp.LastName, Inf.Diseases
FROM Database_1 AS Emp
LEFT OUTER JOIN Database_2 AS Inf
ON Emp.Emp_Id = Inf.Infid
WHERE (Emp.LastName = @LastName OR @LastName='MiddleName') AND
(Emp.Year_Age BETWEEN 35 AND 45) AND Inf.Diseases <> ""
ORDER BY Emp_Id;

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-24 : 11:44:02

Unfortunately I thought the original intent was to have multiple fields that a user might supply a variable so that was what my example was trying to solve.

Based upon your first post. A possible WHERE clause would have been
WHERE (Income <@ Income)
AND (Age <= @LessThanAge)
AND (Age >= @GreaterThanAge)
AND (Visits >= @Visits)
AND (State = @State)
(your fields are Income, Age, Visits and State)
If you wanted to allow the user to select only some, all or none of the parameters.
You would have to declare and accept the variables (in this example) @Income, @LessThanAge, @GreaterThanAge, @Visits and @State
If the user did NOT supply a value, your application should send a blank for each parameter not used.
You would then change your WHERE clause to be:

WHERE ((Income <@ Income) OR (@Income = ' '))
AND (Age <= @LessThanAge) OR (@LessThanAge = ' '))
AND (Age >= @GreaterThanAge) OR (@GreaterThanAge = ' '))
AND (Visits >= @Visits) OR (@Visits = ' '))
AND (State = @State) OR (@State = ' '))

So now your user could say I want all people with incomes greater than 50,000 and living in OH
Or they could say Age is less than 50 with more that 6 visits
Or they could just say Older that 21
Or they could say Give me everything (leave all blank)

If you want to force them to enter a valid entry, just remove the OR (@parm =' ') part.
That was what I thought you were getting at.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources