Calculating Dates before an event

jonobugs

Registered User.
Local time
Today, 01:07
Joined
Apr 15, 2013
Messages
70
I'm not sure what to title this one, but I'm looking for a solution to calculate dates depending on certain criteria.

First the background. I'm creating a birthday list for students. Each student attends class once a week on a specific weekday (Monday to Friday). I need to figure out a way to calculate a date 3 classes before their birthday because we take pictures of them for their birthday. Simple, right? Just use a DateDiff calculation I thought.

But ... here's the snag. We have certain days set aside for holidays and when a class falls on a holiday, they basically lose a week if I did that calculation. Which means that there are only 2 weeks of classes prior to their birthday.

I'm not a programmer, so I've been using queries (and nesting queries) to figure this problem out. I've managed to figure out a partial solution, but because I'm creating a Cartesian type setup I'm getting some students listed seven times or more depending on the criteria.

Is there a way to filter out names in queries that come out more than once?

The basic fields I'm using is Name, Date of Birth, Holidays (in a separate and unrelated table). There are a few other fields, but they are not really important for this. All the other fields, etc are calculated.

Any help is greatly appreciated! By the way, I'm NOT a programmer, so if you think this can only done with programming, please keep in mind that I may have no idea what you're talking about ... but I have created a few simple private sub routines.

PS: On an unrelated note, how does one mark a thread as solved? I can't seem to find the button.
 
I think some sort of criteria will filter out the results you don't want, if you continue to use your current set up. However, I think it might be possible to do this without the current method (cartesian join).

Can you post sample data? Show me 2 sets:

A. STarting data from your tables. Include table and field names of all relevant tables as well as enough sample data to cover all cases.

B. Expected results. Based on A, show me what data you expect this query to produce.
 
After working on it a lot, I was able to create two different query results.

1st query returned all the names and birthdays of students whose birthday follows a holiday and therefore need to be paid attention to earlier and a second table with the results of ALL the students.

I was unable to produce a table with only the students who did not have a birthday near a holiday as the reverse logic just doesn't work. What ends up happening is that I still get a list of ALL students since I'm using a Cartesian table.

ex)

Code:
Query 1                 Query 2                 

Bob                      Bob 
Susan                    Tim
Karen                    Ralph
James
Sally
Jacob
Tim
Ralph
So, Query one is a list of everyone and Query 2 only lists Bob, Tim and Ralph whose birthday falls just after a holiday and so miss a week. I want to find a way to take these two results and perhaps make another query that removes the names from Query 1 if they already appear in Query 2.

I didn't include all the other fields, etc to make this more understandable. I was thinking of making a temporary table of each and then use a delete query..but that seems like a bad way of doing something.
 
I think some sort of criteria will filter out the results you don't want, if you continue to use your current set up. However, I think it might be possible to do this without the current method (cartesian join).

Can you post sample data? Show me 2 sets:

A. STarting data from your tables. Include table and field names of all relevant tables as well as enough sample data to cover all cases.

B. Expected results. Based on A, show me what data you expect this query to produce.

As always, thanks for your reply. I'm a bit hesitant to post the database because I would have to delete a lot of information and I'm not sure if the queries would even work anymore after that. Anyway, if it's the only way, I'll do that, but hopefully you or someone else may have an answer to my last problem.
 
use Not In (), ie:

select names from Query1 Where names Not In (Select names from Query2);
 
another query that removes the names from Query 1 if they already appear in Query 2.

That sounds like what's call an unmatched query. I suggesting googling "unmatched query" and you could also try the Query Wizard, Find Unmatched Query Wizard"
 
That sounds like what's call an unmatched query. I suggesting googling "unmatched query" and you could also try the Query Wizard, Find Unmatched Query Wizard"


GREAT! That's exactly what I was looking for. It worked beautifully!

Now, can anyone tell me how to mark this thread as solved?

:D
 

Users who are viewing this thread

Back
Top Bottom