Hi all, I'm currently working on a db to track reservists brought up to active duty in excess of 31 days. These members are required to submit for travel reimbursement every 30 days until the end of their orders.

Info on the table structure is below. Each member will have a record in tblMarines, while only those that submitted for travel reimbursement will have a record in tblTravel. Its a One-to-Many relationship.

tblMarines
[DCTB] this is the start date of their orders.
[EAS] this is the end date of their orders.

tblTravel
[CLAIM_TYPE] the type of claim (partial, supplemental, or initial).
[START_DATE] start date of the period covered on the reimbursible period
[END_DATE] end date of the period covered on the reimbursible period

I would include a calculated field in the query that would return true: iff(datediff("d", [DCTB],[EAS])>30, "TRUE","FALSE")

Now for the big question:
Since a record in tblTravel isn't created until the member submits a travel reimbursement, How would I be able to filter out the members that have submitted a claim for the current month?

If this isn't enough information, please let me know.

Edited by: marinesct on Tue Dec 18 11:26:34 EST 2007.

I'm not quite sure what you're asking for. Are you saying that you want a list of all members who have not submitted a claim in the current month?

You can use a subquery, but I'm afraid you haven't given quite enough details for me to give you the SQL. Specifically, I don't know enough about START_DATE and END_DATE to be able to filter only those records in tblTravel which are for the current month. Will they all be 2007/12/01 to 2007/12/31 for this month? If so, you can use something like:

SELECT Id
FROM tblMarines
WHERE Id NOT IN (SELECT Id FROM tblTravel WHERE Date() BETWEEN [START_DATE] AND [END_DATE])

Doug, That looks like it should do it.

What I will need to do is run some dummy data into the database to verify since this db isnt implemented yet. I'll post details afterwards.

I ran the sample data. Used the SQL you suggested and didn't get the correct information. It still showed the previous claims as well.

So I modified it as follows:

SELECT Id
FROM tblMarines
WHERE Id NOT IN (SELECT Id FROM tblTravel WHERE SubtractWeekDays(-5,[MBR_SUBM]) BETWEEN [START_DATE] AND [END_DATE])

The MBR_SUBM is a field I just created to help QA the timeliness of the processing. Since the member has 5 working days to submit the claim, I utilized the function listed here to count the working days back from MBR_SUBM. This should work. With the differant sets of data I inputed, it seems to be working rather well.

I've attached the sample data. Employee's 16 and 65 haven't submitted for reimbursement for December yet. However, they aren't showing up in the query as they should. The modification kept the previous submissions filtered out, which is good.