Datasheet Subform Query

[Konchetta via AccessMonster.com]

I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have
entered information for the 2009 Calendar Year and some information for the
2010 Calendar Year. I am trying to get my query to give me all records for
which fees have not been received for the 2009 and 2010 Calendar Years. I
tried the NULL function but that isn't working or maybe I am not using it
correctly. I placed IS NULL in the criteria for Calendar Year. Can this not
be done in a datasheet?

Thanks in advance for your assistance!!

 

[Daryl S]

Konchetta -

I suspect you only add records to the table when the fees are received. If
you enter a record and leave the CalendarYear field empty, then the query you
suggest would only kind of work, since you would not know if the NULL was for
2009 or 2010. Instead, if you only enter a record with a CalendarYear
populated (which is the right way to do it), then you are looking for records
that don't exist, rather than records that have a null value. Since you
didn't give any specifics, I will assume you have a table for your main form
(maybe Clients), and the subform comes from the table CalendarYear, which has
the ClientID as well as the CalendarYear field. The query you want is to
show all Clients that have no 2009 or 2010 CalendarYear records. It will
look something like this:

SELECT Clients.* FROM Clients
WHERE Not exists (Select 'X' from [CalendarYear] WHERE Clients.ClientID =
CalendarYear.ClientID AND CalendarYear.CalendarYear in (2009,2010));

You will need to use your table and field names for this. If you have
problems, post your SQL (in Query Design choose SQL View and copy/paste) so
we can help.

 

[Konchetta via AccessMonster.com]

Thanks Daryl,

Ohh ok I get it. I know I was doing something wrong. I will try this and see
if I can get it to work. Thanks for your help!

Konchetta -

I suspect you only add records to the table when the fees are received. If
you enter a record and leave the CalendarYear field empty, then the query you
suggest would only kind of work, since you would not know if the NULL was for
2009 or 2010. Instead, if you only enter a record with a CalendarYear
populated (which is the right way to do it), then you are looking for records
that don't exist, rather than records that have a null value. Since you
didn't give any specifics, I will assume you have a table for your main form
(maybe Clients), and the subform comes from the table CalendarYear, which has
the ClientID as well as the CalendarYear field. The query you want is to
show all Clients that have no 2009 or 2010 CalendarYear records. It will
look something like this:

SELECT Clients.* FROM Clients
WHERE Not exists (Select 'X' from [CalendarYear] WHERE Clients.ClientID =
CalendarYear.ClientID AND CalendarYear.CalendarYear in (2009,2010));

You will need to use your table and field names for this. If you have
problems, post your SQL (in Query Design choose SQL View and copy/paste) so
we can help.

I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have

[quoted text clipped - 6 lines]

Thanks in advance for your assistance!!

 

[Konchetta via AccessMonster.com]

Daryl,

My subform comes from the tblAOCfees which has the Calendar Year field and
ProjectID. I thought I understood your comment but then I thought about it
and was wondering should i create records for all my projects for the
calendar year? I'm confused but I will post my SQL which is probably horribly
designed.

SELECT tblAOCFees.[Calendar Year], tblAOCFees.[Number of Sched IIA Received],
MainProjectList.Project_Name, [ProjectType_1] & ("/"+[ProjectType_2]) & ("/"+
[ProjectType_3]) AS TypeProject, [ID_1] & ("/"+[ID_2]) & ("/"+[ID_3]) AS ID,
([Project_Address]+",") & (" "+[Project_City]) AS ConcatAddress, tblAOCFees.
[Date Received], tblAOCFees.CheckNumber, tblAOCFees.[Exhibit B], tblAOCFees.
[Exhibit C], tblAOCFees.[Sched IIA], tblAOCFees.[Util Allow Chart],
MainProjectList.[Inactivate Record]
FROM MainProjectList LEFT JOIN tblAOCFees ON MainProjectList.Project_ID =
tblAOCFees.Project_ID
WHERE (((tblAOCFees.[Calendar Year]) Is Null) AND ((MainProjectList.
[Inactivate Record])=False));

Hope this doesn't confuse you!!

Konchetta -

I suspect you only add records to the table when the fees are received. If
you enter a record and leave the CalendarYear field empty, then the query you
suggest would only kind of work, since you would not know if the NULL was for
2009 or 2010. Instead, if you only enter a record with a CalendarYear
populated (which is the right way to do it), then you are looking for records
that don't exist, rather than records that have a null value. Since you
didn't give any specifics, I will assume you have a table for your main form
(maybe Clients), and the subform comes from the table CalendarYear, which has
the ClientID as well as the CalendarYear field. The query you want is to
show all Clients that have no 2009 or 2010 CalendarYear records. It will
look something like this:

