using where clause without specified criteria

Question

1.00/5 (1 vote)

See more:

i have a Access database containing the following:

VB

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

column headings: School, Campus, AdminNo, ModuleCode, ModuleGrp

for the module code i wanna retrieve the distinct data. I know how to obtain distinct data using select distinct
i want to put into datagridview with 2 columns, moduleCode and AdminNo
the output should be like:

ModuleCode AdminNo
EG3901---12344Y
-----------23456H

EG3902---64738U
-----------99393J
-----------63738B

.
.
.

im aware of setting criteria like country=mexico
but right now, i cant list out all the ModuleCodes as the data varies as the file changes, and there are over 30k records.

Thanks!

Posted 2-Jul-13 17:14pm

12345_abcde

Add a Solution

Comments

ArunRajendra 3-Jul-13 0:13am

You have not mentioned what is the issue?

12345_abcde 3-Jul-13 0:29am

if i were to use where clause, how should i put it instead of specifying modulecode=EG3901

_Damian S_ 3-Jul-13 0:36am

Where are you getting the modulecode for the where clause from? On a form? From the user?

12345_abcde 3-Jul-13 5:04am

From access database. should say more to retrieving of data.

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)

Bernhard Hiller · Answer 1 · 2013-07-02T21:16:00

Solution 1

DISTINCT is the key word to use, and it can work without any WHERE clause.

SQL

SELECT DISTINCT ModuleCode, AdminNo
FROM MYTABLE
ORDER BY ModuleCode ASC, AdminNo ASC

Posted 2-Jul-13 21:16pm

Bernhard Hiller

Comments

12345_abcde 3-Jul-13 4:51am

hi thanks for that, after adding on to you code, im able to get something close to what i want, but i want:

EG1001 s1
-blank- s2
-blank- s3

EG1002 S2
-blank- S4

now my program is giving me:
EG1001 S1
EG1001 S2
EG1001 S3

how do i amend it?

Bernhard Hiller 3-Jul-13 9:19am

Replacing those values by blanks is something to be done in the VB program, not in SQL. When you iterate thru the resultset, just check if the modulecode is different from the previous one, then leave it, otherwise replace it with string.Empty

12345_abcde 4-Jul-13 2:34am

i tried with this code:
Dim i As Integer

For i = 0 To dgvModStud.Rows.Count - 1
If dgvModStud.Rows(i).Cells(0).Value = True Then
dgvModStud.Rows(i).Cells(0).Value = String.Empty
Else
Continue For

End If
Next

but it doesnt work, why is it so? where's my error?