Difference in Student Data

[jaaron29]

Hello Folks!!

I am SO THANKFUL that this site exists...for my sanity and my job!

Hopefully I can find a solution before boss returns....


I work at a college, and I'm trying to do a comparision query; one that will show differences.

Student lists one address on financial aid application, another address may be on our admission system. Common to both tables, that I have created, are their SSN numbers.

The query results I am looking for are....
Compare both tables using SSN and look for a difference in the addresses. What query would give me a difference in address listed, from both tables? I intend to take results and do MS MailMerge to students.

Any help on this is appreciated more than you would know....
Thanks!

 

[llkhoutx]

Join the tables in a query by linking the SSNs.

 

[jaaron29]

Yes, the SSN would be the link...it's the difference in data between the two tables that I'm trying to identify... I have linked the tables with the SSN

 

[Jewellissa]

I think this would work... I am trying to get teh syntax right... but just use Expression builder...

Build a query in Design View.
Add the two different Student record types (i.e Fin aid, Reg, etc.)
Build a relationship linking SSN#
(Guess you have this so far....)
Double click on one of the address fields
Click in the Criteria area and then click on the expression builder
(Lets Assume you used the Reg address)
(It looks like a wand with magick coming out)
The expression will be ~:

Not [Fin Aid Table]![Address]

This says that the Reg. Addres is not equal to the Address in Fin Aid Table. You might need to tinker with it for City and such but...

Good Luck,
Jewelz

 

[llkhoutx]

I started building a simple example and immediately realized that your going to have a big problem with street addresses in that they may be substantially similar but not exactly identical, example:

1516 Bay Area Blvd and 1516 Bay Area
2323 1st Street and 2323 First Street
1516 Bay Area and 1615 Bay Area

Unfortunately I don't have a complete solution for you other than to use an equi-join, not a right join as I previously stated, on SSN and street address to get duplicates. You'll still miss some, per above.

 

[Jewellissa]

I think a slight simplification might be to check that both addys are:

First -- Same State
Second -- Same City
Last -- Street Addy

Because there are likely to be more irregularities in Street Address that state and City..


J

 

[jaaron29]

See what I'm sayin'...!!! Haven't tried your suggestions, but just the fact that SOMEBODY has one, is good enough for me.

Will try these when time permits (rather soon, hear boss's footsteps) Seems like I see a way in this...

 

[jaaron29]

Hey All,

It WORKED!!! I used the 'NOT[TABLE NAME]; and results are exactly what I needed.

Jewelz, thanks so much for your help!

To IIkhoutx....the results were as you described...did get similar and different results....will have to try and filter these out...

Thanks to you both....

Be back with other questions as they arise...

Toni