Solved

how do i write a query in MSAccess/SQL/Oracle

Posted on 2003-12-04
8
259 Views
Last Modified: 2013-12-25
how do i write a query in MSAccess/SQL/Oracle to get  
RecordNumber/SrNo along with fields of one/multiple table

for example if my query returns Name & Age of person from table, i need Counting i.e. SrNo in a seprate field
SrNo   Name      Age
1        Austin     25
2        Kalis        45
3        Peter       35
0
Comment
Question by:nirmal_4194
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9873146
1 12/04/2003 20 how do i write a query in MSAccess/SQL/O...  Unlocked VB Databases
2 11/11/2003 20 how to convert bmp file into PDF format ...  Unlocked Visual Basic
3 11/11/2003 20 sending email through VB  Unlocked Visual Basic
4 11/11/2003 20 how do i restrict user not to type more ...  Unlocked Visual Basic
5 11/01/2003 20 How do i print contents of multiline ric...  Unlocked Visual Basic
6 11/01/2003 125 how i can print data from mshflexgrid wi...  Unlocked Visual Basic
0
 
LVL 27

Expert Comment

by:Dabas
ID: 9873234
Hi nirmal_4194:
Please finalise your other questions.
If you have not received reasonable answers, consider increasing the number of points.
(Suggested value of present question: 200 points)

Dabas
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9873292
agree with Dabas :)
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 29

Expert Comment

by:leonstryker
ID: 9874305
I as well.
0
 

Author Comment

by:nirmal_4194
ID: 10024844
Yes, it was my mistake. now i have closed down all the qusetions except this one.
Can any one help me. I am increasing points too...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10024865
what do you mean by record number ?

in a database table there isn't  the concept of a physical sequence (normally exposed to the user anyway)

tell us what your table names are and the relationships between them...

 the Database systems you are interested in can be very different to actually write SQL for...

are you just wanting to interface to MS SQL Server and Oracle Databases from an MS Access Front end Database?
0
 

Author Comment

by:nirmal_4194
ID: 10051079
there is two table named Port_Master, Country_Master

Port_Code    Port_Name        Country_Code
AEDXB         DUBAI              AE
INMUM        MUMBAI            IN
USNYK         NEW YORK        US


Country_Code      Country_Name
AE                       U.A.E
IN                        INDIA
US                       U.S.A

Now I need port name along with country, and also serial number or record number i.e.

SrNo          Port_Name          Country_Name
1               DUBAI                U.A.E
2               MUMBAI              INDIA
3               NEW YORK          U.S.A

i need srno as a seprate field and not physical sequence number.
b'coz i need to write this query in my VB application to retrive data in recordset and i will assign this recordset to MSHFlexgrid.Datasource property.

I hope now you will get exact idea about my need.

thanks
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 75 total points
ID: 10057614
the general method in one statement would be

select count(*) as srno,P.Port_name, Country_name
from Port_master as P inner Join Port_Master as p1
on p.Port_code >= p1.port_code      -- this should be a >= test on the primary Key of the Port_master Table...
inner join country_master as C
on p.country_code = c.country_code
group by p.port_name,countru_name
order by 1 asc
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month6 days, 15 hours left to enroll

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question