Unmatched Query creativity needed - date field

I have a table that contains ClientId and ScheduledDueDate (each Client must
submit a plan within 2 days on either side of the ScheduledDueDate), this
table contains various dates for each client 14 days apart, some schedules
are just 6 months long, others are as long as 2 years). Another table tracks
when the plans are actually received (ClientId, SubmittedDate, etc). The
only common field is ClientID. I can't set up the Unmatched Query Wizard to
get the results I'm looking for. How can I write a query to determine if the
plans submitted are within (or not within) 2 days on either side of a, or
any, scheduled date?

For Example:

tblClientSchedule
Client ID ScheduledDueDate
111 06/30/08
111 07/14/08
111 07/28/08
111 08/11/08

tblClientPlans
ClientID SubmittedDate Plant
111 7/01/08 Buy Yankees (good, met schedule)
111 7/12/08 Sell Yankees (good, met schedule)
111 8/04/08 Get a Job (not good, outside
deadline)

Any help would be very much appreciated.

On Jun 29, 1:35*am, Jane Schuster
wrote:
I have a table that contains ClientId and ScheduledDueDate (each Client must
submit a plan within 2 days on either side of the ScheduledDueDate), this
table contains various dates for each client 14 days apart, some schedules
are just 6 months long, others are as long as 2 years). *Another table tracks
when the plans are actually received *(ClientId, SubmittedDate, etc). *The
only common field is ClientID. *I can't set up the Unmatched Query Wizard to
get the results I'm looking for. *How can I write a query to determine if the
plans submitted are within (or not within) 2 days on either side of a, or
any, scheduled date?

For Example: *

tblClientSchedule
Client ID * ScheduledDueDate
111 * * * * * * * * * *06/30/08
111 * * * * * * * * * *07/14/08
111 * * * * * * * * * *07/28/08
111 * * * * * * * * * *08/11/08

tblClientPlans
ClientID * SubmittedDate * *Plant
111 * * * * * * * *7/01/08 * * * *Buy Yankees * * * (good, met schedule)
111 * * * * * * * *7/12/08 * * * *Sell Yankees * * * (good, met schedule)
111 * * * * * * * *8/04/08 * * * *Get a Job * * * * * *(not good, outside
deadline)

Any help would be very much appreciated.

SubmitDate Between ScheduledDueDate-2 and ScheduledDueDate+2

Ok, I tried that and I got the ones that were within 2 day (+or-). Is there
any way to get the one's that aren't? I'm obviously new to Access or I would
know how to reverse this, but I'm still new and I don't know. What I'm
really after (and I apologize for not being clear in my first post) is the
clients who are not in compliance with there schedules. I'm looking for
exceptions to the rule, not clients who are doing it right.


" wrote:

On Jun 29, 1:35 am, Jane Schuster
wrote
I have a table that contains ClientId and ScheduledDueDate (each Client mustI'm
submit a plan within 2 days on either side of the ScheduledDueDate), this
table contains various dates for each client 14 days apart, some schedules
are just 6 months long, others are as long as 2 years). Another table tracks
when the plans are actually received (ClientId, SubmittedDate, etc). The
only common field is ClientID. I can't set up the Unmatched Query Wizard to
get the results I'm looking for. How can I write a query to determine if the
plans submitted are within (or not within) 2 days on either side of a, or
any, scheduled date?

For Example:

tblClientSchedule
Client ID ScheduledDueDate
111 06/30/08
111 07/14/08
111 07/28/08
111 08/11/08

tblClientPlans
ClientID SubmittedDate Plant
111 7/01/08 Buy Yankees (good, met schedule)
111 7/12/08 Sell Yankees (good, met schedule)
111 8/04/08 Get a Job (not good, outside
deadline)

Any help would be very much appreciated.

SubmitDate Between ScheduledDueDate-2 and ScheduledDueDate+2

Try:
SubmitDate ScheduledDueDate-2 or SubmitDate ScheduledDueDate+2

Bob

Jane Schuster wrote:
Ok, I tried that and I got the ones that were within 2 day (+or-). Is there
any way to get the one's that aren't? I'm obviously new to Access or I would
know how to reverse this, but I'm still new and I don't know. What I'm
really after (and I apologize for not being clear in my first post) is the
clients who are not in compliance with there schedules. I'm looking for
exceptions to the rule, not clients who are doing it right.



On Jun 29, 1:35 am, Jane Schuster
wrote
[quoted text clipped - 27 lines]

SubmitDate Between ScheduledDueDate-2 and ScheduledDueDate+2

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

From my own experience of working within statutory time limits on the receipt
of representations the model doesn't look right to me. As the only means of
joining the tables is on Client ID a submission could be judged to fall
within the + or - 2 day period but in reality be late, possibly substantially
so, in relation to the deadline relevant to the original commission. If we
take your last example:

111 8/04/2008 Get a Job (not good, outside
deadline)

