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 » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT Statement in a Report



 
 
Thread Tools Display Modes
  #31  
Old December 7th, 2006, 04:15 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 2,251
Default SELECT Statement in a Report

My suggestion was to use text boxes rather than labels. Why write code if
you don't have to?

It looks like this report depends on both "frm_CustomReports" and
"frm_CustomSearch". Why don't you have all criteria on a single form?

Do you actually maintain a table that has YearX as field names?

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the
labels at the top of the page work with:
lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0
lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1
lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2
lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3
lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4
The problem is getting the text boxes in the detail section to work.

Here's the code from the report's Record Source:

PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" &
DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS
Expr1,
[Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5,
[Year5] AS Expr6 FROM tbl_ReserveAmounts;

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any
other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5
years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break
that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT
statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able
to
take
an extended period to examine your problem. At this point, I just
don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me.
You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem.
In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all
the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:









  #32  
Old December 7th, 2006, 05:19 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

I have only one form... with only one text box, where the user puts in the
year to start from... eventually there will be multiple combo boxes as well
as a date range, and they will be able to pull custom reports. I just want to
get this one "thing" done, so at least I have a starting point.

I have the following tables:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
Country
BenefitType
(there's more fields, I just wanted to list a few)

tbl_Quarter
IdeaID (dual primary key)
Qtrenddate (dual)
QuarterValue

I created a Select Query using these two tables. It shows everything: qry_Test

My goal is to open up frm_CustomReports and prompt the user to select a year
from a combo box (maybe a text box would be easier so then I don't have to
list the next 25 years in tbl_Year), then the form would open up and put that
information in the first column: textbox txtYear1, and then do Year2
(Year1+1), Year3 (Year1+2), and so on.

The form sorts by Country and then Structure, with a subtotal after each
structure and then a total for the country. The other part to this is another
subtotal by country but broken down into three categories (BenefitType)... if
the total of the year is 100, 25 would be in ETR, 50 in Cash, and 25 in ETR &
Cash.

I hope this makes more sense.

"Duane Hookom" wrote:

My suggestion was to use text boxes rather than labels. Why write code if
you don't have to?

It looks like this report depends on both "frm_CustomReports" and
"frm_CustomSearch". Why don't you have all criteria on a single form?

Do you actually maintain a table that has YearX as field names?

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the
labels at the top of the page work with:
lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0
lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1
lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2
lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3
lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4
The problem is getting the text boxes in the detail section to work.

Here's the code from the report's Record Source:

PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" &
DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS
Expr1,
[Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5,
[Year5] AS Expr6 FROM tbl_ReserveAmounts;

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any
other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5
years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break
that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT
statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able
to
take
an extended period to examine your problem. At this point, I just
don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me.
You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem.
In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all
the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:










  #33  
Old December 7th, 2006, 07:24 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Did I thoroughly baffle you? I'm sure I'm not explaining myself well. I wish
I could show you a screen shot of the report... then you would know what I
mean.

"chickalina" wrote:

I have only one form... with only one text box, where the user puts in the
year to start from... eventually there will be multiple combo boxes as well
as a date range, and they will be able to pull custom reports. I just want to
get this one "thing" done, so at least I have a starting point.

I have the following tables:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
Country
BenefitType
(there's more fields, I just wanted to list a few)

tbl_Quarter
IdeaID (dual primary key)
Qtrenddate (dual)
QuarterValue

I created a Select Query using these two tables. It shows everything: qry_Test

My goal is to open up frm_CustomReports and prompt the user to select a year
from a combo box (maybe a text box would be easier so then I don't have to
list the next 25 years in tbl_Year), then the form would open up and put that
information in the first column: textbox txtYear1, and then do Year2
(Year1+1), Year3 (Year1+2), and so on.

The form sorts by Country and then Structure, with a subtotal after each
structure and then a total for the country. The other part to this is another
subtotal by country but broken down into three categories (BenefitType)... if
the total of the year is 100, 25 would be in ETR, 50 in Cash, and 25 in ETR &
Cash.

I hope this makes more sense.

"Duane Hookom" wrote:

My suggestion was to use text boxes rather than labels. Why write code if
you don't have to?

It looks like this report depends on both "frm_CustomReports" and
"frm_CustomSearch". Why don't you have all criteria on a single form?

Do you actually maintain a table that has YearX as field names?

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the
labels at the top of the page work with:
lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0
lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1
lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2
lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3
lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4
The problem is getting the text boxes in the detail section to work.

Here's the code from the report's Record Source:

PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" &
DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS
Expr1,
[Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5,
[Year5] AS Expr6 FROM tbl_ReserveAmounts;

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any
other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5
years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break
that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT
statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able
to
take
an extended period to examine your problem. At this point, I just
don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me.
You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem.
In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all
the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:










  #34  
Old December 7th, 2006, 07:35 PM posted to microsoft.public.access.reports
chickalina
external usenet poster
 
Posts: 194
Default SELECT Statement in a Report

Duane,
OK... I got the query to work with a Between[Beginning Date] and [Ending
Date]... it gives me the totals by quarter.. (since the information is stored
as such, so that's how the query results look)

ideaid qtreddate quartervalue
1 3/31/08 2
1 6/30/08 2
1 9/30/08 2
1 12/31/08 2
2 3/31/08 5
2 6/30/08 5
2 9/30/08 5
2 12/31/08 5

You get the picture... Now, how do I incorporate this into the following
report

Australia

Structural
Year1 Year2 Year3 Year4 Year5 Total
Idea 1 5 5 5 5 5 25
Idea 2 2 2 2 2 2 10
Str total 7 7 7 7 7 35

Non-Structural
Year1 Year2 Year3 Year4 Year5 Total
Idea 1 4 4 4 4 4 20
Idea 2 1 1 1 1 1 5
Non-Strt. tot 5 5 5 5 5 25

ETR 5 5 5 5 5 25
Cash 4 4 4 4 4 20
ETR & Cash 3 3 3 3 3 15

Australia Total 12 12 12 12 12 60


Australia
Structural

"chickalina" wrote:

I have only one form... with only one text box, where the user puts in the
year to start from... eventually there will be multiple combo boxes as well
as a date range, and they will be able to pull custom reports. I just want to
get this one "thing" done, so at least I have a starting point.

I have the following tables:

tbl_Ideas_Bank
IdeaID (primary key)
Description
Structural
Jurisdiction
Country
BenefitType
(there's more fields, I just wanted to list a few)

tbl_Quarter
IdeaID (dual primary key)
Qtrenddate (dual)
QuarterValue

I created a Select Query using these two tables. It shows everything: qry_Test

My goal is to open up frm_CustomReports and prompt the user to select a year
from a combo box (maybe a text box would be easier so then I don't have to
list the next 25 years in tbl_Year), then the form would open up and put that
information in the first column: textbox txtYear1, and then do Year2
(Year1+1), Year3 (Year1+2), and so on.

The form sorts by Country and then Structure, with a subtotal after each
structure and then a total for the country. The other part to this is another
subtotal by country but broken down into three categories (BenefitType)... if
the total of the year is 100, 25 would be in ETR, 50 in Cash, and 25 in ETR &
Cash.

I hope this makes more sense.

"Duane Hookom" wrote:

My suggestion was to use text boxes rather than labels. Why write code if
you don't have to?

It looks like this report depends on both "frm_CustomReports" and
"frm_CustomSearch". Why don't you have all criteria on a single form?

Do you actually maintain a table that has YearX as field names?

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the
labels at the top of the page work with:
lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0
lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1
lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2
lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3
lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4
The problem is getting the text boxes in the detail section to work.

Here's the code from the report's Record Source:

PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" &
DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS
Expr1,
[Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5,
[Year5] AS Expr6 FROM tbl_ReserveAmounts;

"Duane Hookom" wrote:

Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any
other
query.

--
Duane Hookom
MS Access MVP

"chickalina" wrote in message
...
Hi John,
I have a report that "should" show the yearly sum for an idea for 5
years.
I
want the user to be able to choose the start year. At the bottom of the
report (whereever you say the text boxes should go), I need totals for
each
of three different fields (all ideas fall into one of the three). I
created
a crosstab query that gives me yearly sums, but then you can't break
that
down into the three sections, and I can't figure out a way for a user
prompt
for the start year.

Should I be using a Select Query for the source and then SELECT
statements
in the boxes to pull the information?

Thanks.
M


"John Spencer" wrote:

Sorry, I haven't responded but I've been busy and have not been able
to
take
an extended period to examine your problem. At this point, I just
don't
understand what you are attempting to do.

One thing that bothers me is you keep saying you are using a query (a
select
statement) in the footer of the report. That makes no sense to me.
You
cannot use a query in a footer and you can't use it as the source of a
control.

I will try to find some time this afternoon to look at your problem.
In
the
meantime, please copy and paste the SQL statement you are using as the
report's record source. And try to explain what you are attempting to
accomplish in the report's footer (not the page footer).

To make sense of this, I am probably going to have to print out all
the
postings in this thread and then ask additional questions.


"chickalina" wrote in message
...
Is there anything anyone can do? I'm in desperate need of help.

"chickalina" wrote:










 




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 11:20 AM.


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