archiving

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have queries that archive data by departure date, but an odd thing is that
one field is being populated by one of the following two words when a
specific date is typed in as a parameter but other records that are not the
specific date are being archived as well. Has anyone seen this before?

zirkelbach

or

sook
 
Please provide the SQL for this query. Also do the tables used in the queries
have subdatasheets or lookup fields at table level?
 
no I don't think so. I have looked for meanings on the internet and they are
just names.
 
yes there are subdatasheets and lookup fields. here is the SQL calls: there
are three queries run from a macro in this order:

1. Append archive
INSERT INTO Archive ( EMPLOYEEID, LASTNAME, [OTHER NAME], FIRSTNAME, MI,
COUNTRYCODE, COUNTRYNAME, COUNTRYADJECTIVE, ODCADDRESS, NETSAFA, RANK,
DATEOFRANK, SERVICE, SENIOROFFICER, WCN, CT, [ID#STUDENT], [ID#SPOUSE], CURR,
CURRCODE, CURRICSHORTNAME, GRADDATES, DEGREE1, DEGREE2, TOEFL, ARRIVAL,
DEPARTURE, [FMS/IMET], ZIP, [NUMBER], DATEOFBIRTH, [CASE], TRAVEL, [ITO#],
ITODATE, INSTRBEGINDATE, PRIVACYRELEASE, NEEDSSPONSOR, HAVESPONSOR,
INTERNATIONAL, WILLINGTOSPONSOR, ACCOMPANIED, LIVINGALLOWANCE, TECHREF,
ENGSCI, DLI, BOQCHECKOUTFOLIO, [ID#], DEPENDENTS_SPOUSEFIRSTNAME,
DEPENDENTS_DEPENDENTTYPE, DEPENDENTLASTNAME, DEPENDENTOTHERNAME,
DEPENDENTFIRSTNAME, DOB, DEPENDENTS_GENDER, STATUS, SPONSOR_SPONSORCODE,
SPONSORLASTNAME, SPONSORFIRSTNAME, SPONSORRANK, SPONSORSERVICE,
GRADUATIONDATE, SPONSOR_DEPENDENTTYPE, SSN, CURRICNUMBER, SPONSOR_ADDRESS,
SPONSOR_CITY, ZIPCODE, PHONE, SPONSOR_EMAIL, [SPONSOR_#OFCHILDREN],
SPONSOR_MAILCODE, SPONSOR_GENDER, SPONSOR_MARITALSTATUS, DATEAPPLIED,
DATEASSIGNED, PREFERENCES, TYPE, OCCUPATION, MASLNO, COURSENO, TITLE,
LENGTHQTRS, CONVENES, ENDS, MASLS_COMMENTS, SPONSORCHILDLASTNAME,
SPONSORCHILDOTHERNAME, SPONSORCHILDNAME, SPONSORCHILDDOB, SPONSORCHILDGENDER )
SELECT [INTERNATIONAL STUDENT].EMPLOYEEID, [INTERNATIONAL STUDENT].LASTNAME,
[INTERNATIONAL STUDENT].[OTHER NAME], [INTERNATIONAL STUDENT].FIRSTNAME,
[INTERNATIONAL STUDENT].MI, [INTERNATIONAL STUDENT].COUNTRYCODE,
[INTERNATIONAL STUDENT].COUNTRYNAME, [INTERNATIONAL
STUDENT].COUNTRYADJECTIVE, [INTERNATIONAL STUDENT].ODCADDRESS, [INTERNATIONAL
STUDENT].NETSAFA, [INTERNATIONAL STUDENT].RANK, [INTERNATIONAL
STUDENT].DATEOFRANK, [INTERNATIONAL STUDENT].SERVICE, [INTERNATIONAL
STUDENT].SENIOROFFICER, [INTERNATIONAL STUDENT].WCN, [INTERNATIONAL
STUDENT].CT, [INTERNATIONAL STUDENT].[ID#STUDENT], [INTERNATIONAL
STUDENT].[ID#SPOUSE], [INTERNATIONAL STUDENT].CURR, [INTERNATIONAL
STUDENT].CURRCODE, [INTERNATIONAL STUDENT].CURRICSHORTNAME, [INTERNATIONAL
STUDENT].GRADDATES, [INTERNATIONAL STUDENT].DEGREE1, [INTERNATIONAL
STUDENT].DEGREE2, [INTERNATIONAL STUDENT].TOEFL, [INTERNATIONAL
STUDENT].ARRIVAL, [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL
STUDENT].[FMS/IMET], [INTERNATIONAL STUDENT].ZIP, [INTERNATIONAL
STUDENT].NUMBER, [INTERNATIONAL STUDENT].DATEOFBIRTH, [INTERNATIONAL
STUDENT].CASE, [INTERNATIONAL STUDENT].TRAVEL, [INTERNATIONAL
STUDENT].[ITO#], [INTERNATIONAL STUDENT].ITODATE, [INTERNATIONAL
STUDENT].INSTRBEGINDATE, [INTERNATIONAL STUDENT].PRIVACYRELEASE,
[INTERNATIONAL STUDENT].NEEDSSPONSOR, [INTERNATIONAL STUDENT].HAVESPONSOR,
[INTERNATIONAL STUDENT].INTERNATIONAL, [INTERNATIONAL
STUDENT].WILLINGTOSPONSOR, [INTERNATIONAL STUDENT].ACCOMPANIED,
[INTERNATIONAL STUDENT].LIVINGALLOWANCE, [INTERNATIONAL STUDENT].TECHREF,
[INTERNATIONAL STUDENT].ENGSCI, [INTERNATIONAL STUDENT].DLI, [INTERNATIONAL
STUDENT].BOQCHECKOUTFOLIO, [INTERNATIONAL STUDENT].[ID#],
DEPENDENTS.SPOUSEFIRSTNAME, DEPENDENTS.DEPENDENTTYPE,
DEPENDENTS.DEPENDENTLASTNAME, DEPENDENTS.DEPENDENTOTHERNAME,
DEPENDENTS.DEPENDENTFIRSTNAME, DEPENDENTS.DOB, DEPENDENTS.GENDER,
SPONSOR.STATUS, SPONSOR.SPONSORCODE, SPONSOR.SPONSORLASTNAME,
SPONSOR.SPONSORFIRSTNAME, SPONSOR.SPONSORRANK, SPONSOR.SPONSORSERVICE,
SPONSOR.GRADUATIONDATE, SPONSOR.DEPENDENTTYPE, SPONSOR.SSN,
SPONSOR.CURRICNUMBER, SPONSOR.ADDRESS, SPONSOR.CITY, SPONSOR.ZIPCODE,
SPONSOR.PHONE, SPONSOR.EMAIL, SPONSOR.[#OFCHILDREN], SPONSOR.MAILCODE,
SPONSOR.GENDER, SPONSOR.MARITALSTATUS, SPONSOR.DATEAPPLIED,
SPONSOR.DATEASSIGNED, SPONSOR.PREFERENCES, SPONSOR.TYPE, SPONSOR.OCCUPATION,
MASLS.MASLNO, MASLS.COURSENO, MASLS.TITLE, MASLS.LENGTHQTRS, MASLS.CONVENES,
MASLS.ENDS, MASLS.COMMENTS, [SPONSOR DEPENDENTS].SPONSORCHILDLASTNAME,
[SPONSOR DEPENDENTS].SPONSORCHILDOTHERNAME, [SPONSOR
DEPENDENTS].SPONSORCHILDNAME, [SPONSOR DEPENDENTS].SPONSORCHILDDOB, [SPONSOR
DEPENDENTS].SPONSORCHILDGENDER
FROM ((([INTERNATIONAL STUDENT] LEFT JOIN DEPENDENTS ON [INTERNATIONAL
STUDENT].EMPLOYEEID = DEPENDENTS.EMPLOYEEID) LEFT JOIN SPONSOR ON
[INTERNATIONAL STUDENT].EMPLOYEEID = SPONSOR.EMPLOYEEID) LEFT JOIN MASLS ON
[INTERNATIONAL STUDENT].EMPLOYEEID = MASLS.EMPLOYEEID) LEFT JOIN [SPONSOR
DEPENDENTS] ON SPONSOR.SPONSORID = [SPONSOR DEPENDENTS].SPONSORID
GROUP BY [INTERNATIONAL STUDENT].EMPLOYEEID, [INTERNATIONAL
STUDENT].LASTNAME, [INTERNATIONAL STUDENT].[OTHER NAME], [INTERNATIONAL
STUDENT].FIRSTNAME, [INTERNATIONAL STUDENT].MI, [INTERNATIONAL
STUDENT].COUNTRYCODE, [INTERNATIONAL STUDENT].COUNTRYNAME, [INTERNATIONAL
STUDENT].COUNTRYADJECTIVE, [INTERNATIONAL STUDENT].ODCADDRESS, [INTERNATIONAL
STUDENT].NETSAFA, [INTERNATIONAL STUDENT].RANK, [INTERNATIONAL
STUDENT].DATEOFRANK, [INTERNATIONAL STUDENT].SERVICE, [INTERNATIONAL
STUDENT].SENIOROFFICER, [INTERNATIONAL STUDENT].WCN, [INTERNATIONAL
STUDENT].CT, [INTERNATIONAL STUDENT].[ID#STUDENT], [INTERNATIONAL
STUDENT].[ID#SPOUSE], [INTERNATIONAL STUDENT].CURR, [INTERNATIONAL
STUDENT].CURRCODE, [INTERNATIONAL STUDENT].CURRICSHORTNAME, [INTERNATIONAL
STUDENT].GRADDATES, [INTERNATIONAL STUDENT].DEGREE1, [INTERNATIONAL
STUDENT].DEGREE2, [INTERNATIONAL STUDENT].TOEFL, [INTERNATIONAL
STUDENT].ARRIVAL, [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL
STUDENT].[FMS/IMET], [INTERNATIONAL STUDENT].ZIP, [INTERNATIONAL
STUDENT].NUMBER, [INTERNATIONAL STUDENT].DATEOFBIRTH, [INTERNATIONAL
STUDENT].CASE, [INTERNATIONAL STUDENT].TRAVEL, [INTERNATIONAL
STUDENT].[ITO#], [INTERNATIONAL STUDENT].ITODATE, [INTERNATIONAL
STUDENT].INSTRBEGINDATE, [INTERNATIONAL STUDENT].PRIVACYRELEASE,
[INTERNATIONAL STUDENT].NEEDSSPONSOR, [INTERNATIONAL STUDENT].HAVESPONSOR,
[INTERNATIONAL STUDENT].INTERNATIONAL, [INTERNATIONAL
STUDENT].WILLINGTOSPONSOR, [INTERNATIONAL STUDENT].ACCOMPANIED,
[INTERNATIONAL STUDENT].LIVINGALLOWANCE, [INTERNATIONAL STUDENT].TECHREF,
[INTERNATIONAL STUDENT].ENGSCI, [INTERNATIONAL STUDENT].DLI, [INTERNATIONAL
STUDENT].BOQCHECKOUTFOLIO, [INTERNATIONAL STUDENT].[ID#],
DEPENDENTS.SPOUSEFIRSTNAME, DEPENDENTS.DEPENDENTTYPE,
DEPENDENTS.DEPENDENTLASTNAME, DEPENDENTS.DEPENDENTOTHERNAME,
DEPENDENTS.DEPENDENTFIRSTNAME, DEPENDENTS.DOB, DEPENDENTS.GENDER,
SPONSOR.STATUS, SPONSOR.SPONSORCODE, SPONSOR.SPONSORLASTNAME,
SPONSOR.SPONSORFIRSTNAME, SPONSOR.SPONSORRANK, SPONSOR.SPONSORSERVICE,
SPONSOR.GRADUATIONDATE, SPONSOR.DEPENDENTTYPE, SPONSOR.SSN,
SPONSOR.CURRICNUMBER, SPONSOR.ADDRESS, SPONSOR.CITY, SPONSOR.ZIPCODE,
SPONSOR.PHONE, SPONSOR.EMAIL, SPONSOR.[#OFCHILDREN], SPONSOR.MAILCODE,
SPONSOR.GENDER, SPONSOR.MARITALSTATUS, SPONSOR.DATEAPPLIED,
SPONSOR.DATEASSIGNED, SPONSOR.PREFERENCES, SPONSOR.TYPE, SPONSOR.OCCUPATION,
MASLS.MASLNO, MASLS.COURSENO, MASLS.TITLE, MASLS.LENGTHQTRS, MASLS.CONVENES,
MASLS.ENDS, MASLS.COMMENTS, [SPONSOR DEPENDENTS].SPONSORCHILDLASTNAME,
[SPONSOR DEPENDENTS].SPONSORCHILDOTHERNAME, [SPONSOR
DEPENDENTS].SPONSORCHILDNAME, [SPONSOR DEPENDENTS].SPONSORCHILDDOB, [SPONSOR
DEPENDENTS].SPONSORCHILDGENDER
HAVING ((([INTERNATIONAL STUDENT].DEPARTURE)=[Enter Departure Date-For
Example: 12/04]));

2. Archive delete:
DELETE [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL STUDENT].*
FROM [INTERNATIONAL STUDENT]
WHERE ((([INTERNATIONAL STUDENT].DEPARTURE)=[Enter Departure Date-For
Example: 12/04]));

3. Append from Archive to Archive Input Form-not all the fields were needed
for this input form:
INSERT INTO [Archive for Input Form] ( EMPLOYEEID, LASTNAME, [OTHER NAME],
FIRSTNAME, MI, COUNTRYCODE, COUNTRYNAME, COUNTRYADJECTIVE, ODCADDRESS,
NETSAFA, RANK, DATEOFRANK, SERVICE, SENIOROFFICER, WCN, CT, [ID#STUDENT],
[ID#SPOUSE], CURR, CURRCODE, CURRICSHORTNAME, GRADDATES, DEGREE1, DEGREE2,
TOEFL, ARRIVAL, DEPARTURE, [FMS/IMET], [INTERNATIONAL STUDENT_ADDRESS],
[INTERNATIONAL STUDENT_CITY], ZIP, [NUMBER], DATEOFBIRTH, [CASE], TRAVEL,
[ITO#], ITODATE, INSTRBEGINDATE, PRIVACYRELEASE, [INTERNATIONAL
STUDENT_EMAIL], EMAIL2, NEEDSSPONSOR, HAVESPONSOR, INTERNATIONAL,
WILLINGTOSPONSOR, ACCOMPANIED, [INTERNATIONAL STUDENT_#OFCHILDREN],
[INTERNATIONAL STUDENT_SPOUSEFIRSTNAME], LIVINGALLOWANCE, [INTERNATIONAL
STUDENT_MARITALSTATUS], TECHREF, ENGSCI, DLI, BOQCHECKOUTFOLIO,
[INTERNATIONAL STUDENT_COMMENTS], [ID#], [INTERNATIONAL STUDENT_SPONSORCODE] )
SELECT Archive.EMPLOYEEID, Archive.LASTNAME, Archive.[OTHER NAME],
Archive.FIRSTNAME, Archive.MI, Archive.COUNTRYCODE, Archive.COUNTRYNAME,
Archive.COUNTRYADJECTIVE, Archive.ODCADDRESS, Archive.NETSAFA, Archive.RANK,
Archive.DATEOFRANK, Archive.SERVICE, Archive.SENIOROFFICER, Archive.WCN,
Archive.CT, Archive.[ID#STUDENT], Archive.[ID#SPOUSE], Archive.CURR,
Archive.CURRCODE, Archive.CURRICSHORTNAME, Archive.GRADDATES,
Archive.DEGREE1, Archive.DEGREE2, Archive.TOEFL, Archive.ARRIVAL,
Archive.DEPARTURE, Archive.[FMS/IMET], Archive.[INTERNATIONAL
STUDENT_ADDRESS], Archive.[INTERNATIONAL STUDENT_CITY], Archive.ZIP,
Archive.NUMBER, Archive.DATEOFBIRTH, Archive.CASE, Archive.TRAVEL,
Archive.[ITO#], Archive.ITODATE, Archive.INSTRBEGINDATE,
Archive.PRIVACYRELEASE, Archive.[INTERNATIONAL STUDENT_EMAIL],
Archive.EMAIL2, Archive.NEEDSSPONSOR, Archive.HAVESPONSOR,
Archive.INTERNATIONAL, Archive.WILLINGTOSPONSOR, Archive.ACCOMPANIED,
Archive.[INTERNATIONAL STUDENT_#OFCHILDREN], Archive.[INTERNATIONAL
STUDENT_SPOUSEFIRSTNAME], Archive.LIVINGALLOWANCE, Archive.[INTERNATIONAL
STUDENT_MARITALSTATUS], Archive.TECHREF, Archive.ENGSCI, Archive.DLI,
Archive.BOQCHECKOUTFOLIO, Archive.[INTERNATIONAL STUDENT_COMMENTS],
Archive.[ID#], Archive.[INTERNATIONAL STUDENT_SPONSORCODE]
FROM Archive
GROUP BY Archive.EMPLOYEEID, Archive.LASTNAME, Archive.[OTHER NAME],
Archive.FIRSTNAME, Archive.MI, Archive.COUNTRYCODE, Archive.COUNTRYNAME,
Archive.COUNTRYADJECTIVE, Archive.ODCADDRESS, Archive.NETSAFA, Archive.RANK,
Archive.DATEOFRANK, Archive.SERVICE, Archive.SENIOROFFICER, Archive.WCN,
Archive.CT, Archive.[ID#STUDENT], Archive.[ID#SPOUSE], Archive.CURR,
Archive.CURRCODE, Archive.CURRICSHORTNAME, Archive.GRADDATES,
Archive.DEGREE1, Archive.DEGREE2, Archive.TOEFL, Archive.ARRIVAL,
Archive.DEPARTURE, Archive.[FMS/IMET], Archive.[INTERNATIONAL
STUDENT_ADDRESS], Archive.[INTERNATIONAL STUDENT_CITY], Archive.ZIP,
Archive.NUMBER, Archive.DATEOFBIRTH, Archive.CASE, Archive.TRAVEL,
Archive.[ITO#], Archive.ITODATE, Archive.INSTRBEGINDATE,
Archive.PRIVACYRELEASE, Archive.[INTERNATIONAL STUDENT_EMAIL],
Archive.EMAIL2, Archive.NEEDSSPONSOR, Archive.HAVESPONSOR,
Archive.INTERNATIONAL, Archive.WILLINGTOSPONSOR, Archive.ACCOMPANIED,
Archive.[INTERNATIONAL STUDENT_#OFCHILDREN], Archive.[INTERNATIONAL
STUDENT_SPOUSEFIRSTNAME], Archive.LIVINGALLOWANCE, Archive.[INTERNATIONAL
STUDENT_MARITALSTATUS], Archive.TECHREF, Archive.ENGSCI, Archive.DLI,
Archive.BOQCHECKOUTFOLIO, Archive.[INTERNATIONAL STUDENT_COMMENTS],
Archive.[ID#], Archive.[INTERNATIONAL STUDENT_SPONSORCODE]
HAVING (((Archive.DEPARTURE)=[Enter Departure Date-For Example: 12/04]));
 
I bet that it's the subdatasheets or lookup fields. They are horrible things.
You probably need to include links to the tables behind either of them in
your queries.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

nms said:
yes there are subdatasheets and lookup fields. here is the SQL calls: there
are three queries run from a macro in this order:

1. Append archive
INSERT INTO Archive ( EMPLOYEEID, LASTNAME, [OTHER NAME], FIRSTNAME, MI,
COUNTRYCODE, COUNTRYNAME, COUNTRYADJECTIVE, ODCADDRESS, NETSAFA, RANK,
DATEOFRANK, SERVICE, SENIOROFFICER, WCN, CT, [ID#STUDENT], [ID#SPOUSE], CURR,
CURRCODE, CURRICSHORTNAME, GRADDATES, DEGREE1, DEGREE2, TOEFL, ARRIVAL,
DEPARTURE, [FMS/IMET], ZIP, [NUMBER], DATEOFBIRTH, [CASE], TRAVEL, [ITO#],
ITODATE, INSTRBEGINDATE, PRIVACYRELEASE, NEEDSSPONSOR, HAVESPONSOR,
INTERNATIONAL, WILLINGTOSPONSOR, ACCOMPANIED, LIVINGALLOWANCE, TECHREF,
ENGSCI, DLI, BOQCHECKOUTFOLIO, [ID#], DEPENDENTS_SPOUSEFIRSTNAME,
DEPENDENTS_DEPENDENTTYPE, DEPENDENTLASTNAME, DEPENDENTOTHERNAME,
DEPENDENTFIRSTNAME, DOB, DEPENDENTS_GENDER, STATUS, SPONSOR_SPONSORCODE,
SPONSORLASTNAME, SPONSORFIRSTNAME, SPONSORRANK, SPONSORSERVICE,
GRADUATIONDATE, SPONSOR_DEPENDENTTYPE, SSN, CURRICNUMBER, SPONSOR_ADDRESS,
SPONSOR_CITY, ZIPCODE, PHONE, SPONSOR_EMAIL, [SPONSOR_#OFCHILDREN],
SPONSOR_MAILCODE, SPONSOR_GENDER, SPONSOR_MARITALSTATUS, DATEAPPLIED,
DATEASSIGNED, PREFERENCES, TYPE, OCCUPATION, MASLNO, COURSENO, TITLE,
LENGTHQTRS, CONVENES, ENDS, MASLS_COMMENTS, SPONSORCHILDLASTNAME,
SPONSORCHILDOTHERNAME, SPONSORCHILDNAME, SPONSORCHILDDOB, SPONSORCHILDGENDER )
SELECT [INTERNATIONAL STUDENT].EMPLOYEEID, [INTERNATIONAL STUDENT].LASTNAME,
[INTERNATIONAL STUDENT].[OTHER NAME], [INTERNATIONAL STUDENT].FIRSTNAME,
[INTERNATIONAL STUDENT].MI, [INTERNATIONAL STUDENT].COUNTRYCODE,
[INTERNATIONAL STUDENT].COUNTRYNAME, [INTERNATIONAL
STUDENT].COUNTRYADJECTIVE, [INTERNATIONAL STUDENT].ODCADDRESS, [INTERNATIONAL
STUDENT].NETSAFA, [INTERNATIONAL STUDENT].RANK, [INTERNATIONAL
STUDENT].DATEOFRANK, [INTERNATIONAL STUDENT].SERVICE, [INTERNATIONAL
STUDENT].SENIOROFFICER, [INTERNATIONAL STUDENT].WCN, [INTERNATIONAL
STUDENT].CT, [INTERNATIONAL STUDENT].[ID#STUDENT], [INTERNATIONAL
STUDENT].[ID#SPOUSE], [INTERNATIONAL STUDENT].CURR, [INTERNATIONAL
STUDENT].CURRCODE, [INTERNATIONAL STUDENT].CURRICSHORTNAME, [INTERNATIONAL
STUDENT].GRADDATES, [INTERNATIONAL STUDENT].DEGREE1, [INTERNATIONAL
STUDENT].DEGREE2, [INTERNATIONAL STUDENT].TOEFL, [INTERNATIONAL
STUDENT].ARRIVAL, [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL
STUDENT].[FMS/IMET], [INTERNATIONAL STUDENT].ZIP, [INTERNATIONAL
STUDENT].NUMBER, [INTERNATIONAL STUDENT].DATEOFBIRTH, [INTERNATIONAL
STUDENT].CASE, [INTERNATIONAL STUDENT].TRAVEL, [INTERNATIONAL
STUDENT].[ITO#], [INTERNATIONAL STUDENT].ITODATE, [INTERNATIONAL
STUDENT].INSTRBEGINDATE, [INTERNATIONAL STUDENT].PRIVACYRELEASE,
[INTERNATIONAL STUDENT].NEEDSSPONSOR, [INTERNATIONAL STUDENT].HAVESPONSOR,
[INTERNATIONAL STUDENT].INTERNATIONAL, [INTERNATIONAL
STUDENT].WILLINGTOSPONSOR, [INTERNATIONAL STUDENT].ACCOMPANIED,
[INTERNATIONAL STUDENT].LIVINGALLOWANCE, [INTERNATIONAL STUDENT].TECHREF,
[INTERNATIONAL STUDENT].ENGSCI, [INTERNATIONAL STUDENT].DLI, [INTERNATIONAL
STUDENT].BOQCHECKOUTFOLIO, [INTERNATIONAL STUDENT].[ID#],
DEPENDENTS.SPOUSEFIRSTNAME, DEPENDENTS.DEPENDENTTYPE,
DEPENDENTS.DEPENDENTLASTNAME, DEPENDENTS.DEPENDENTOTHERNAME,
DEPENDENTS.DEPENDENTFIRSTNAME, DEPENDENTS.DOB, DEPENDENTS.GENDER,
SPONSOR.STATUS, SPONSOR.SPONSORCODE, SPONSOR.SPONSORLASTNAME,
SPONSOR.SPONSORFIRSTNAME, SPONSOR.SPONSORRANK, SPONSOR.SPONSORSERVICE,
SPONSOR.GRADUATIONDATE, SPONSOR.DEPENDENTTYPE, SPONSOR.SSN,
SPONSOR.CURRICNUMBER, SPONSOR.ADDRESS, SPONSOR.CITY, SPONSOR.ZIPCODE,
SPONSOR.PHONE, SPONSOR.EMAIL, SPONSOR.[#OFCHILDREN], SPONSOR.MAILCODE,
SPONSOR.GENDER, SPONSOR.MARITALSTATUS, SPONSOR.DATEAPPLIED,
SPONSOR.DATEASSIGNED, SPONSOR.PREFERENCES, SPONSOR.TYPE, SPONSOR.OCCUPATION,
MASLS.MASLNO, MASLS.COURSENO, MASLS.TITLE, MASLS.LENGTHQTRS, MASLS.CONVENES,
MASLS.ENDS, MASLS.COMMENTS, [SPONSOR DEPENDENTS].SPONSORCHILDLASTNAME,
[SPONSOR DEPENDENTS].SPONSORCHILDOTHERNAME, [SPONSOR
DEPENDENTS].SPONSORCHILDNAME, [SPONSOR DEPENDENTS].SPONSORCHILDDOB, [SPONSOR
DEPENDENTS].SPONSORCHILDGENDER
FROM ((([INTERNATIONAL STUDENT] LEFT JOIN DEPENDENTS ON [INTERNATIONAL
STUDENT].EMPLOYEEID = DEPENDENTS.EMPLOYEEID) LEFT JOIN SPONSOR ON
[INTERNATIONAL STUDENT].EMPLOYEEID = SPONSOR.EMPLOYEEID) LEFT JOIN MASLS ON
[INTERNATIONAL STUDENT].EMPLOYEEID = MASLS.EMPLOYEEID) LEFT JOIN [SPONSOR
DEPENDENTS] ON SPONSOR.SPONSORID = [SPONSOR DEPENDENTS].SPONSORID
GROUP BY [INTERNATIONAL STUDENT].EMPLOYEEID, [INTERNATIONAL
STUDENT].LASTNAME, [INTERNATIONAL STUDENT].[OTHER NAME], [INTERNATIONAL
STUDENT].FIRSTNAME, [INTERNATIONAL STUDENT].MI, [INTERNATIONAL
STUDENT].COUNTRYCODE, [INTERNATIONAL STUDENT].COUNTRYNAME, [INTERNATIONAL
STUDENT].COUNTRYADJECTIVE, [INTERNATIONAL STUDENT].ODCADDRESS, [INTERNATIONAL
STUDENT].NETSAFA, [INTERNATIONAL STUDENT].RANK, [INTERNATIONAL
STUDENT].DATEOFRANK, [INTERNATIONAL STUDENT].SERVICE, [INTERNATIONAL
STUDENT].SENIOROFFICER, [INTERNATIONAL STUDENT].WCN, [INTERNATIONAL
STUDENT].CT, [INTERNATIONAL STUDENT].[ID#STUDENT], [INTERNATIONAL
STUDENT].[ID#SPOUSE], [INTERNATIONAL STUDENT].CURR, [INTERNATIONAL
STUDENT].CURRCODE, [INTERNATIONAL STUDENT].CURRICSHORTNAME, [INTERNATIONAL
STUDENT].GRADDATES, [INTERNATIONAL STUDENT].DEGREE1, [INTERNATIONAL
STUDENT].DEGREE2, [INTERNATIONAL STUDENT].TOEFL, [INTERNATIONAL
STUDENT].ARRIVAL, [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL
STUDENT].[FMS/IMET], [INTERNATIONAL STUDENT].ZIP, [INTERNATIONAL
STUDENT].NUMBER, [INTERNATIONAL STUDENT].DATEOFBIRTH, [INTERNATIONAL
STUDENT].CASE, [INTERNATIONAL STUDENT].TRAVEL, [INTERNATIONAL
STUDENT].[ITO#], [INTERNATIONAL STUDENT].ITODATE, [INTERNATIONAL
STUDENT].INSTRBEGINDATE, [INTERNATIONAL STUDENT].PRIVACYRELEASE,
[INTERNATIONAL STUDENT].NEEDSSPONSOR, [INTERNATIONAL STUDENT].HAVESPONSOR,
[INTERNATIONAL STUDENT].INTERNATIONAL, [INTERNATIONAL
STUDENT].WILLINGTOSPONSOR, [INTERNATIONAL STUDENT].ACCOMPANIED,
[INTERNATIONAL STUDENT].LIVINGALLOWANCE, [INTERNATIONAL STUDENT].TECHREF,
[INTERNATIONAL STUDENT].ENGSCI, [INTERNATIONAL STUDENT].DLI, [INTERNATIONAL
STUDENT].BOQCHECKOUTFOLIO, [INTERNATIONAL STUDENT].[ID#],
DEPENDENTS.SPOUSEFIRSTNAME, DEPENDENTS.DEPENDENTTYPE,
DEPENDENTS.DEPENDENTLASTNAME, DEPENDENTS.DEPENDENTOTHERNAME,
DEPENDENTS.DEPENDENTFIRSTNAME, DEPENDENTS.DOB, DEPENDENTS.GENDER,
SPONSOR.STATUS, SPONSOR.SPONSORCODE, SPONSOR.SPONSORLASTNAME,
SPONSOR.SPONSORFIRSTNAME, SPONSOR.SPONSORRANK, SPONSOR.SPONSORSERVICE,
SPONSOR.GRADUATIONDATE, SPONSOR.DEPENDENTTYPE, SPONSOR.SSN,
SPONSOR.CURRICNUMBER, SPONSOR.ADDRESS, SPONSOR.CITY, SPONSOR.ZIPCODE,
SPONSOR.PHONE, SPONSOR.EMAIL, SPONSOR.[#OFCHILDREN], SPONSOR.MAILCODE,
SPONSOR.GENDER, SPONSOR.MARITALSTATUS, SPONSOR.DATEAPPLIED,
SPONSOR.DATEASSIGNED, SPONSOR.PREFERENCES, SPONSOR.TYPE, SPONSOR.OCCUPATION,
MASLS.MASLNO, MASLS.COURSENO, MASLS.TITLE, MASLS.LENGTHQTRS, MASLS.CONVENES,
MASLS.ENDS, MASLS.COMMENTS, [SPONSOR DEPENDENTS].SPONSORCHILDLASTNAME,
[SPONSOR DEPENDENTS].SPONSORCHILDOTHERNAME, [SPONSOR
DEPENDENTS].SPONSORCHILDNAME, [SPONSOR DEPENDENTS].SPONSORCHILDDOB, [SPONSOR
DEPENDENTS].SPONSORCHILDGENDER
HAVING ((([INTERNATIONAL STUDENT].DEPARTURE)=[Enter Departure Date-For
Example: 12/04]));

2. Archive delete:
DELETE [INTERNATIONAL STUDENT].DEPARTURE, [INTERNATIONAL STUDENT].*
FROM [INTERNATIONAL STUDENT]
WHERE ((([INTERNATIONAL STUDENT].DEPARTURE)=[Enter Departure Date-For
Example: 12/04]));

3. Append from Archive to Archive Input Form-not all the fields were needed
for this input form:
INSERT INTO [Archive for Input Form] ( EMPLOYEEID, LASTNAME, [OTHER NAME],
FIRSTNAME, MI, COUNTRYCODE, COUNTRYNAME, COUNTRYADJECTIVE, ODCADDRESS,
NETSAFA, RANK, DATEOFRANK, SERVICE, SENIOROFFICER, WCN, CT, [ID#STUDENT],
[ID#SPOUSE], CURR, CURRCODE, CURRICSHORTNAME, GRADDATES, DEGREE1, DEGREE2,
TOEFL, ARRIVAL, DEPARTURE, [FMS/IMET], [INTERNATIONAL STUDENT_ADDRESS],
[INTERNATIONAL STUDENT_CITY], ZIP, [NUMBER], DATEOFBIRTH, [CASE], TRAVEL,
[ITO#], ITODATE, INSTRBEGINDATE, PRIVACYRELEASE, [INTERNATIONAL
STUDENT_EMAIL], EMAIL2, NEEDSSPONSOR, HAVESPONSOR, INTERNATIONAL,
WILLINGTOSPONSOR, ACCOMPANIED, [INTERNATIONAL STUDENT_#OFCHILDREN],
[INTERNATIONAL STUDENT_SPOUSEFIRSTNAME], LIVINGALLOWANCE, [INTERNATIONAL
STUDENT_MARITALSTATUS], TECHREF, ENGSCI, DLI, BOQCHECKOUTFOLIO,
[INTERNATIONAL STUDENT_COMMENTS], [ID#], [INTERNATIONAL STUDENT_SPONSORCODE] )
SELECT Archive.EMPLOYEEID, Archive.LASTNAME, Archive.[OTHER NAME],
Archive.FIRSTNAME, Archive.MI, Archive.COUNTRYCODE, Archive.COUNTRYNAME,
Archive.COUNTRYADJECTIVE, Archive.ODCADDRESS, Archive.NETSAFA, Archive.RANK,
Archive.DATEOFRANK, Archive.SERVICE, Archive.SENIOROFFICER, Archive.WCN,
Archive.CT, Archive.[ID#STUDENT], Archive.[ID#SPOUSE], Archive.CURR,
Archive.CURRCODE, Archive.CURRICSHORTNAME, Archive.GRADDATES,
Archive.DEGREE1, Archive.DEGREE2, Archive.TOEFL, Archive.ARRIVAL,
Archive.DEPARTURE, Archive.[FMS/IMET], Archive.[INTERNATIONAL
STUDENT_ADDRESS], Archive.[INTERNATIONAL STUDENT_CITY], Archive.ZIP,
Archive.NUMBER, Archive.DATEOFBIRTH, Archive.CASE, Archive.TRAVEL,
Archive.[ITO#], Archive.ITODATE, Archive.INSTRBEGINDATE,
Archive.PRIVACYRELEASE, Archive.[INTERNATIONAL STUDENT_EMAIL],
Archive.EMAIL2, Archive.NEEDSSPONSOR, Archive.HAVESPONSOR,
Archive.INTERNATIONAL, Archive.WILLINGTOSPONSOR, Archive.ACCOMPANIED,
Archive.[INTERNATIONAL STUDENT_#OFCHILDREN], Archive.[INTERNATIONAL
STUDENT_SPOUSEFIRSTNAME], Archive.LIVINGALLOWANCE, Archive.[INTERNATIONAL
STUDENT_MARITALSTATUS], Archive.TECHREF, Archive.ENGSCI, Archive.DLI,
Archive.BOQCHECKOUTFOLIO, Archive.[INTERNATIONAL STUDENT_COMMENTS],
Archive.[ID#], Archive.[INTERNATIONAL STUDENT_SPONSORCODE]
FROM Archive
GROUP BY Archive.EMPLOYEEID, Archive.LASTNAME, Archive.[OTHER NAME],
Archive.FIRSTNAME, Archive.MI, Archive.COUNTRYCODE, Archive.COUNTRYNAME,
Archive.COUNTRYADJECTIVE, Archive.ODCADDRESS, Archive.NETSAFA, Archive.RANK,
Archive.DATEOFRANK, Archive.SERVICE, Archive.SENIOROFFICER, Archive.WCN,
Archive.CT, Archive.[ID#STUDENT], Archive.[ID#SPOUSE], Archive.CURR,
Archive.CURRCODE, Archive.CURRICSHORTNAME, Archive.GRADDATES,
Archive.DEGREE1, Archive.DEGREE2, Archive.TOEFL, Archive.ARRIVAL,
Archive.DEPARTURE, Archive.[FMS/IMET], Archive.[INTERNATIONAL
STUDENT_ADDRESS], Archive.[INTERNATIONAL STUDENT_CITY], Archive.ZIP,
Archive.NUMBER, Archive.DATEOFBIRTH, Archive.CASE, Archive.TRAVEL,
Archive.[ITO#], Archive.ITODATE, Archive.INSTRBEGINDATE,
Archive.PRIVACYRELEASE, Archive.[INTERNATIONAL STUDENT_EMAIL],
Archive.EMAIL2, Archive.NEEDSSPONSOR, Archive.HAVESPONSOR,
Archive.INTERNATIONAL, Archive.WILLINGTOSPONSOR, Archive.ACCOMPANIED,
Archive.[INTERNATIONAL STUDENT_#OFCHILDREN], Archive.[INTERNATIONAL
STUDENT_SPOUSEFIRSTNAME], Archive.LIVINGALLOWANCE, Archive.[INTERNATIONAL
STUDENT_MARITALSTATUS], Archive.TECHREF, Archive.ENGSCI, Archive.DLI,
Archive.BOQCHECKOUTFOLIO, Archive.[INTERNATIONAL STUDENT_COMMENTS],
Archive.[ID#], Archive.[INTERNATIONAL STUDENT_SPONSORCODE]
HAVING (((Archive.DEPARTURE)=[Enter Departure Date-For Example: 12/04]));


Jerry Whittle said:
Please provide the SQL for this query. Also do the tables used in the queries
have subdatasheets or lookup fields at table level?
Click to expand...
Click to expand...
 
Back
Top