Looping Access database table

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 844 Views - Last Post: 02 July 2013 - 01:27 AM Rate Topic: -----

#1 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Looping Access database table

Posted 01 July 2013 - 06:39 PM

hi, i have a access database containing the following:

SEG,AMK,101427Y,EG3902,EG3902-G1
SEG,AMK,101427Y,EGS650,EGS650-G1
SEG,AMK,103852S,EG3901,EG3901-G1
SEG,AMK,103852S,EG3904,EG3904-G1
SEG,AMK,103852S,EGS104,EGS104-G1
SEG,AMK,103852S,EGS650,EGS650-G1
SEG,AMK,106581C,EG3901,EG3901-G1
SEG,AMK,106581C,EG3902,EG3902-G1
SEG,AMK,111713M,EG3901,EG3901-G1
SEG,AMK,111713M,EG3902,EG3902-G1

i wanna loop to get: example, EG3091 - 103852S
- 106581C
- 111713M

Means a list with Distinct Module codes and the students taking the module to come out like the example i listed.

i have retrieved the distinct module list from my database using SQL command.

anyone teach me how should i go about doing it?

Can i use For loop? if yes, how can i say if the student matches the module?

Thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: Looping Access database table

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3331
  • View blog
  • Posts: 11,275
  • Joined: 12-December 12

Re: Looping Access database table

Posted 01 July 2013 - 06:58 PM

It would be helpful if you gave your columns field-names (I assume there are 5 fields?). This will help you to describe your question, and for us to be able to discuss it with you. For example, what is a Module? What column represent a Student?

But your description is unclear to me, particularly as EG3091 doesn't appear in your table. I know, of course, that you meant EG3901, but it still isn't clear what you want to achieve.

Added: I guess you are looking for the items in column 3 based on a search for a code from column 4? If so, use a WHERE clause in your sql-statement.

BTW You've mentioned distinct several times but, based on your sample data, the values obtained from the 3rd column will be distinct/unique for a particular value in the 4th column.

This post has been edited by andrewsw: 01 July 2013 - 07:01 PM

Was This Post Helpful? 0
  • +
  • -

#3 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 07:13 PM

Hi thanks for the reply. My data are in columns with heading: School,Campus,AdminNo(which is students No),ModuleCode(also SubjectCodes),ModuleGroup

i wanted it to be like, example, in the 4th column i have this module code EG3901
and i have students which is column 3, taking the module.
i want the output to be :
EG3901-Student1,student2,student3

displaying in datagridview.
Thanks!

This post has been edited by andrewsw: 01 July 2013 - 07:16 PM
Reason for edit:: No need to quote the full previous post

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3331
  • View blog
  • Posts: 11,275
  • Joined: 12-December 12

Re: Looping Access database table

Posted 01 July 2013 - 07:18 PM

So what did you find out about the WHERE clause?
Was This Post Helpful? 0
  • +
  • -

#5 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 07:21 PM

View Postandrewsw, on 01 July 2013 - 07:18 PM, said:

So what did you find out about the WHERE clause?


haven look into it yet. i dont really which method to approach ):
Am really stuck.
i will go look into the where clause now.

View Postandrewsw, on 01 July 2013 - 07:18 PM, said:

So what did you find out about the WHERE clause?



erm, but my connection is OleDB
Was This Post Helpful? 0
  • +
  • -

#6 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 07:36 PM

View Postandrewsw, on 01 July 2013 - 07:18 PM, said:

So what did you find out about the WHERE clause?


Sub DisplayCustomersForRegion(ByVal customers As List(Of Customer),
ByVal region As String)

Dim customersForRegion = From cust In customers
Where cust.Region = region

For Each cust In customersForRegion
Console.WriteLine(cust.CompanyName)
Next
End Sub

i found this example codes, but i dont really understand it. sorry, im a very beginner in vb.
but i have understand that the where clause is filtering of data and select those that meet certain criteria.
im coding under a button, so can i Dim customers as list(of customers) instead of byval? and the As list(columnName)??
Appreciate for your help!
Was This Post Helpful? 0
  • +
  • -

#7 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Looping Access database table

Posted 01 July 2013 - 08:54 PM

this link will tell you about where clause example
http://www.w3schools...l/sql_where.asp
this for how to show data in datagridview if u have any problem u can ask here
http://social.msdn.m...-sql-with-vbnet

This post has been edited by vks.gautam1: 01 July 2013 - 08:57 PM

Was This Post Helpful? 0
  • +
  • -

#8 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 09:07 PM

View Postvks.gautam1, on 01 July 2013 - 08:54 PM, said:

this link will tell you about where clause example
http://www.w3schools...l/sql_where.asp
this for how to show data in datagridview if u have any problem u can ask here
http://social.msdn.m...-sql-with-vbnet


Hi, Thanks for that.
i've been reading the where clause and i sort of understand it.
but now i dont wanna list out the module code and select the students matching it, comes out. as i have over 30k records.
i wanna write the where clause statement to search and populate itself.
but i dont know how should i go about writing it. couldnt find any examples.
Was This Post Helpful? 0
  • +
  • -

#9 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Looping Access database table

Posted 01 July 2013 - 09:23 PM

you want to seacrh data by typing from text box.
this is simple example ok
q="select * from table1 where username='"&textbox1.text&"'


Was This Post Helpful? 0
  • +
  • -

#10 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 11:19 PM

View Postvks.gautam1, on 01 July 2013 - 09:23 PM, said:

you want to seacrh data by typing from text box.
this is simple example ok
q="select * from table1 where username='"&textbox1.text&"'



not searching for data. my data are in access database
My data are in columns with heading: School,Campus,AdminNo(which is students No),ModuleCode(also SubjectCodes),ModuleGroup

i wanted it to be like, example, in the 4th column i have this module code EG3901
and i have students which is column 3, taking the module.
i want the output to be :
EG3901-Student1
-student2
-student3

displaying in datagridview.
Thanks!
Was This Post Helpful? 0
  • +
  • -

#11 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Looping Access database table

Posted 01 July 2013 - 11:39 PM

i don't know what is wrong but where condition can fulfill your given criteria. can you show us what you have tried so far ?
Was This Post Helpful? 0
  • +
  • -

#12 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 01 July 2013 - 11:49 PM

View Postvks.gautam1, on 01 July 2013 - 11:39 PM, said:

i don't know what is wrong but where condition can fulfill your given criteria. can you show us what you have tried so far ?


i've written this.
''Connections
Dim Conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=J:\segdata.accdb;")
Dim Cmd As New OleDbCommand

Cmd.CommandText = "SELECT [ModuleCode, AdminNo] FROM [table4]"
Dim ModuleStudents = From stud In AdminNo




Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()

i dont know how to continue.
Was This Post Helpful? 0
  • +
  • -

#13 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Looping Access database table

Posted 02 July 2013 - 12:50 AM

ur query is not what result you want and there is no coding you are doing to populate your data in datagridview.
use this link . only difference is to use Oledb in place of SQL client given in example.
http://social.msdn.m...-sql-with-vbnet

This post has been edited by vks.gautam1: 02 July 2013 - 01:08 AM

Was This Post Helpful? 0
  • +
  • -

#14 fanglign  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 48
  • Joined: 24-June 13

Re: Looping Access database table

Posted 02 July 2013 - 01:02 AM

View Postvks.gautam1, on 02 July 2013 - 12:50 AM, said:

ur query is not what result you want and there is no coding you are doing to populate your data in datagridview.
use this link . only difference is to use Oledb in place of SQL client given in example.


now my problem is i dont know got to code for where clause to populate the data i want. cause all the examples i read was specifying the criteria like country=Mexico
and now i cant list all the module codes out as the data varies as the file changes, and there are over 30k records.

i dont know what should i put in instead of listing the module codes.
Appreciate for your help
Was This Post Helpful? 0
  • +
  • -

#15 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Looping Access database table

Posted 02 July 2013 - 01:20 AM

View Postfanglign, on 02 July 2013 - 01:02 AM, said:

View Postvks.gautam1, on 02 July 2013 - 12:50 AM, said:

ur query is not what result you want and there is no coding you are doing to populate your data in datagridview.
use this link . only difference is to use Oledb in place of SQL client given in example.


now my problem is i dont know got to code for where clause to populate the data i want. cause all the examples i read was specifying the criteria like country=Mexico
and now i cant list all the module codes out as the data varies as the file changes, and there are over 30k records.

i dont know what should i put in instead of listing the module codes.
Appreciate for your help

how you want to select particular " module code"? where you will input your module code to retrieve particualr "module code"
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2