Datasheet Subform Query
in [Form]
|
From: Konchetta via AccessMonster.com on 27 May 2010 10:16 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/Forums.aspx/access-forms/201005/1
From: Daryl S on 27 May 2010 15:58 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/Forums.aspx/access-forms/201005/1 > > . >
From: Konchetta via AccessMonster.com on 27 May 2010 16:14 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/Forums.aspx/access-forms/201005/1
From: Konchetta via AccessMonster.com on 27 May 2010 16:29 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/Forums.aspx/access-forms/201005/1
From: Daryl S on 28 May 2010 11:14
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/Forums.aspx/access-forms/201005/1 > > . > |