this is outside the parameters because it is later than 07/28/2008 + 2, and
before 08/11/2008 - 2. But if the client is even later with the submission
and doesn't do so until 08/09/2008, as far as the database is concerned the
deadline is met, which to my mind at least, would be an unusual business
model. Is that really your business model? If so then you should be able to
use a subquery to identify the errant submissions:

SELECT *
FROM tblClientPlans
WHERE NOT EXISTS
(SELECT *
FROM tblClientSchedule
WHERE tblClientSchedule.ClientID = tblClientPlans.ClientID
AND tblClientSchedule.ScheduledDueDate BETWEEN
DATEADD("d", -2, tblClientPlans.SubmittedDate) AND
DATEADD("d", +2, tblClientPlans.SubmittedDate));

If not read on:

If we look at this slightly more abstractly in terms of the relational model
the primary key of tblClientSchedule is a composite one of Client ID and
ScheduledDueDate. It follows therefore that the corresponding foreign key in
tblClientPlans should also be a composite one of Client ID and
ScheduledDueDate, i.e. you need to introduce a ScheduledDueDate column into
tblClientPlans.

With this revised model you wouldn't need to include the tblClientSchedule
table in a query to return the rows were the SubmittedDate falls outside the
permitted data range as its simply a question of comparing the values in two
columns in tblClientPlans:

SELECT *
FROM tblClientPlans
WHERE SubmittedDate DATEADD("d", -2 ScheduledDueDate)
OR SubmittedDate DATEADD("d", +2 ScheduledDueDate);

You would still need the tblClientSchedule table as, by enforcing
referential integrity between it and tblClientPlans it prevents invalid
Client ID and ScheduledDueDate values being entered into tblClientPlans.
The relationship is now on the two columns, Client ID and ScheduledDueDate,
of course.

For data entry purposes, when inserting rows into tblClientPlans, you'd have
a number of options for inserting the ScheduledDueDate. One would be to use
a combo box which lists all schedule dates from the current date onwards for
the current client, i.e. with a RowSource property of:

SELECT ScheduledDueDate
FROM tblClientSchedule
WHERE ScheduledDueDate =DATE()
AND ClientID = Form!ClientID;

Note that you can use the Form property here to refer to the current form
rather than using a full reference to the form. To get the combo box to show
only the current client's dates you'd requery the combo box in the ClientID
control's AfterUpdate event procedu

Me.cboScheduledDueDate.Requery

where cboScheduledDueDate is the name of the combo box bound to the
ScheduledDueDate field.

Alternatively, if appropriate, you could automatically insert the next
available date in the schedule after the current date. Use the same combo
box but this time in the ClientID control's AfterUpdate event procedure put:

Dim strCriteria as String

strCriteria = "ClientID = " & Me.ClientID & _
" And ScheduledDueDate #" & _
Format(VBA.Date,"mm/dd/yyyy") & "#"

Me.cboScheduledDueDate.Requery

Me.cboScheduledDueDate = _
DMin("ScheduledDueDate", "tblClientSchedule", strCriteria)

With this automatic date insertion you would of course be free to select a
later date from the combo box's list if necessary.

Ken Sheridan
Stafford, England

"Jane Schuster" wrote:

Ok, I tried that and I got the ones that were within 2 day (+or-). Is there
any way to get the one's that aren't? I'm obviously new to Access or I would
know how to reverse this, but I'm still new and I don't know. What I'm
really after (and I apologize for not being clear in my first post) is the
clients who are not in compliance with there schedules. I'm looking for
exceptions to the rule, not clients who are doing it right.


" wrote:

On Jun 29, 1:35 am, Jane Schuster
wrote
I have a table that contains ClientId and ScheduledDueDate (each Client mustI'm
submit a plan within 2 days on either side of the ScheduledDueDate), this
table contains various dates for each client 14 days apart, some schedules
are just 6 months long, others are as long as 2 years). Another table tracks
when the plans are actually received (ClientId, SubmittedDate, etc). The
only common field is ClientID. I can't set up the Unmatched Query Wizard to
get the results I'm looking for. How can I write a query to determine if the
plans submitted are within (or not within) 2 days on either side of a, or
any, scheduled date?

For Example:

tblClientSchedule
Client ID ScheduledDueDate
111 06/30/08
111 07/14/08
111 07/28/08
111 08/11/08

tblClientPlans
ClientID SubmittedDate Plant
111 7/01/08 Buy Yankees (good, met schedule)
111 7/12/08 Sell Yankees (good, met schedule)
111 8/04/08 Get a Job (not good, outside
deadline)

Any help would be very much appreciated.

SubmitDate Between ScheduledDueDate-2 and ScheduledDueDate+2

Ken,
Thank you so much. I knew the model wasn't exactly right, but didn't know
quite how to deal with it. Again, thank you.

"Ken Sheridan" wrote:

