October 29, 2012 at 8:50 am
Hi,
I have two tables (table A and table B), with a relation 1 to n (n being 0 to max 3).
I make a left join on these 2 tables using the primary key-foreign key relation and I need to have every occurrence of each row to be numerically identified. So I use the DENSE_RANK() function on a OVER (PARTITION BY) clause.
My query is like this:
SELECT ab.*, DENSE_RANK() OVER (PARTITION BY ab.pps_date, ab.country, ab.ltcf_id, ab.resident_id, mo.ab_code, mo.ab_route
ORDER BY mo.mo01) AS MOOccurrence, mo.mo01
FROM dbo.PPS_Resident_Antibiotic AS ab LEFT OUTER JOIN
dbo.PPS_Resident_Microorganism AS mo ON ab.pps_date = mo.pps_date AND ab.ltcf_id = mo.ltcf_id AND ab.resident_id = mo.resident_id AND ab.country = mo.country AND ab.abt01 = mo.ab_code AND ab.abt04 = mo.ab_route
My primary key-foreign key is composed of 4 columns. It works perfect in SQL 2008.
I have been asked to produce the same result in MS Access. But of course the OVER clause doesn't work in Access. What is the best approach to produce the same result as qthe query abobe, but in Access?
Thanks in advance for any help.
October 29, 2012 at 9:03 am
pierre.daubresse (10/29/2012)
Hi,I have two tables (table A and table B), with a relation 1 to n (n being 0 to max 3).
I make a left join on these 2 tables using the primary key-foreign key relation and I need to have every occurrence of each row to be numerically identified. So I use the DENSE_RANK() function on a OVER (PARTITION BY) clause.
My query is like this:
SELECT ab.*, DENSE_RANK() OVER (PARTITION BY ab.pps_date, ab.country, ab.ltcf_id, ab.resident_id, mo.ab_code, mo.ab_route
ORDER BY mo.mo01) AS MOOccurrence, mo.mo01
FROM dbo.PPS_Resident_Antibiotic AS ab LEFT OUTER JOIN
dbo.PPS_Resident_Microorganism AS mo ON ab.pps_date = mo.pps_date AND ab.ltcf_id = mo.ltcf_id AND ab.resident_id = mo.resident_id AND ab.country = mo.country AND ab.abt01 = mo.ab_code AND ab.abt04 = mo.ab_route
My primary key-foreign key is composed of 4 columns. It works perfect in SQL 2008.
I have been asked to produce the same result in MS Access. But of course the OVER clause doesn't work in Access. What is the best approach to produce the same result as qthe query abobe, but in Access?
Thanks in advance for any help.
That is like saying I have a race car with excellent performance, now I want to get this same thing from a Yugo. :w00t:
Can you query the sql box from Access? If so, then I would recommend creating a view in sql and either running that as a query or creating a linked table that points to your view in Access.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2012 at 9:20 am
Yes I know it seems strange, but let me explain the context: I'm working for a national institute for Public Health. We want to collect data from long term care facilities, in about 25 european countries. We were asked to develop a .Net Windows application, using .Net 2.0, based on the assumption there is no Internet connection in the facilities. So every facility receives the Windows application and enters data in it. The Windows application stores the data in a local Access database. When facilities are done, they send to us their Access DB (through CD-Rom, USB stick or SharePoint site if they have Internet access).
For the first year of the collect, I imported the Access databases into SQL 2008 in a central DB, and was able to make the queries I needed, using all the stuff from SQL Server 2008. But now I'm asked to bring those queries directly into the Windows application (and therefore into MS Access), if possible. Otherwise we will do the job through .Net code in the Windows application
October 29, 2012 at 9:47 am
pierre.daubresse (10/29/2012)
Yes I know it seems strange, but let me explain the context: I'm working for a national institute for Public Health. We want to collect data from long term care facilities, in about 25 european countries. We were asked to develop a .Net Windows application, using .Net 2.0, based on the assumption there is no Internet connection in the facilities. So every facility receives the Windows application and enters data in it. The Windows application stores the data in a local Access database. When facilities are done, they send to us their Access DB (through CD-Rom, USB stick or SharePoint site if they have Internet access).For the first year of the collect, I imported the Access databases into SQL 2008 in a central DB, and was able to make the queries I needed, using all the stuff from SQL Server 2008. But now I'm asked to bring those queries directly into the Windows application (and therefore into MS Access), if possible. Otherwise we will do the job through .Net code in the Windows application
You could do it in Access but not directly in a query. You would have to do this type of windowing function in VBA. Tooo bad you didn't use SQL Express instead of Access. If you had, you would be able to do this pretty easily.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 29, 2012 at 9:53 am
it gets a little outside of a lot of peoples comfort zones, but in .NET you can do a lot things on the client.. .left join/inner join/full join on the DataTable objects, Select Distinct, and yes, even windowing functions like Dense rank.
here's just one link on Creating the Dense_Rank equivilent in a datatable:
http://stackoverflow.com/questions/11446254/how-to-emulate-sql-partition-by-in-r
Edit:
oops the above really refers to something else than .NET DataTable objects.
I'm searching for a decent implementation of row_number()/ other partitioning at the datatable and will post itwhen i get it.
actually, if you care to switch to the 3.5 framework instead of 2.0, you can use LINQ.
http://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq
Lowell
October 29, 2012 at 10:06 am
Thanks a lot for your help. We'll investigate the use of the DataTable object.
Kind regards,
Pierre
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply