How to have a below SQL Query in MS-Access2007 ?

Question

0.00/5 (No votes)

See more:

Hi,
Well. Iam doing a small project in C# - MsAccess-2007 Database. And I wish to
convert the below SQL Query in MS-Access2007. Any guidences will be very much
helpful.

SQL

select * from
(select row_number() over(partition by bill_no,bill_type order by line_no) as rn,* from billmst) t where t.rn = 1

The above query useful to select an each single row by bill_no from various bill details in billmst

Thanks for the helps

Posted 14-Nov-14 6:03am

Paramu1973

Add a Solution

Comments

Herman<T>.Instance 17-Nov-14 11:21am

If each bill has a unique bill_no, then all rows would be returned as result. Or do you keep sending over order to the same custmer with the same bill_no?

Herman<T>.Instance 17-Nov-14 11:23am

why not use LocalDB for this project? free, light and you don't have to change your query...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Nielsen - getCore · Answer 1 · 2014-11-18T03:07:00

Solution 1

Easiest if you have access97 is to open the adp or the medb file and add a view/ query then setup your query and switch to sql view :)

Posted 18-Nov-14 3:07am

Thomas Nielsen - getCore

Comments

Herman<T>.Instance 18-Nov-14 9:10am

Do you think the part 'select row_number() over(partition by bill_no,bill_type order by line_no)' might be understood then by Access?

Thomas Nielsen - getCore 18-Nov-14 9:13am

No but there is a pretty good WYSIWYG query editor, just add tables and click to form your query, then base your second on that other query and then switch to sql view and you will let MSAccess write the access sql for you, which is practical