From my own experience of working within statutory time limits on the receipt
of representations the model doesn't look right to me. As the only means of
joining the tables is on Client ID a submission could be judged to fall
within the + or - 2 day period but in reality be late, possibly substantially
so, in relation to the deadline relevant to the original commission. If we
take your last example:

111 8/04/2008 Get a Job (not good, outside
deadline)

this is outside the parameters because it is later than 07/28/2008 + 2, and
before 08/11/2008 - 2. But if the client is even later with the submission
and doesn't do so until 08/09/2008, as far as the database is concerned the
deadline is met, which to my mind at least, would be an unusual business
model. Is that really your business model? If so then you should be able to
use a subquery to identify the errant submissions:

SELECT *
FROM tblClientPlans
WHERE NOT EXISTS
(SELECT *
FROM tblClientSchedule
WHERE tblClientSchedule.ClientID = tblClientPlans.ClientID
AND tblClientSchedule.ScheduledDueDate BETWEEN
DATEADD("d", -2, tblClientPlans.SubmittedDate) AND
DATEADD("d", +2, tblClientPlans.SubmittedDate));

If not read on:

If we look at this slightly more abstractly in terms of the relational model
the primary key of tblClientSchedule is a composite one of Client ID and
ScheduledDueDate. It follows therefore that the corresponding foreign key in
tblClientPlans should also be a composite one of Client ID and
ScheduledDueDate, i.e. you need to introduce a ScheduledDueDate column into
tblClientPlans.

With this revised model you wouldn't need to include the tblClientSchedule
table in a query to return the rows were the SubmittedDate falls outside the
permitted data range as its simply a question of comparing the values in two
columns in tblClientPlans:

SELECT *
FROM tblClientPlans
WHERE SubmittedDate DATEADD("d", -2 ScheduledDueDate)
OR SubmittedDate DATEADD("d", +2 ScheduledDueDate);

You would still need the tblClientSchedule table as, by enforcing
referential integrity between it and tblClientPlans it prevents invalid
Client ID and ScheduledDueDate values being entered into tblClientPlans.
The relationship is now on the two columns, Client ID and ScheduledDueDate,
of course.

For data entry purposes, when inserting rows into tblClientPlans, you'd have
a number of options for inserting the ScheduledDueDate. One would be to use
a combo box which lists all schedule dates from the current date onwards for
the current client, i.e. with a RowSource property of:

SELECT ScheduledDueDate
FROM tblClientSchedule
WHERE ScheduledDueDate =DATE()
AND ClientID = Form!ClientID;

Note that you can use the Form property here to refer to the current form
rather than using a full reference to the form. To get the combo box to show
only the current client's dates you'd requery the combo box in the ClientID
control's AfterUpdate event procedu

Me.cboScheduledDueDate.Requery

where cboScheduledDueDate is the name of the combo box bound to the
ScheduledDueDate field.

Alternatively, if appropriate, you could automatically insert the next
available date in the schedule after the current date. Use the same combo
box but this time in the ClientID control's AfterUpdate event procedure put:

Dim strCriteria as String

strCriteria = "ClientID = " & Me.ClientID & _
" And ScheduledDueDate #" & _
Format(VBA.Date,"mm/dd/yyyy") & "#"

Me.cboScheduledDueDate.Requery

Me.cboScheduledDueDate = _
DMin("ScheduledDueDate", "tblClientSchedule", strCriteria)

With this automatic date insertion you would of course be free to select a
later date from the combo box's list if necessary.

Ken Sheridan
Stafford, England

"Jane Schuster" wrote:

Ok, I tried that and I got the ones that were within 2 day (+or-). Is there
any way to get the one's that aren't? I'm obviously new to Access or I would
know how to reverse this, but I'm still new and I don't know. What I'm
really after (and I apologize for not being clear in my first post) is the
clients who are not in compliance with there schedules. I'm looking for
exceptions to the rule, not clients who are doing it right.


" wrote:

On Jun 29, 1:35 am, Jane Schuster
wrote
I have a table that contains ClientId and ScheduledDueDate (each Client mustI'm
submit a plan within 2 days on either side of the ScheduledDueDate), this
table contains various dates for each client 14 days apart, some schedules
are just 6 months long, others are as long as 2 years). Another table tracks
when the plans are actually received (ClientId, SubmittedDate, etc). The
only common field is ClientID. I can't set up the Unmatched Query Wizard to
get the results I'm looking for. How can I write a query to determine if the
plans submitted are within (or not within) 2 days on either side of a, or
any, scheduled date?

For Example:

tblClientSchedule
Client ID ScheduledDueDate
111 06/30/08
111 07/14/08
111 07/28/08
111 08/11/08

tblClientPlans
ClientID SubmittedDate Plant
111 7/01/08 Buy Yankees (good, met schedule)
111 7/12/08 Sell Yankees (good, met schedule)
111 8/04/08 Get a Job (not good, outside
deadline)

Any help would be very much appreciated.

SubmitDate Between ScheduledDueDate-2 and ScheduledDueDate+2