Need Query for Duplicate Problem

[Al]

I have a table that contains info that I periodically "inherit," meaning I
cannot change the way it is set up. The contains college class schedule
information. Briefly it looks something like this: ClassNumber (like
1234444), Course Prefix (eg ENG), Number (eg 111), Section (eg 02), Credits
(eg 3), Days, Times, Room, Instructor.... Each course has a unique
ClassNumber; however, sometimes a course will meet in more than one room, or
sometimes a course will have a lab. In those instances, the course info is
repeated/duplicated, including the credit associated with the course. What
I need is some sort of query that says if the ClassNumber is repeated, then
set the Credits to zero. Any suggestions would be much appreciated. I am
not a programmer, so couch your responses accordingly. Thanks.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You haven't said why you're trying to do that. If you just need the
credits per class try something like this:

SELECT ClassNumber, Credits
FROM table
GROUP BY ClassNumber, Credits
ORDER BY ClassNumber

Put the above query in a Query's SQL view. Change the table name to
your table name - same w/ column names, if applicable.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk2oj4echKqOuFEgEQK2RgCg5HXMmhFJwj5hNseCxrPca6vagZ8AoIBE
3dvRZoNuFDDNWyF1ijliSQ8Y
=YZII
-----END PGP SIGNATURE-----

 

[Al]

Although I haven't tried your suggestion yet, it doesn't appear that what
you suggest would change the repeated credit value to zero.

My problem is that I may have a course in my table that is CourseNumber 1234
etc ACC 211 Section 02 3 credits MWF 9:00-10:00. That same course (with
the same coursenumber) may have a lab and it reads same coursenumber ACC 211
Section 03 3 credits Tues 10:00-11:00. However the total credits for that
class are three, but because credits are apparantly tied to the course
number, every time it is repeated, the credits are repeated. I need some
mechanism to change the credits to zero when the coursenumber is repeated.

 

[MGFoster]

Although I haven't tried your suggestion yet, it doesn't appear that what
you suggest would change the repeated credit value to zero.

My problem is that I may have a course in my table that is CourseNumber 1234
etc ACC 211 Section 02 3 credits MWF 9:00-10:00. That same course (with
the same coursenumber) may have a lab and it reads same coursenumber ACC 211
Section 03 3 credits Tues 10:00-11:00. However the total credits for that
class are three, but because credits are apparantly tied to the course
number, every time it is repeated, the credits are repeated. I need some
mechanism to change the credits to zero when the coursenumber is repeated.

< SNIP previous posts >

My question is "why do you need to zero out those duplicate credits?"

 

[Al]

Here is why. Example one: a course has a listing for its lecture and
it indicates it is 3 credits. That same class has a lab and its listing
indicates it is also 3 credits. However, the total credits for the
class is 3 credits. Example two. a course has a listing for room 101
and it indicates it is worth 3 credits. That same course may also meet
in adjoining room 102 and the listing for that also indicates 3
credits. However, the total credits for the class is only 3 credits,
not 6.

In the table I inherit, credits a re tied to a course number, so
whenever that coursenumber is repeated (because the course has a
lecture and a lab, or meets in two rooms) the credits are repeated when
they should not be. Hence I'm looking for a way to set credits to zero
when the coursenumber is repeated.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OK. What you really want is to "know" the number of credits per course
number. That is what my first query did.

I you want to display this info in a report you can use the Hide
Duplicates property of a TextBox control on the credits control and on
the course number control. The output will look like this:

Course Number Credits Rm <other columns>
============================================
205 3 101
102
303 2 101
401 3 552
633

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk9ruIechKqOuFEgEQLaZwCffx+Okz8BSV9YsDgwwEEk9G6fcG0An2PA
3MKpNCWq/d6aD9yTl6KvDHTK
=zHpo
-----END PGP SIGNATURE-----

 

[Al]

That was simplicity itself, as long as you know what you are doing. I was
too locked into solving the problem with a query to think about solving it
in a report. Your suggestion works fine. Thanks.

 

[Al]

I spoke altogether too soon. Doing as suggested does solve one problem but
creates another. When the coursenumber would normally be repeated, it is
now hidden. And the credits for the course are listed only once and are not
repeated. This is exactly what I wanted. However, there are now certain
other courses (not all) which have their credits hidden (although not their
coursenumber). These courses do not have repeated coursenumbers, so I don't
know why their credits are hidden. There seems to be no commonality between
those courses where credits are shown and those where they are not shown.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It's because the previous row's Credits equals the current row's
Credits. You will probably have to put a conditional statement in the
Credits TextBox. Actually, set the Visible property of the Credits
control to False. Move the Credits control out of the way. Create
another TextBox control in the same spot where the Credits control was.
Set the ControlSource of the new TextBox to something like this:

=DisplayCredit()

Then create a function like this in the report's VBA module.

Private Function DisplayCredit()

Static intCourse As Integer

If Me![Course Number] <> intCourse Then
intCourse = Me![Course Number]
DisplayCredit = Me!Credits
Else
' Don't show any credits, 'cuz we are
' still on the same course no.
DisplayCredit = ""
End If

End Function

I don't know if this is the "Correct" answer, but, I believe it will
work. Change the intCourse data type to whatever the Course Number data
type is; also, change the names of [Course Number] and Credits to
whatever you are using in the report.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlQwlIechKqOuFEgEQL6SwCg4bY48A8yr6Lpe2KcguPPhU1fkf8AoLlv
K3+w9CXzUiMHApgqJ8Ql15rR
=2L+E
-----END PGP SIGNATURE-----