duplicate records

[BBiggs]

I have a new database that I have set up and it is comparing similar data for
two different semesters. It has a junction table between that two tables
that has referential integrity enforced twice. When running a query, if it
does not have exactly matching records in each table, it duplicates records.
If I run a report and does not have matching records, it truncates the record
that does not have a "buddy". Have any ideas how I can get this to work
properly? I need to show all the data so that I can compare semester to
semester.
BB

 

[John W. Vinson]

I have a new database that I have set up and it is comparing similar data for
two different semesters. It has a junction table between that two tables
that has referential integrity enforced twice. When running a query, if it
does not have exactly matching records in each table, it duplicates records.
If I run a report and does not have matching records, it truncates the record
that does not have a "buddy". Have any ideas how I can get this to work
properly? I need to show all the data so that I can compare semester to
semester.
BB

Ummmm...

So you want Access to take incorrect data and somehow ascertain what you
meant?

If you don't have matching records then... you don't have matching records.
Could you post some examples of the data, and how you would like it to
match??? Perhaps also post the SQL view of the query.

 

[BBiggs]

Let's see if I can explain. Ok last semester I had a certain number of
courses that were available to students and had general info available to
view like who taught the course and how many students were enrolled in it.
This semester I also have the some of the same courses available, and ALSO
some that were not offered last semester. Also some of the courses that were
offered last semester are nNOT being offered this semester. Now if I compare
this semester to last semester, the report will truncate the unmatched
courses for some reason. Or it will duplicate records to compensate for the
"missing" courses. Any ideas as to how I can see all of the data for both?

 

[John W. Vinson]

Let's see if I can explain. Ok last semester I had a certain number of
courses that were available to students and had general info available to
view like who taught the course and how many students were enrolled in it.
This semester I also have the some of the same courses available, and ALSO
some that were not offered last semester. Also some of the courses that were
offered last semester are nNOT being offered this semester. Now if I compare
this semester to last semester, the report will truncate the unmatched
courses for some reason. Or it will duplicate records to compensate for the
"missing" courses. Any ideas as to how I can see all of the data for both?

Well, restructuring your tables so that they are correctly normalized (with a
table of Courses related one to many to a table of CourseOfferings) would be
one way. Another would be to use a fairly complicated query - join this
semester to last semester using a "Left Outer Join" (select the join line and
choose option 2, "show all records in Query1 and matching records in Query2");
a second query using option 3, a "Right Outer Join"; and a UNION query
splicing these two together. Since you didn't post any field or table names I
can't just dash off the SQL for you; and I'm leaving for a week tomorrow, so
perhaps you should post the SQL of your current query and the explanation
above as a new thread if you have a hard time getting this working.

 

[BBiggs]

SELECT [Fall 2008 Web Query].CRN AS [Fall 2008 Web Query_CRN], [Fall 2008 Web
Query].[Course #] AS [Fall 2008 Web Query_Course #], [Fall 2008 Web
Query].Sect AS [Fall 2008 Web Query_Sect], [Fall 2008 Web Query].[Class
Title] AS [Fall 2008 Web Query_Class Title], [Fall 2008 Web Query].Professor
AS [Fall 2008 Web Query_Professor], [Fall 2008 Web Query].PEnroll AS [Fall
2008 Web Query_PEnroll], [Fall 2008 Web Query].AEnroll AS [Fall 2008 Web
Query_AEnroll], [Spring 2009 Web Query].CRN AS [Spring 2009 Web Query_CRN],
[Spring 2009 Web Query].[Course #] AS [Spring 2009 Web Query_Course #],
[Spring 2009 Web Query].Sect AS [Spring 2009 Web Query_Sect], [Spring 2009
Web Query].[Class Title] AS [Spring 2009 Web Query_Class Title], [Spring 2009
Web Query].Professor AS [Spring 2009 Web Query_Professor], [Spring 2009 Web
Query].PEnroll AS [Spring 2009 Web Query_PEnroll], [Spring 2009 Web
Query].AEnroll AS [Spring 2009 Web Query_AEnroll]
FROM ([Course Offerings] INNER JOIN [Spring 2009 Web Query] ON [Course
Offerings].[Course #] = [Spring 2009 Web Query].[Course #]) INNER JOIN [Fall
2008 Web Query] ON [Course Offerings].[Course #] = [Fall 2008 Web
Query].[Course #];


Here is the SQL view of the comparison query.
BB