SELECT Clients.* FROM Clients
WHERE Not exists (Select 'X' from [CalendarYear] WHERE Clients.ClientID =
CalendarYear.ClientID AND CalendarYear.CalendarYear in (2009,2010));

You will need to use your table and field names for this. If you have
problems, post your SQL (in Query Design choose SQL View and copy/paste) so
we can help.

I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have

[quoted text clipped - 6 lines]

Thanks in advance for your assistance!!

 

[Daryl S]

Konchetta -

This query will show you all MainProjectList records that don't have a 2009
or 2010 tblAOCFees record (untested):

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False))
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));

--
Daryl S

Daryl,

My subform comes from the tblAOCfees which has the Calendar Year field and
ProjectID. I thought I understood your comment but then I thought about it
and was wondering should i create records for all my projects for the
calendar year? I'm confused but I will post my SQL which is probably horribly
designed.

SELECT tblAOCFees.[Calendar Year], tblAOCFees.[Number of Sched IIA Received],
MainProjectList.Project_Name, [ProjectType_1] & ("/"+[ProjectType_2]) & ("/"+
[ProjectType_3]) AS TypeProject, [ID_1] & ("/"+[ID_2]) & ("/"+[ID_3]) AS ID,
([Project_Address]+",") & (" "+[Project_City]) AS ConcatAddress, tblAOCFees.
[Date Received], tblAOCFees.CheckNumber, tblAOCFees.[Exhibit B], tblAOCFees.
[Exhibit C], tblAOCFees.[Sched IIA], tblAOCFees.[Util Allow Chart],
MainProjectList.[Inactivate Record]
FROM MainProjectList LEFT JOIN tblAOCFees ON MainProjectList.Project_ID =
tblAOCFees.Project_ID
WHERE (((tblAOCFees.[Calendar Year]) Is Null) AND ((MainProjectList.
[Inactivate Record])=False));

Hope this doesn't confuse you!!

Konchetta -

I suspect you only add records to the table when the fees are received. If
you enter a record and leave the CalendarYear field empty, then the query you
suggest would only kind of work, since you would not know if the NULL was for
2009 or 2010. Instead, if you only enter a record with a CalendarYear
populated (which is the right way to do it), then you are looking for records
that don't exist, rather than records that have a null value. Since you
didn't give any specifics, I will assume you have a table for your main form
(maybe Clients), and the subform comes from the table CalendarYear, which has
the ClientID as well as the CalendarYear field. The query you want is to
show all Clients that have no 2009 or 2010 CalendarYear records. It will
look something like this:

SELECT Clients.* FROM Clients
WHERE Not exists (Select 'X' from [CalendarYear] WHERE Clients.ClientID =
CalendarYear.ClientID AND CalendarYear.CalendarYear in (2009,2010));

You will need to use your table and field names for this. If you have
problems, post your SQL (in Query Design choose SQL View and copy/paste) so
we can help.

I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have

[quoted text clipped - 6 lines]

Thanks in advance for your assistance!!

--



.

 

[Konchetta via AccessMonster.com]

Thanks Daryl, I will try this. I will appreciate your help.

Konchetta -

This query will show you all MainProjectList records that don't have a 2009
or 2010 tblAOCFees record (untested):

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False))
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));

Daryl,

[quoted text clipped - 45 lines]

 

[Konchetta via AccessMonster.com]

hello Daryl,
It says there is an extra ) in query expression...

Konchetta -

This query will show you all MainProjectList records that don't have a 2009
or 2010 tblAOCFees record (untested):

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False))
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));

Daryl,

[quoted text clipped - 45 lines]

 

[Daryl S]

Sure was. Try this:

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False)
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));



--
Daryl S

hello Daryl,
It says there is an extra ) in query expression...

Konchetta -

This query will show you all MainProjectList records that don't have a 2009
or 2010 tblAOCFees record (untested):

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False))
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));

Daryl,

[quoted text clipped - 45 lines]

Thanks in advance for your assistance!!

 

[Konchetta via AccessMonster.com]

I think I got it to work Mr. Daryl. Another question, for upcoming years that
we receive fees, should I just add that calendar year to the query?

Konchetta -

This query will show you all MainProjectList records that don't have a 2009
or 2010 tblAOCFees record (untested):

SELECT MainProjectList.*
FROM MainProjectList
WHERE ((MainProjectList.[Inactivate Record])=False))
AND (Not exists (SELECT 'X' FROM tblAOCFees WHERE
MainProjectList.Project_ID = tblAOCFees.Project_ID
AND tblAOCFees.CalendarYear in (2009, 2010)));

Daryl,

[quoted text clipped - 45 lines]