Data Duplication - Data needs to be seperated by term - Please help - See text box for code

[jfranklin72106]

I have attached a sample data sheet in Excel so you can see what the data is doing. Also have attached in Word pictures of the design view so you can see how I imputed the parameters for the query. Below is the SQL code.

I am wanting to pull data for specific students about courses taken in a particular year and term with certain prefixes. What I am getting is duplication between every term needed. The sample student attached has taken 8 courses at one or two per term. I am needing Access to pull out the particular term and year that a student enrolled in a specific course. Also, the data only shows one specialization and one degree and so I also need it show all specializations and degrees for the student. Thanks Jennifer

ELECT DISTINCT dbo_ACADEMIC.PEOPLE_ID, dbo_PEOPLE.LAST_NAME, dbo_PEOPLE.FIRST_NAME, dbo_ACADEMIC.DEGREE, dbo_CODE_CURRICULUM.LONG_DESC, dbo_ACADEMIC.ACADEMIC_TERM, dbo_ACADEMIC.ACADEMIC_YEAR, dbo_TRANSCRIPTDETAIL.EVENT_ID, dbo_TRANSCRIPTDETAIL.SECTION, dbo_ACADEMIC.COLLEGE, dbo_USERDEFINEDIND.PRIMARYCAMPUS, dbo_ACADEMIC.PROGRAM, dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT
FROM dbo_SECTIONS, (dbo_ADDRESSSCHEDULE INNER JOIN (((dbo_ACADEMIC INNER JOIN dbo_CODE_CURRICULUM ON dbo_ACADEMIC.CURRICULUM = dbo_CODE_CURRICULUM.CODE_VALUE_KEY) INNER JOIN dbo_PEOPLE ON dbo_ACADEMIC.PEOPLE_ID = dbo_PEOPLE.PEOPLE_ID) INNER JOIN dbo_USERDEFINEDIND ON dbo_PEOPLE.PEOPLE_ID = dbo_USERDEFINEDIND.PEOPLE_ID) ON dbo_ADDRESSSCHEDULE.PEOPLE_ORG_CODE_ID = dbo_ACADEMIC.PEOPLE_CODE_ID) INNER JOIN dbo_TRANSCRIPTDETAIL ON dbo_PEOPLE.PEOPLE_CODE_ID = dbo_TRANSCRIPTDETAIL.PEOPLE_CODE_ID
WHERE (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "BUAD5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D") AND ((dbo_ACADEMIC.PRIMARY_FLAG)="Y")) OR (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "ECON5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D")) OR (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "FINA5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D")) OR (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "MGMT5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D")) OR (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "MISM5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D")) OR (((dbo_ACADEMIC.ACADEMIC_YEAR) Like "2016" Or (dbo_ACADEMIC.ACADEMIC_YEAR) Like "2017") AND ((dbo_TRANSCRIPTDETAIL.EVENT_ID) Like "MKTG5*") AND ((dbo_TRANSCRIPTDETAIL.SECTION) Like "LU*" Or (dbo_TRANSCRIPTDETAIL.SECTION) Like "VC*") AND ((dbo_ACADEMIC.COLLEGE)="BUAD") AND ((dbo_USERDEFINEDIND.PRIMARYCAMPUS)="O000000014") AND ((dbo_ACADEMIC.PROGRAM)="GRADEX") AND ((dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT) Like "A" Or (dbo_TRANSCRIPTDETAIL.ADD_DROP_WAIT)="D"))
ORDER BY dbo_PEOPLE.LAST_NAME, dbo_PEOPLE.FIRST_NAME;

[Bulzie]

So its pulling what you want but just giving you duplicate records? If so sounds like you need a Totals/GroupBy somewhere. You can create a new query and reference the above query, add all the fields and then do the GrouBy option on all the fields to remove dups.

[jfranklin72106]

Bulzie can you give me a step by step on this? I am still fairly next at Access. Excel has been what I always used. Will the totals/groupby parameter separate out the terms and degree or specialization?

[Bulzie]

Not sure if this is what you need. I assume that query above returns the records you want but there are duplicates?

1. At top in the ribbon bar, Go to Create, Query Design, Select the above query from the Queries tab and hit Ok.
2. Select all the fields and put in the columns. Then at the top of that query, select the Totals button and have it do a GroubBy on all the fields. Run it and see what you get.

[Micron]

Maybe set the query Unique Records or Unique Values property to Yes?
They are mutually exclusive, so you cannot set both to yes at the same time. See the query property sheet.

[jfranklin72106]

That didn't work. Still getting duplicates. It is not picking out specific courses per term. It is picking all courses the student ever took and duplicating them for each term as in the sample.

[jfranklin72106]

This did not work either. Any other thoughts? Thanks.

[Micron]

Not sure whom you're addressing.
Anyway, given the complexity of your query and all those joins, the best suggestion I can offer is to build it in stages. Even though you may not be using all the fields in one or more of those tables, repeated data can cause your query to return records you don't want. So start with two tables and see how it goes. Sometimes the best you can do is create a query in which you might minimize duplicates but find you can't eliminate them totally, then you query that query and use Unique Values or Unique Rows. You can also simplify your sql by using IN("BUAD5", "ECON5", etc) rather than all those OR lines for Event_ID and Academic_Year.

[jfranklin72106]

Thanks Micron. It was you I was addressing previously. Sorry I didn't make that clear. I will try this and see what I get.

[jfranklin72106]

Hi Micron. I tried what you suggested but still didn't get any where. Could you possibly write into my SQL how I should plug in the IN query you suggested. I don't think I did it right.

[Micron]

Just so we're clear, Like can only be used against string data. At this point, I'm unclear if your ACADEMIC_YEAR field is date data type or not. Upon closer examination of your images, I see that the field you're asking about regarding using the IN clause has wild cards in it (pretty small on my laptop). You can't use wildcards with the IN clause, AFAIK. Back to building your query in stages, I guess.

[jfranklin72106]

Thanks Micron. I checked and I think the Academic_year field is set to text. I'll try and take out the wildcards and see what happens. I know there is a simple solution but for some reason I can't seem to figure out what it is.

[Micron]

As I wrote, Like is ok with text fields, so if your date is text you can use it. However, not sure what impact a text date will have on your results. You can't link it to any date field, and who knows, that could contribute to your issue (you may need additional joins that you can't create because of that).
go back to post 8 suggestion: build in stages