Use values from a form as criteria in a DSum

Hello All,

I have a report in which the end user selects a single person or multiple 
people from a form and then I store their selection(s) in a temp table 
(ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
so that I can sort and group it properly.  All the fields are populated via 
DSum function because the final report resembles a crosstab report that may 
contain rows with no data and yet the rows still need to be seen.  My 
difficulty is in referencing the personID stored in the temp table 
([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
field on the report that stores the personID value (fldIsuNm), but I can't 
seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
help would be greatly appreciated.

Sincerely,
Shaun

DSum Example:

=DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
= 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/20/2007 9:54:02 PM
access.reports 4434 articles. 0 followers. Follow

8 Replies
1041 Views

Similar Articles

[PageSpeed] 10

Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
 = 1 AND[AssetNm] = 'Corporate'")
-- 
Duane Hookom
Microsoft Access MVP


"Shytown_Turk" wrote:

> Hello All,
> 
> I have a report in which the end user selects a single person or multiple 
> people from a form and then I store their selection(s) in a temp table 
> (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> so that I can sort and group it properly.  All the fields are populated via 
> DSum function because the final report resembles a crosstab report that may 
> contain rows with no data and yet the rows still need to be seen.  My 
> difficulty is in referencing the personID stored in the temp table 
> ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> field on the report that stores the personID value (fldIsuNm), but I can't 
> seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> help would be greatly appreciated.
> 
> Sincerely,
> Shaun
> 
> DSum Example:
> 
> =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 2:50:02 AM
Duane,

Unfortunately I can't because tblXHolding is a table that is the result of a 
make table query in which ttmp_Prn_Isu is used to filter the data from a much 
larger dataset.  

For example, each row of data will contain the following fields:  IsuNm 
(person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
Year), ExpMt (Expiration Month), and ShareTotal .

The report that I am trying to populate is a "fake" crosstab or grid like 
report because the client's requirements need it to display rows even if 
there is no data for those rows, which is the reason I could not use just a 
straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
And the row headers are the four AssetNm categories.  ShareTotal is the value 
that is used to populate each textbox on the report.

My intention was to use the DLookup (I was fried when I posted DSum, but 
your advice helped me with that) to check each field’s value.  As a result, I 
set the record source of the report to the temp table because in addition I 
would like to group the report based on the end-user selection and have each 
subset of data on its own page.  I was hoping by using the record source I 
would be able to reference the individual dynamically in my DLookup.  Please 
let me know if there are any other details that are needed.

Thanks, 
Shaun

"Duane Hookom" wrote:

> Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
>  = 1 AND[AssetNm] = 'Corporate'")
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Shytown_Turk" wrote:
> 
> > Hello All,
> > 
> > I have a report in which the end user selects a single person or multiple 
> > people from a form and then I store their selection(s) in a temp table 
> > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > so that I can sort and group it properly.  All the fields are populated via 
> > DSum function because the final report resembles a crosstab report that may 
> > contain rows with no data and yet the rows still need to be seen.  My 
> > difficulty is in referencing the personID stored in the temp table 
> > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > field on the report that stores the personID value (fldIsuNm), but I can't 
> > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > help would be greatly appreciated.
> > 
> > Sincerely,
> > Shaun
> > 
> > DSum Example:
> > 
> > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 2:48:02 PM
You can make sure rows are added to any query by creating a new query and 
using a left or right join that includes all the records from another table 
or query. I expect you could create a query of all AssetNm categories and 
years. Join this with your current crosstab to fill out all your required 
rows.


-- 
Duane Hookom
Microsoft Access MVP


"Shytown_Turk" wrote:

> Duane,
> 
> Unfortunately I can't because tblXHolding is a table that is the result of a 
> make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> larger dataset.  
> 
> For example, each row of data will contain the following fields:  IsuNm 
> (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> Year), ExpMt (Expiration Month), and ShareTotal .
> 
> The report that I am trying to populate is a "fake" crosstab or grid like 
> report because the client's requirements need it to display rows even if 
> there is no data for those rows, which is the reason I could not use just a 
> straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> And the row headers are the four AssetNm categories.  ShareTotal is the value 
> that is used to populate each textbox on the report.
> 
> My intention was to use the DLookup (I was fried when I posted DSum, but 
> your advice helped me with that) to check each field’s value.  As a result, I 
> set the record source of the report to the temp table because in addition I 
> would like to group the report based on the end-user selection and have each 
> subset of data on its own page.  I was hoping by using the record source I 
> would be able to reference the individual dynamically in my DLookup.  Please 
> let me know if there are any other details that are needed.
> 
> Thanks, 
> Shaun
> 
> "Duane Hookom" wrote:
> 
> > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> >  = 1 AND[AssetNm] = 'Corporate'")
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "Shytown_Turk" wrote:
> > 
> > > Hello All,
> > > 
> > > I have a report in which the end user selects a single person or multiple 
> > > people from a form and then I store their selection(s) in a temp table 
> > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > so that I can sort and group it properly.  All the fields are populated via 
> > > DSum function because the final report resembles a crosstab report that may 
> > > contain rows with no data and yet the rows still need to be seen.  My 
> > > difficulty is in referencing the personID stored in the temp table 
> > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > help would be greatly appreciated.
> > > 
> > > Sincerely,
> > > Shaun
> > > 
> > > DSum Example:
> > > 
> > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 3:42:03 PM
Duane,

Let me add another wrinkle, the expiration year row for the report is 
dynamic or a rolling 5 year.  For example, I need to classify everything via 
the next five years.  First row would be 2007, second row would be 2008, 
third row would be 2009, fourth would be 2010, and fifth year would be 2011.  
So next January it would be 2008, 2009, 2010, 2011, 2012.

Thanks
Shaun

"Duane Hookom" wrote:

> You can make sure rows are added to any query by creating a new query and 
> using a left or right join that includes all the records from another table 
> or query. I expect you could create a query of all AssetNm categories and 
> years. Join this with your current crosstab to fill out all your required 
> rows.
> 
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Shytown_Turk" wrote:
> 
> > Duane,
> > 
> > Unfortunately I can't because tblXHolding is a table that is the result of a 
> > make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> > larger dataset.  
> > 
> > For example, each row of data will contain the following fields:  IsuNm 
> > (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> > Year), ExpMt (Expiration Month), and ShareTotal .
> > 
> > The report that I am trying to populate is a "fake" crosstab or grid like 
> > report because the client's requirements need it to display rows even if 
> > there is no data for those rows, which is the reason I could not use just a 
> > straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> > feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> > And the row headers are the four AssetNm categories.  ShareTotal is the value 
> > that is used to populate each textbox on the report.
> > 
> > My intention was to use the DLookup (I was fried when I posted DSum, but 
> > your advice helped me with that) to check each field’s value.  As a result, I 
> > set the record source of the report to the temp table because in addition I 
> > would like to group the report based on the end-user selection and have each 
> > subset of data on its own page.  I was hoping by using the record source I 
> > would be able to reference the individual dynamically in my DLookup.  Please 
> > let me know if there are any other details that are needed.
> > 
> > Thanks, 
> > Shaun
> > 
> > "Duane Hookom" wrote:
> > 
> > > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > >  = 1 AND[AssetNm] = 'Corporate'")
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "Shytown_Turk" wrote:
> > > 
> > > > Hello All,
> > > > 
> > > > I have a report in which the end user selects a single person or multiple 
> > > > people from a form and then I store their selection(s) in a temp table 
> > > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > > so that I can sort and group it properly.  All the fields are populated via 
> > > > DSum function because the final report resembles a crosstab report that may 
> > > > contain rows with no data and yet the rows still need to be seen.  My 
> > > > difficulty is in referencing the personID stored in the temp table 
> > > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > > help would be greatly appreciated.
> > > > 
> > > > Sincerely,
> > > > Shaun
> > > > 
> > > > DSum Example:
> > > > 
> > > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 4:04:03 PM
I'm not sure what difference this makes. Are you suggesting you don't know 
how to create a query of each year for each AssetNm? If this is the case, you 
might want to provide some additional information about your tables etc. I 
expect you have a table of unique AssetNm values.

-- 
Duane Hookom
Microsoft Access MVP


"Shytown_Turk" wrote:

> Duane,
> 
> Let me add another wrinkle, the expiration year row for the report is 
> dynamic or a rolling 5 year.  For example, I need to classify everything via 
> the next five years.  First row would be 2007, second row would be 2008, 
> third row would be 2009, fourth would be 2010, and fifth year would be 2011.  
> So next January it would be 2008, 2009, 2010, 2011, 2012.
> 
> Thanks
> Shaun
> 
> "Duane Hookom" wrote:
> 
> > You can make sure rows are added to any query by creating a new query and 
> > using a left or right join that includes all the records from another table 
> > or query. I expect you could create a query of all AssetNm categories and 
> > years. Join this with your current crosstab to fill out all your required 
> > rows.
> > 
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "Shytown_Turk" wrote:
> > 
> > > Duane,
> > > 
> > > Unfortunately I can't because tblXHolding is a table that is the result of a 
> > > make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> > > larger dataset.  
> > > 
> > > For example, each row of data will contain the following fields:  IsuNm 
> > > (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> > > Year), ExpMt (Expiration Month), and ShareTotal .
> > > 
> > > The report that I am trying to populate is a "fake" crosstab or grid like 
> > > report because the client's requirements need it to display rows even if 
> > > there is no data for those rows, which is the reason I could not use just a 
> > > straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> > > feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> > > And the row headers are the four AssetNm categories.  ShareTotal is the value 
> > > that is used to populate each textbox on the report.
> > > 
> > > My intention was to use the DLookup (I was fried when I posted DSum, but 
> > > your advice helped me with that) to check each field’s value.  As a result, I 
> > > set the record source of the report to the temp table because in addition I 
> > > would like to group the report based on the end-user selection and have each 
> > > subset of data on its own page.  I was hoping by using the record source I 
> > > would be able to reference the individual dynamically in my DLookup.  Please 
> > > let me know if there are any other details that are needed.
> > > 
> > > Thanks, 
> > > Shaun
> > > 
> > > "Duane Hookom" wrote:
> > > 
> > > > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > > > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > >  = 1 AND[AssetNm] = 'Corporate'")
> > > > -- 
> > > > Duane Hookom
> > > > Microsoft Access MVP
> > > > 
> > > > 
> > > > "Shytown_Turk" wrote:
> > > > 
> > > > > Hello All,
> > > > > 
> > > > > I have a report in which the end user selects a single person or multiple 
> > > > > people from a form and then I store their selection(s) in a temp table 
> > > > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > > > so that I can sort and group it properly.  All the fields are populated via 
> > > > > DSum function because the final report resembles a crosstab report that may 
> > > > > contain rows with no data and yet the rows still need to be seen.  My 
> > > > > difficulty is in referencing the personID stored in the temp table 
> > > > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > > > help would be greatly appreciated.
> > > > > 
> > > > > Sincerely,
> > > > > Shaun
> > > > > 
> > > > > DSum Example:
> > > > > 
> > > > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 4:22:08 PM
Duane,

What would be the best way to post my table or database information?  

Shaun

"Duane Hookom" wrote:

> I'm not sure what difference this makes. Are you suggesting you don't know 
> how to create a query of each year for each AssetNm? If this is the case, you 
> might want to provide some additional information about your tables etc. I 
> expect you have a table of unique AssetNm values.
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Shytown_Turk" wrote:
> 
> > Duane,
> > 
> > Let me add another wrinkle, the expiration year row for the report is 
> > dynamic or a rolling 5 year.  For example, I need to classify everything via 
> > the next five years.  First row would be 2007, second row would be 2008, 
> > third row would be 2009, fourth would be 2010, and fifth year would be 2011.  
> > So next January it would be 2008, 2009, 2010, 2011, 2012.
> > 
> > Thanks
> > Shaun
> > 
> > "Duane Hookom" wrote:
> > 
> > > You can make sure rows are added to any query by creating a new query and 
> > > using a left or right join that includes all the records from another table 
> > > or query. I expect you could create a query of all AssetNm categories and 
> > > years. Join this with your current crosstab to fill out all your required 
> > > rows.
> > > 
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "Shytown_Turk" wrote:
> > > 
> > > > Duane,
> > > > 
> > > > Unfortunately I can't because tblXHolding is a table that is the result of a 
> > > > make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> > > > larger dataset.  
> > > > 
> > > > For example, each row of data will contain the following fields:  IsuNm 
> > > > (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> > > > Year), ExpMt (Expiration Month), and ShareTotal .
> > > > 
> > > > The report that I am trying to populate is a "fake" crosstab or grid like 
> > > > report because the client's requirements need it to display rows even if 
> > > > there is no data for those rows, which is the reason I could not use just a 
> > > > straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> > > > feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> > > > And the row headers are the four AssetNm categories.  ShareTotal is the value 
> > > > that is used to populate each textbox on the report.
> > > > 
> > > > My intention was to use the DLookup (I was fried when I posted DSum, but 
> > > > your advice helped me with that) to check each field’s value.  As a result, I 
> > > > set the record source of the report to the temp table because in addition I 
> > > > would like to group the report based on the end-user selection and have each 
> > > > subset of data on its own page.  I was hoping by using the record source I 
> > > > would be able to reference the individual dynamically in my DLookup.  Please 
> > > > let me know if there are any other details that are needed.
> > > > 
> > > > Thanks, 
> > > > Shaun
> > > > 
> > > > "Duane Hookom" wrote:
> > > > 
> > > > > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > > > > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > >  = 1 AND[AssetNm] = 'Corporate'")
> > > > > -- 
> > > > > Duane Hookom
> > > > > Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Shytown_Turk" wrote:
> > > > > 
> > > > > > Hello All,
> > > > > > 
> > > > > > I have a report in which the end user selects a single person or multiple 
> > > > > > people from a form and then I store their selection(s) in a temp table 
> > > > > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > > > > so that I can sort and group it properly.  All the fields are populated via 
> > > > > > DSum function because the final report resembles a crosstab report that may 
> > > > > > contain rows with no data and yet the rows still need to be seen.  My 
> > > > > > difficulty is in referencing the personID stored in the temp table 
> > > > > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > > > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > > > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > > > > help would be greatly appreciated.
> > > > > > 
> > > > > > Sincerely,
> > > > > > Shaun
> > > > > > 
> > > > > > DSum Example:
> > > > > > 
> > > > > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 4:36:09 PM
Type it in the format like:
tblYourTable1
=============
FirstField  autonumber PK
SecondField FK to tblYourTable2.FirstField
....

tblYourTable2
===============
FirstField autonumber PK
SecondField text
.....

-- 
Duane Hookom
Microsoft Access MVP


"Shytown_Turk" wrote:

> Duane,
> 
> What would be the best way to post my table or database information?  
> 
> Shaun
> 
> "Duane Hookom" wrote:
> 
> > I'm not sure what difference this makes. Are you suggesting you don't know 
> > how to create a query of each year for each AssetNm? If this is the case, you 
> > might want to provide some additional information about your tables etc. I 
> > expect you have a table of unique AssetNm values.
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "Shytown_Turk" wrote:
> > 
> > > Duane,
> > > 
> > > Let me add another wrinkle, the expiration year row for the report is 
> > > dynamic or a rolling 5 year.  For example, I need to classify everything via 
> > > the next five years.  First row would be 2007, second row would be 2008, 
> > > third row would be 2009, fourth would be 2010, and fifth year would be 2011.  
> > > So next January it would be 2008, 2009, 2010, 2011, 2012.
> > > 
> > > Thanks
> > > Shaun
> > > 
> > > "Duane Hookom" wrote:
> > > 
> > > > You can make sure rows are added to any query by creating a new query and 
> > > > using a left or right join that includes all the records from another table 
> > > > or query. I expect you could create a query of all AssetNm categories and 
> > > > years. Join this with your current crosstab to fill out all your required 
> > > > rows.
> > > > 
> > > > 
> > > > -- 
> > > > Duane Hookom
> > > > Microsoft Access MVP
> > > > 
> > > > 
> > > > "Shytown_Turk" wrote:
> > > > 
> > > > > Duane,
> > > > > 
> > > > > Unfortunately I can't because tblXHolding is a table that is the result of a 
> > > > > make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> > > > > larger dataset.  
> > > > > 
> > > > > For example, each row of data will contain the following fields:  IsuNm 
> > > > > (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> > > > > Year), ExpMt (Expiration Month), and ShareTotal .
> > > > > 
> > > > > The report that I am trying to populate is a "fake" crosstab or grid like 
> > > > > report because the client's requirements need it to display rows even if 
> > > > > there is no data for those rows, which is the reason I could not use just a 
> > > > > straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> > > > > feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> > > > > And the row headers are the four AssetNm categories.  ShareTotal is the value 
> > > > > that is used to populate each textbox on the report.
> > > > > 
> > > > > My intention was to use the DLookup (I was fried when I posted DSum, but 
> > > > > your advice helped me with that) to check each field’s value.  As a result, I 
> > > > > set the record source of the report to the temp table because in addition I 
> > > > > would like to group the report based on the end-user selection and have each 
> > > > > subset of data on its own page.  I was hoping by using the record source I 
> > > > > would be able to reference the individual dynamically in my DLookup.  Please 
> > > > > let me know if there are any other details that are needed.
> > > > > 
> > > > > Thanks, 
> > > > > Shaun
> > > > > 
> > > > > "Duane Hookom" wrote:
> > > > > 
> > > > > > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > > > > > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > >  = 1 AND[AssetNm] = 'Corporate'")
> > > > > > -- 
> > > > > > Duane Hookom
> > > > > > Microsoft Access MVP
> > > > > > 
> > > > > > 
> > > > > > "Shytown_Turk" wrote:
> > > > > > 
> > > > > > > Hello All,
> > > > > > > 
> > > > > > > I have a report in which the end user selects a single person or multiple 
> > > > > > > people from a form and then I store their selection(s) in a temp table 
> > > > > > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > > > > > so that I can sort and group it properly.  All the fields are populated via 
> > > > > > > DSum function because the final report resembles a crosstab report that may 
> > > > > > > contain rows with no data and yet the rows still need to be seen.  My 
> > > > > > > difficulty is in referencing the personID stored in the temp table 
> > > > > > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > > > > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > > > > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > > > > > help would be greatly appreciated.
> > > > > > > 
> > > > > > > Sincerely,
> > > > > > > Shaun
> > > > > > > 
> > > > > > > DSum Example:
> > > > > > > 
> > > > > > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 4:46:00 PM
Duane,

Here are the tables and subquery that I am using to create my crosstab.  The 
report needs to pull the data for the next five years broken down by month 
(12 column headers) and asset class name (4 row headers) for each IsuNm.  The 
following rules must be applied to format the report correctly:

1. Any security who's expiration year is greater than Year()+4 is treated as 
having an expiration year of Year()+4.
2. Even if there are no holdings for the year or the asset class name in 
question the row must show up with blanks.

Database Information:
-------------

tblAcct
=============
AcctID  autonumber PK
AcctNum
AcctNm
DateOpen
ISUID FK to tblIsu.IsuID
....

tblIsu
=============
IsuID  autonumber PK
IsuNum
ClientCodeID FK to tblClientCode.ClientCodeID
....


tblSecurity
=============
SecurityID  autonumber PK
SecurityNm
CUSIP
AssetClassID FK to tlkpAssetClass.AssetClassID
ExpDate
ExpMt
ExpYr
....


tlkpAssetClass
=============
AssetClassID  autonumber PK
AssetNm
Symbol
AssetClassID FK to tlkpAssetClass.AssetClassID
ExpDate
ExpMt
ExpYr
....


tblHolding
=============
SecurityID  
AcctID
CUSIP
ShareNum
CUSIP
....


qsubHolding_CashMargin
==============
SELECT tblHolding.Acct_Num, Sum(tblHolding.ShareNum) AS TotalShares, 
Sum(tblHolding.MktVal) AS SumOfMktVal, tblHolding.SecurityID, 
First(tblHolding.Price) AS FirstOfPrice, tblHolding.CUSIP
FROM tblHolding
GROUP BY tblHolding.Acct_Num, tblHolding.SecurityID, tblHolding.CUSIP;

Let me know if there is something else I can do to help.

Shaun

"Duane Hookom" wrote:

> Type it in the format like:
> tblYourTable1
> =============
> FirstField  autonumber PK
> SecondField FK to tblYourTable2.FirstField
> ...
> 
> tblYourTable2
> ===============
> FirstField autonumber PK
> SecondField text
> ....
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Shytown_Turk" wrote:
> 
> > Duane,
> > 
> > What would be the best way to post my table or database information?  
> > 
> > Shaun
> > 
> > "Duane Hookom" wrote:
> > 
> > > I'm not sure what difference this makes. Are you suggesting you don't know 
> > > how to create a query of each year for each AssetNm? If this is the case, you 
> > > might want to provide some additional information about your tables etc. I 
> > > expect you have a table of unique AssetNm values.
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "Shytown_Turk" wrote:
> > > 
> > > > Duane,
> > > > 
> > > > Let me add another wrinkle, the expiration year row for the report is 
> > > > dynamic or a rolling 5 year.  For example, I need to classify everything via 
> > > > the next five years.  First row would be 2007, second row would be 2008, 
> > > > third row would be 2009, fourth would be 2010, and fifth year would be 2011.  
> > > > So next January it would be 2008, 2009, 2010, 2011, 2012.
> > > > 
> > > > Thanks
> > > > Shaun
> > > > 
> > > > "Duane Hookom" wrote:
> > > > 
> > > > > You can make sure rows are added to any query by creating a new query and 
> > > > > using a left or right join that includes all the records from another table 
> > > > > or query. I expect you could create a query of all AssetNm categories and 
> > > > > years. Join this with your current crosstab to fill out all your required 
> > > > > rows.
> > > > > 
> > > > > 
> > > > > -- 
> > > > > Duane Hookom
> > > > > Microsoft Access MVP
> > > > > 
> > > > > 
> > > > > "Shytown_Turk" wrote:
> > > > > 
> > > > > > Duane,
> > > > > > 
> > > > > > Unfortunately I can't because tblXHolding is a table that is the result of a 
> > > > > > make table query in which ttmp_Prn_Isu is used to filter the data from a much 
> > > > > > larger dataset.  
> > > > > > 
> > > > > > For example, each row of data will contain the following fields:  IsuNm 
> > > > > > (person selected), AssetNm (Corporate, Muni, Govt, Intl), ExpYr (Expiration 
> > > > > > Year), ExpMt (Expiration Month), and ShareTotal .
> > > > > > 
> > > > > > The report that I am trying to populate is a "fake" crosstab or grid like 
> > > > > > report because the client's requirements need it to display rows even if 
> > > > > > there is no data for those rows, which is the reason I could not use just a 
> > > > > > straight crosstab.  Is there a way to add fixed “rows” like the fixed column 
> > > > > > feature?  The column headers are: ExpMt (Jan-Dec) for each ExpYr (Grouped).  
> > > > > > And the row headers are the four AssetNm categories.  ShareTotal is the value 
> > > > > > that is used to populate each textbox on the report.
> > > > > > 
> > > > > > My intention was to use the DLookup (I was fried when I posted DSum, but 
> > > > > > your advice helped me with that) to check each field’s value.  As a result, I 
> > > > > > set the record source of the report to the temp table because in addition I 
> > > > > > would like to group the report based on the end-user selection and have each 
> > > > > > subset of data on its own page.  I was hoping by using the record source I 
> > > > > > would be able to reference the individual dynamically in my DLookup.  Please 
> > > > > > let me know if there are any other details that are needed.
> > > > > > 
> > > > > > Thanks, 
> > > > > > Shaun
> > > > > > 
> > > > > > "Duane Hookom" wrote:
> > > > > > 
> > > > > > > Can't you combine tblXHolding with ttmp_prn_Isu in a query and use that in 
> > > > > > > your DSum("ShareTotal","qNewQuery","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > > >  = 1 AND[AssetNm] = 'Corporate'")
> > > > > > > -- 
> > > > > > > Duane Hookom
> > > > > > > Microsoft Access MVP
> > > > > > > 
> > > > > > > 
> > > > > > > "Shytown_Turk" wrote:
> > > > > > > 
> > > > > > > > Hello All,
> > > > > > > > 
> > > > > > > > I have a report in which the end user selects a single person or multiple 
> > > > > > > > people from a form and then I store their selection(s) in a temp table 
> > > > > > > > (ttmp_Prn_Isu).  I have a report that uses this temp table as its datasource 
> > > > > > > > so that I can sort and group it properly.  All the fields are populated via 
> > > > > > > > DSum function because the final report resembles a crosstab report that may 
> > > > > > > > contain rows with no data and yet the rows still need to be seen.  My 
> > > > > > > > difficulty is in referencing the personID stored in the temp table 
> > > > > > > > ([ttmp_Prn_Isu].[IsuNm]) as criteria in my DSum function.  I also have a 
> > > > > > > > field on the report that stores the personID value (fldIsuNm), but I can't 
> > > > > > > > seem to use it properly.  The field name in the temp table is 'IsuNm'.  Any 
> > > > > > > > help would be greatly appreciated.
> > > > > > > > 
> > > > > > > > Sincerely,
> > > > > > > > Shaun
> > > > > > > > 
> > > > > > > > DSum Example:
> > > > > > > > 
> > > > > > > > =DSum("ShareTotal","tblXHolding","[ExpYr] = Cstr(Year(Date())) AND [ExpMth] 
> > > > > > > > = 1 AND[AssetNm] = 'Corporate' And [IsuNm] = 'fldIsuNm'")
0
Utf
8/21/2007 7:36:03 PM
Reply:

Similar Artilces:

Using Business Unit
Hi, How I can set each Oportunity to specific Business Unit doesn't matter the Ower from Opportunity? I need creat a user that can see (only read) specific Oppotunities. ...

How to write xml file using existing XML Schema file?
My app used myDataset.xmlwrite to export dataset to xml file. It works fine for data but first two lines need to modify as existing XML Schema file. Below are the difference in first two lines: New xml file: <?xml version="1.0" standalone="yes" ?> <NewDataSet> <SpecSum> <USER>ANDYK</USER> <Grouper>Allergy/Immunology</Grouper> <Auths>0</Auths> <Cost>0</Cost> </SpecSum> <SpecSum> Old xml file: <?xml version="1.0" encoding="UTF-8" ?> ...

Changing Of Values After A Copy And Paste Special: Why ?
Hello, I tried the new user Excel Forum, but no luck. Would like to try here, as this really has me baffled, and a bit concerned. Using Excel 2007 and Windows7 This sure seems "funny," and was hoping someone might explain it for me: In B1 I have the value 154.22 In B2 I have the value 33.74 In E1 I have 154000000 In E2 I have 33740000 I copy from Column E, and do a Paste Special in Column B Column B looks correct, as it now has in it: B1 the value 154220000 B2 the value 33740000 But- E1 has changed to 1.5422E+14 E2 has changed to 3.374E+13 In th...

Cmd Click --- check the values first,append record and then delete them.
Hi all, I want to execute below code but it seems absolutely fail, please help: Private Sub AddSpec_cmd_Click() On Error GoTo Err_AddSpec_cmd_Click If IsNull(Me.Model) Then MsgBox " Enter Model Name", vbOKOnly, "Model name empty" End If Model.SetFocus If Inputvoltage.Value < 11 Then MsgBox " Input correct voltage rate ", vbOKOnly, "Input voltage" End If Dim db As Database Set db = CurrentDb db.Execute "Appendix model spec1_qry", dbFailOnError db.Execute &quo...

outlook forms #8
Stephen I need to know how you change the name of the tab/P.2 when creating an Outlook form. Also, how do you delete or create on the default message tab when creating a form. Thanks ...

Adding new record to a form
I have a form with a command button to add a new record. The first field on the form is a lookup field where I would like to select an item to fill in the field, the rest of the fields require data entry. When clicking the add record button a blank form opens but I cannot add any data to it. I can see the option in the lookup field but cannot select one. On the other fields that require data entry, I cannot enter anything. On the forms property I have 'allow additions, yes'. How can I get this to work? Bg1 - What is the recordsource for your form? If it is a query, t...

importing primary contacts using the bulk import
Hello all, I have successfully imported a csv file of all our accounts i exported the accountid to the contacts import excel sheet and did a vloolkup in excel and imported and matched the contacts to the accounts. Now i want to make one of the contacts a primary user, we did import into the dept a hidden field a Y if the user was going to be a primary user. I have exported out all the contactid and accountid from contactbase for users who have Y in the dept field using SQL ent manager How do i import into the accounts table matching on accountid, and update the primary contactid wi...

Forms: not working right with tables.
I am not totaly good with access. The problem is: I have many tables setup and working like I need them to. This is a church project. Table 1 is linked to table two. Table one stores only address, home phone and stuff related to family's that live at the same address and table two stores names, cell phone, birthdays and stuff related to a singel person. When I create a form to enter this information it works good like a charm. The problems start with table two and three. Table three will be used to keep a recoded of the attendaince. All tables are setup and working and linked with ...

Developing ActiveX controls using MFC in VS.NET
Hi, I am developing an ActiveX control using MFC, which is to be deployed on an web page. If I develop the control using MFC in VS.NET , will the client machine require .NET to be installed on it ? regards, Sachin Sachin Hegde wrote: > Hi, > I am developing an ActiveX control using MFC, which is to be deployed on an > web page. > If I develop the control using MFC in VS.NET , will the client machine > require .NET to be installed on it ? > > regards, > Sachin > > > No. Microsoft slaps the .NET name on everything, even if it does not use the .NET ru...

Sheet names used in formulas
I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! Hi! Here's one way: =INDIRECT("S"&ROW(A40)&"!$W$5") Which evaluates to: =S40!$W$5 As you drag copy down: =S40!...

Sending emails using access & outlook on citrix
We've recently switched our users over to citrix and now ever since then none of the email functions I have in my database are working. Prior to the switch the users had outlook on their desktops and when using the database they would trigger emails via command buttons, etc. It would then use there outlook to send the emails. Now that we're on citrix this function no longer works. Nothing has changed except for the users outlook is now on the citrix server instead of their desktop. How do I get this functionality back? Is there something special I need to do? More than like...

using excel to analyze time series data
I have two columns of data - column A has dates in chronological order and column B has corresponding closing prices. I'd like to programmically (or with a formula), find the first date of each month and add the corresponding closing prices and then divide by the number of closing prices that meet the criteria. Is this something that I can do in excel? Should I learn VBA? Or should I look elsewhere? Just want to also note that by the first date of each month, I meant the earliest date of each month, which in my data isn't always the 1st. On Jun 9, 9:32=A0am, eggman2001 <sod...@gm...

Size, Rotation, and Ordering not available for forms Excel 2008
I need to align a huge number of check boxes on a form. They are considered objects, so the Size, Rotation, and Ordering option should show up on the Formatting toolbar, but it's not. So how do I align and space 1000+ checkboxes? On 5/4/10 6:05 PM, fogharty wrote: > I need to align a huge number of check boxes on a form. They are > considered objects, so the Size, Rotation, and Ordering option should > show up on the Formatting toolbar, but it's not. So how do I align and > space 1000+ checkboxes? You are correct. Size rotation and ordering is not available for ...

the entity retrieved using SDK are empty
Hi: I used SDK to retrieve the entities such as Campaigns, Contacts, Roles from CRM 3.0 server. All their IDs (e.go. campaignID, contactID...) are non-null values but all other members are null. My questions are: (1) is it because my permission is not admin such that I cannot retrieve the entities using SDK? (2) I have manully created a campaign and two contact in my account. But I cannot even see those entities I created using SDK. If I am not an admin, should I suppose to see the entities I created myself? Or I do not suppose to see any entities if I am not an admin? (...

field references in forms
If I have a form "myform" with recordsource "mytable" and the table contains a field called "id," I can reference the current value of that field in expressions used for controls in the form by myform.id. This is true even when there is no control bound to mytable.ID on the form. I can "print" the value of myform.id in the debug immediate window. If I try the same thing for a form whose record source is a query, ? myform.id produces an error. Why the difference? And a corollary question: what's the most efficient way to test the ...

search form not to load all data up to start with
I have a form based on a query that i use to search for product in my database, on the form i have text box to type in, then a button to requery the query has the following Like "*" & [forms]![frmProductList]! [txtProductSearch] & "*" This query works fine but when i load up the form is shows all the products we stock which is a lot and seems to take a lot longer to load now, Is there a way for the form not to load up any data until o press the search button Try putting a default in [forms]![frmProductList]![txtProductSearch] that would not pro...

Changing values in a value box
Hi all, I have a data set that looks like this: DATE A$ B$ A% B% mar-14 50 100 33 66 mar-21 100 100 50 50 mar-28 75 25 75 25 I want to chart the percentages by each date, but I want the "value" box to display the dollars. The only way I can think to trick the Excel chart is create 2 x-axises and hide the $ axis, but then the $ values don't match the height of the % columns. Is there a way to link the $ values to the % columns? Thanks. Josh, To chart only the percentages, select the Date column, and also the two % columns (drag throug...

How to use Word 2000 as e-mail editor in Outlook 2003
Hello all, With the purchase of SBS 2003 Server came the ClientApps install of Outlook 2003. This upgraded all of the Outlook 2000's but now Word 2000 can't be used as the e-mail editor. The spot is greyed out and says Word 2003 for Editor. This has cost us our ability to fax out of Outlook since Word must be the editor for that function. Has anyone else encountered this and found a fix? Thanks, Todd There is no fix - Word and Outlook versions must match for automation to work - it has always been this way. --� Milly Staples [MVP - Outlook] Post all replies to the group ...

CreatedBy field use in Condition in Workflow
I can not use the CreatedBy field in the workflows condition. Does anyone know of a way around this. Thanks in advance You need to write ".Net assembly " to accomplish it :) . /Aamir ...

Using SqlBytes/SqlChar write method in CLR
I need to return Varchar(Max) from my CLR function. Using write method return error Buffer is insufficient. Can anyone share the sample to use write method. My return data can be upto 1GB Submitted via EggHeadCafe - Software Developer Portal of Choice Build a Selected Text Favorites Utility for your Web Site http://www.eggheadcafe.com/tutorials/aspnet/d72a2557-4ffd-4d29-bf1c-86feb39cae83/build-a-selected-text-fav.aspx Are you using DDL or VS autodeploy to deploy your CLR function? If you're using VS autodeploy, have you decorated your return value with the SqlFacet attribute ...

Accessing Contact fields from the opportunity form
Hi This may be a basic question but I have not been able to find the answer in lots of searching...or maybe I don't know when the answer is looking me in the face..... Users would like to be able to see some of the address fields of a contact in an opportunity which relates to this contact (so they don't have to go back to look at the contact details, they appear on the opportunity form). How do I go about getting the contact details via script when I am on the opportunity form? In the same vein, I would like to be able to break the contact name into firstname and lastname so that use...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

Counting number of records based on criteria
Hello, (Try again to get the question in the google-groups. Perhaps not pushed on the send button). For counting records as expression in a report in ms.access I'm using =count(*) which shows all records. Now I also want to know how many of them has registered a specific data in a specific field. I'm trying to use =count([Field1]="999") to count how many of the selection reported has registered data 999. This option isn't work. Can somebody give me the correct solution. thanks, Johan The 999 needs to go in the criteria for the query driving the...

[help please!] Extract selected data from html files using keywords?
Hi, I would like to keep some selected data in an Excel cell that is available between two keywords in an internet html page (to be downloaded by excel). For instance : Inside the html file, there is the following text : --- Number : 1258622 Sector : electric Net : 8.2 --- So I would like Excel to search between the keyword "Number" and "Sector" and keep the data "1258622" in a cell. Many thanks in advance for any help, ;-) Tid These lines are all in one cell? If yes, then this worked ok for me (assumes A1 contains the text): =--REPLACE(REPLACE(A1,SEARC...

Foreign keys using LINQ to SQL
Is there some way in .NET (3.5) to specify that my SQL server foreign links should use declarative referential integrity ("ON DELETE CASCADE")? My application is developed in C# for .NET 3.5 using LINQ to SQL where my database tables are linked with foreign key associations. The database is designed using the LINQ to SQL graphics designer (dbml). The "Edit Association" properties in the database designer do not seem to include foreign key constraints. Do I have to write specific code for each foreign key using T_SQL to "ALTER TABLE .. ADD CONSTRAINT&quo...