Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing date parameters from Access form to underlying views

Status
Not open for further replies.

handlebars

Technical User
Feb 18, 2003
270
GB
I have a view made up of 6 underlying views that all need to accept the same date parameters from an Access Form.

What I was thinking was to have one table that holds date values and is updated each time the form is closed (before running the report).

These two values would then make up the between parameters in the WHERE part of the query.

What I am not sure about is how to reference date variables in SQL server.

Can anyone suggest the correct syntax to do this?

Any help would be appreciated.

Andrew
 
Hehe, that's the way that I "worked around" the issue of views not having parameters. Put them in a table and access them via a join or subquery. Well, since the time that I began doing that, I've learned that views, especially nested views, are not a good thing. Consider rewriting the 6 views into 1 SP by using derived tables. If you need help, show us some or all of the views.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
donutman,

Thanks for your reply, you wouldnt belive that i am working for a London Local Authority (IS department of 40) and the only SQL developer could not suggest the best solution!!

I will need your assistence (and will be truely grateful). Here are the underlying views:

CallsLogged

SELECT dbo.request.sys_request_id, dbo.request.sys_requestdate, dbo.analgroup.sys_analgroup, dbo.priority.sys_priority_id
FROM dbo.priority INNER JOIN
dbo.request ON dbo.priority.sys_priority_id = dbo.request.sys_requestpriority INNER JOIN
dbo.analgroup ON dbo.request.sys_assignedtoanalgroup = dbo.analgroup.sys_analgroup
WHERE sys_requestdate BETWEEN @beginningdate and @enddate

CallsLoggedCount

SELECT COUNT(sys_request_id) AS [Calls Logged], sys_priority_id, sys_analgroup
FROM dbo.CallsLogged
GROUP BY sys_priority_id, sys_analgroup
WHERE sys_requestdate BETWEEN @beginningdate and @enddate

There are the two views for: calls resolved, resolved in target and calls still outstanding.

They are then tied together with this view:

SELECT dbo.priority.sys_priority_id, dbo.priority.sys_prioritysla, dbo.CallsLoggedCount.[Calls Logged], dbo.CallsResolvedCount.[Calls Resolved Count],
dbo.ResolvedInTargetCount.[Resolved In Target], dbo.CallsOutstandingCount.[Calls Outstanding Count], dbo.analgroup.sys_analgroup
FROM dbo.priority INNER JOIN
dbo.CallsOutstandingCount ON dbo.priority.sys_priority_id = dbo.CallsOutstandingCount.sys_priority_id INNER JOIN
dbo.CallsResolvedCount ON dbo.priority.sys_priority_id = dbo.CallsResolvedCount.sys_priority_id INNER JOIN
dbo.CallsLoggedCount ON dbo.priority.sys_priority_id = dbo.CallsLoggedCount.sys_priority_id INNER JOIN
dbo.analgroup ON dbo.CallsOutstandingCount.sys_analgroup = dbo.analgroup.sys_analgroup AND
dbo.CallsResolvedCount.sys_analgroup = dbo.analgroup.sys_analgroup AND
dbo.CallsLoggedCount.sys_analgroup = dbo.analgroup.sys_analgroup LEFT OUTER JOIN
dbo.ResolvedInTargetCount ON dbo.analgroup.sys_analgroup = dbo.ResolvedInTargetCount.sys_analgroup AND
dbo.priority.sys_priority_id = dbo.ResolvedInTargetCount.sys_priority_id

If you could point me in the right direction i would much appreciate it.

Thanks in advance

Andrew
 
First let me help with the cheap trick technique of using a table for parameters in a view. Start with your view for CallsLogged:
Code:
[Blue]SELECT[/Blue] P.sys_request_id[Gray],[/Gray]     
       P.sys_requestdate[Gray],[/Gray] 
       P.sys_analgroup[Gray],[/Gray] 
       P.sys_priority_id
   [Blue]FROM[/Blue] dbo.priority P [Blue]INNER[/Blue] [Blue]JOIN[/Blue] dbo.request R
      [Blue]ON[/Blue] P.sys_priority_id [Gray]=[/Gray] R.sys_requestpriority 
   [Blue]INNER[/Blue] [Blue]JOIN[/Blue] dbo.analgroup A
      [Blue]ON[/Blue] R.sys_assignedtoanalgroup [Gray]=[/Gray] A.sys_analgroup
   [Blue]CROSS[/Blue] [Blue]JOIN[/Blue] ReportDateTable RD
   [Blue]WHERE[/Blue] sys_requestdate 
      [Blue]BETWEEN[/Blue] RD.BeginDate [Gray]AND[/Gray] RD.EndDate
This is NOT a multi-user solution. You could make it one if you had to by adding another column to ReportDateTable. In any case you have to update the single row of the RD table with your @BeginDate and @EndDate. The view should then return the desired rows. BTW, the Cross Join could be an Inner Join by changing the Where clause into an ON clause.
The other choice is to make the above a derived table and use it in your final select. Then do your Inner Joins.
Code:
[Gray]([/Gray][Blue]SELECT[/Blue] P.sys_request_id[Gray],[/Gray]     
       P.sys_requestdate[Gray],[/Gray] 
       P.sys_analgroup[Gray],[/Gray] 
       P.sys_priority_id
   [Blue]FROM[/Blue] dbo.priority P [Blue]INNER[/Blue] [Blue]JOIN[/Blue] dbo.request R
      [Blue]ON[/Blue] P.sys_priority_id [Gray]=[/Gray] R.sys_requestpriority [Gray]AND[/Gray]
         sys_requestdate[Gray]>[/Gray]@BeginDate [Gray]AND[/Gray]
         sys_requestdate[Gray]<[/Gray]@EndDate
   [Blue]INNER[/Blue] [Blue]JOIN[/Blue] dbo.analgroup A
      [Blue]ON[/Blue] R.sys_assignedtoanalgroup [Gray]=[/Gray] A.sys_analgroup[Gray])[/Gray] DT1
The reason that I gave you the 1st version is that you might want to check the speed of doing it both ways. I'd be curious about the result of such a test. If you can't play around, just use the 2nd approach.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top