UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Retrieve Value From SQL Table In SQL View, Any Versions    
 
   
leannemurphy
post Aug 1 2020, 03:41 PM
Post#1



Posts: 14
Joined: 28-March 19



Hi guys,

I have an Access database that uses a SQL Server Azure backend database. I have a lookup table with columns FinancialYear, StartDate and EndDate. And in another table I have a list of orders that each have an OrderDate. How do I retrieve the financial year of the order based on the order date within my orders view?

Any ideas?

Thanks,
Leanne
Go to the top of the page
 
RJD
post Aug 1 2020, 04:47 PM
Post#2


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi Leanne: I don't know if this works with your SQL Server Azure backend (I don't have that setup to test), but it works in Access with Access tables in my test file. You might try this and see. There are other approaches as well. If this does not work with your configuration, let us know and perhaps someone with your configuration can join the conversation.

CODE
SELECT tblOrders.OrderNumber, tblOrders.OrderDate, tblFYDateRange.FinancialYear
FROM tblOrders LEFT JOIN tblFYDateRange ON (tblOrders.OrderDate >= tblFYDateRange.StartDate) And (tblOrders.OrderDate <= tblFYDateRange.EndDate);

Change, of course, to match your table names.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
leannemurphy
post Aug 1 2020, 04:52 PM
Post#3



Posts: 14
Joined: 28-March 19



Thanks RJD, but it needs to be done within a SQL Server view. I'm a strong Access developer, but unfortunately porting that knowledge to SQL is not possible, as SQL is so different.
Go to the top of the page
 
RJD
post Aug 1 2020, 04:57 PM
Post#4


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Right, I was afraid of that, Leanne. I do not work with SQL Server Azure, so am of no help with this. Perhaps someone else can join this and alter the approach to accommodate your configuration.

Best of luck with this. I will follow to see what progress you make, for my own edification.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Aug 1 2020, 05:44 PM
Post#5



Posts: 3,848
Joined: 27-February 09



Do you have a Calendar table in your Azure database?

Something like this should work...

CODE
SELECT soh.SalesOrderID
    , soh.OrderDate
    , fc.FinancialYear
FROM Sales.SalesOrderHeader soh
INNER JOIN tempdb.dbo.FinancialCalendar fc
ON soh.OrderDate = fc.CalendarDate


You could join to your view just fine... I would probably make a copy of the view and then just modify the copy - add the Calendar table, and away you go.
Go to the top of the page
 
cheekybuddha
post Aug 2 2020, 05:25 AM
Post#6


UtterAccess Moderator
Posts: 13,120
Joined: 6-December 03
From: Telegraph Hill


>> Thanks RJD, but it needs to be done within a SQL Server view <<

I don't quite understand this statement. A view is just a stored query on your server.

Joe's SQL should give you the result you need:
CODE
CREATE VIEW dbo.vwOrdersWithFY AS
  SELECT
    o.OrderNumber,
    o.OrderDate,
--  Other fields from tblOrders if required
    fy.FinancialYear
  FROM tblOrders o
  LEFT JOIN tblFYDateRange fy
         ON o.OrderDate >= fy.StartDate
        AND o.OrderDate <= fy.EndDate
;

--------------------


Regards,

David Marten
Go to the top of the page
 
leannemurphy
post Aug 2 2020, 05:29 AM
Post#7



Posts: 14
Joined: 28-March 19



cheekybuddha - RJD's solution was within an Access query. But I need the logic within my SQL Server backend view. I don't want Access to do the processing, I want SQL Server to do it and present to me as a view/table in Access.

Regarding your solution below - it looks very interesting and could very well be my solution! Thank you!
Go to the top of the page
 
cheekybuddha
post Aug 2 2020, 06:38 AM
Post#8


UtterAccess Moderator
Posts: 13,120
Joined: 6-December 03
From: Telegraph Hill


>> RJD's solution was within an Access query <<

Ah OK!

I read it differently: that he was just giving you the SQL to use in creating the view.

Keep us posted with your progress.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Aug 2 2020, 10:25 AM
Post#9


UA Admin
Posts: 37,630
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

A view in SQL Server is, as pointed out, the same thing as a query in Access. The exact syntax can vary due to differences in the way the two dialects of SQL. However, the basic structures parallel one another. In addition, views can be considerably more complex and robust. Of course, the advantage here is that performance is also likely to be better, although that's not guranteed.

One of the difference we encounter when working with views in SQL Server as opposed to queries in Access is that in SQL Server it is much more common to script out the whole create process--which is what David did--whereas in Access it is much more common to build queries in the Query Grid, which is a graphical interface that actually generates the SQL behind the scenes.

Another important point hinted at here is how one goes about using the views in an Access relational database application front end. You link to view almost exactly as you do tables. The main difference to be alert to is that Access may or may not recognize a Primary Key, or unique identifier field when linking to views. If that's the case, and if you want a view to be updateable from within Access, you have to designate the PK for such views during the linking process.



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Aug 2 2020, 11:11 AM
Post#10


UtterAccess VIP
Posts: 2,472
Joined: 4-June 18
From: Somerset, UK


PMFJI as well
One caveat to George's very helpful comments.
If, as I do, you use DSN-less connections, it isn't possible to assign the view PK field when linking views.
However, it is possible to do so afterwards.
I remember having a long discussion here about this issue with Albert Kallal (aren't they always 😏) and if it helps, I will try to find the link or the code I used to do this from Access.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:06 AM