Use values from a form as criteria in a DSum

[Guest]

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'")

 

[Guest]

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'")

 

[Guest]

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

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

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'")

 

[Guest]

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

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

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

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'")

 

[Guest]

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

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

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

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


:

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'")

 

[Guest]

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

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

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

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

:

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


:

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'")

 

[Guest]

Duane,

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

Shaun

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

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

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


:

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

:

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


:

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'")

 

[Guest]

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

Duane,

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

Shaun

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

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

:

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


:

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

:

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


:

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'")

 

[Guest]

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

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

Duane,

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

Shaun

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


:

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

:

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


:

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

:

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


:

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'")