A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Datasheet Subform Query



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 03:16 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

Ads
  #2  
Old May 27th, 2010, 08:58 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Datasheet Subform Query

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.

--
Daryl S


"Konchetta via AccessMonster.com" wrote:

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!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

.

  #3  
Old May 27th, 2010, 09:14 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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!

Daryl S wrote:
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!!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #4  
Old May 27th, 2010, 09:29 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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!!

Daryl S wrote:
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!!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #5  
Old May 28th, 2010, 04:14 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Datasheet Subform 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 S


"Konchetta via AccessMonster.com" wrote:

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!!

Daryl S wrote:
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!!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

.

  #6  
Old May 28th, 2010, 05:25 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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

Daryl S wrote:
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!!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #7  
Old May 28th, 2010, 05:27 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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

Daryl S wrote:
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!!


--
Message posted via http://www.accessmonster.com

  #8  
Old May 28th, 2010, 06:44 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Datasheet Subform Query

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


"Konchetta via AccessMonster.com" wrote:

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

Daryl S wrote:
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!!


--
Message posted via http://www.accessmonster.com

.

  #9  
Old May 28th, 2010, 06:53 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

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?

Daryl S wrote:
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!!


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.