I need to write to a group of students about 500 who have met certain criteria - I know their name and student number. The student number is the unique identifier I use in my Access 2000 database. However, I foolishly expected central records to provide Term-time addresses for my study group.
Instead I have been sent an Excel spreadsheet containing both term and home addresses of all students.
What's the problem - I hear you ask. Well....
The table consists of 17,000 entries but this only represents about 11,000 students as many of the entries are duplicated in that the same student number can have either 1 or 2 entries depending on the type of address . (I believe this type of structure is called a flatfile - could be wrong) sample below.
The 3 fields I am concerned with are student number, address type, either T or H, representing Term-time or Home address and the Address field itself.
If the student lives at home during term-time there is NO term-time address entry; the Home Address doubles as the Term-time address but is not identified as such.
I can create a table link to this file - that bit is OK
My problem is that I need to write to all the students at their Term-time address.
So I need a query that says:- SELECT all students with a term-time address (easy bit) and ALSO select all students Home Address WHERE there is no term time address for the same student number.
I can do this the long way round by splitting the Execl table into two tables - one containing only the Home Addresses and the other with only the Term-Time Addresses. I can then compare them using the unmatched query wizard - select those in home which have no matching entry in term and append this to term to get the full list.
There must be a simpler way of doing this but I can't think of it.
I wouldn't worry if this was a one-off mailing but I think I'm going to be doing this every term for the forseeable future but with a different cohort on each occasion.
Any ideas?
Instead I have been sent an Excel spreadsheet containing both term and home addresses of all students.
What's the problem - I hear you ask. Well....
The table consists of 17,000 entries but this only represents about 11,000 students as many of the entries are duplicated in that the same student number can have either 1 or 2 entries depending on the type of address . (I believe this type of structure is called a flatfile - could be wrong) sample below.
Code:
11234 T Blah de Blah
11234 T Blah de Blah
11234 H So and So
22565 H Blah de Blah
54649 H So and So
88832 T Blah de Blah
88832 H So and SoThe 3 fields I am concerned with are student number, address type, either T or H, representing Term-time or Home address and the Address field itself.
If the student lives at home during term-time there is NO term-time address entry; the Home Address doubles as the Term-time address but is not identified as such.
I can create a table link to this file - that bit is OK
My problem is that I need to write to all the students at their Term-time address.
So I need a query that says:- SELECT all students with a term-time address (easy bit) and ALSO select all students Home Address WHERE there is no term time address for the same student number.
I can do this the long way round by splitting the Execl table into two tables - one containing only the Home Addresses and the other with only the Term-Time Addresses. I can then compare them using the unmatched query wizard - select those in home which have no matching entry in term and append this to term to get the full list.
There must be a simpler way of doing this but I can't think of it.
I wouldn't worry if this was a one-off mailing but I think I'm going to be doing this every term for the forseeable future but with a different cohort on each occasion.
Any ideas?
Last edited:
