Report Question for Duane

[Guest]

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.

 

[Guest]

Duane,
Upon looking at your notes, please take a look at my SQL statement.

tbl_Issues_Log is the table where the Issues are stored and the IssueID is a
dual primary key with the ChangeDate in the Reserve table -
tbl_ReserveQuarter.

Issues Table
tbl_Issues_log
IssueID
Country
IssueDescription
ReserveCategory
SubCategory
ReserveYear

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType (this is where you would choose the Field1, Field2, Field3)
Value

Here is the SQL Statement I am going to use? ( I don't know what I'm doing
with SQL). I don't know what goes where.

PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS
SalesDate
FROM (Categories INNER JOIN Products ON tbl.IssueID =
tbl_ReserveQuarter.IssueIDID) INNER JOIN (Orders INNER JOIN [Order Details]
ON
tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[ChangeDate])
PIVOT Categories.CategoryName In
("BeginBal","T-Cumul", "I-Cumul","T-TaxAcc","I-TaxAcc","T-IntAcc","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood","T-Acq",
"I-Acq","T-OthBalSht","I-OthBalSht","T-OthReclass","I-OthReclass","T-OthProv","I-OthProv","Pay/Ref","T-Reclass","I-Reclass");


For the Previous Quarter Balance:
=DateAdd(???

Thanks Duane?

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

Duane,
I apologize... I just noticed I pasted the wrong SQL statement.... This is
what I came up with. I keep getting the error message about problems with my
INNER JOIN. I'm doing something wrong, but I don't know what. I've posted the
table information again as well.

PARAMETERS [Forms]![frm_issuedates]![txtReportDate] DateTime;
TRANSFORM Sum([changetype].value) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS SalesDate
FROM (changedate INNER JOIN changetype ON issueid = IssueID) INNER JOIN
(Orders INNER JOIN [Value]
ON tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON
Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frm_issuedates]![txtReportDate],Null,[ChangeDate])
PIVOT ChangeType.changetype In
("Beginning Balance","Acquisition/Disposition - Tax",
"Acquisition/Disposition - Interest","Other Balance Sheet Reclass -
Tax","Other Balance Sheet Reclass - Interest","Tax Reversal","Tax Accrual",
"Interest Accrual","Interest Reversal","Other Provision Activity -
Tax","Other Provision Activity - Interest","Reclass Within State Reserves -
Tax","Reclass Within State Reserves - Interest","Cumulative Translation
Adjustment - Tax","Cumulative Translation Adjustment -
Interest","Payments/Refunds","Other Balance Sheet Activity - Tax","Other
Balance Sheet Activity - Interest");

tbl_Issues
IssueID
IssueDescription
Country
TaxYear
ReserveCategory
SubCategory

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

An Issue will never have more than one reserve.

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

Duane,
I apologize for the multiple postings... however, I created a crosstab query
using this table:

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

this produced the Query with the following fields (column headings)
IssueID
ChangeDate
ChangeType
Value
Total Of Value: Value
(and this shows the totals for each of the categories I need)

I have not had a chance yet to display them in the report... however, I
wanted to know the formula to show the previous quarter ending balance. I
don't know how to do this...

Here's the SQL for the Query too!
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate,
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;


The Total Of Value shows all the columns where changes can occur.

Thanks again... and I apologize... this is a work in progress and while I'm
waiting for help ( I appreciate it), I am trying things to figure it out
myself.

Thanks again!!!!!

Duane,
I apologize... I just noticed I pasted the wrong SQL statement.... This is
what I came up with. I keep getting the error message about problems with my
INNER JOIN. I'm doing something wrong, but I don't know what. I've posted the
table information again as well.

PARAMETERS [Forms]![frm_issuedates]![txtReportDate] DateTime;
TRANSFORM Sum([changetype].value) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS SalesDate
FROM (changedate INNER JOIN changetype ON issueid = IssueID) INNER JOIN
(Orders INNER JOIN [Value]
ON tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON
Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frm_issuedates]![txtReportDate],Null,[ChangeDate])
PIVOT ChangeType.changetype In
("Beginning Balance","Acquisition/Disposition - Tax",
"Acquisition/Disposition - Interest","Other Balance Sheet Reclass -
Tax","Other Balance Sheet Reclass - Interest","Tax Reversal","Tax Accrual",
"Interest Accrual","Interest Reversal","Other Provision Activity -
Tax","Other Provision Activity - Interest","Reclass Within State Reserves -
Tax","Reclass Within State Reserves - Interest","Cumulative Translation
Adjustment - Tax","Cumulative Translation Adjustment -
Interest","Payments/Refunds","Other Balance Sheet Activity - Tax","Other
Balance Sheet Activity - Interest");

tbl_Issues
IssueID
IssueDescription
Country
TaxYear
ReserveCategory
SubCategory

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

An Issue will never have more than one reserve.

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

You stated earlier "Shows the previous quarter ending balance (which is all
the values in the DB up to the prior quarter)" . Try this SQL which should
group all ChangeDate prior to 1/1/2007 together:

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate) as TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

--
Duane Hookom
Microsoft Access MVP

Duane,
I apologize for the multiple postings... however, I created a crosstab query
using this table:

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

this produced the Query with the following fields (column headings)
IssueID
ChangeDate
ChangeType
Value
Total Of Value: Value
(and this shows the totals for each of the categories I need)

I have not had a chance yet to display them in the report... however, I
wanted to know the formula to show the previous quarter ending balance. I
don't know how to do this...

Here's the SQL for the Query too!
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate,
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;


The Total Of Value shows all the columns where changes can occur.

Thanks again... and I apologize... this is a work in progress and while I'm
waiting for help ( I appreciate it), I am trying things to figure it out
myself.

Thanks again!!!!!

Duane,
I apologize... I just noticed I pasted the wrong SQL statement.... This is
what I came up with. I keep getting the error message about problems with my
INNER JOIN. I'm doing something wrong, but I don't know what. I've posted the
table information again as well.

PARAMETERS [Forms]![frm_issuedates]![txtReportDate] DateTime;
TRANSFORM Sum([changetype].value) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS SalesDate
FROM (changedate INNER JOIN changetype ON issueid = IssueID) INNER JOIN
(Orders INNER JOIN [Value]
ON tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON
Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frm_issuedates]![txtReportDate],Null,[ChangeDate])
PIVOT ChangeType.changetype In
("Beginning Balance","Acquisition/Disposition - Tax",
"Acquisition/Disposition - Interest","Other Balance Sheet Reclass -
Tax","Other Balance Sheet Reclass - Interest","Tax Reversal","Tax Accrual",
"Interest Accrual","Interest Reversal","Other Provision Activity -
Tax","Other Provision Activity - Interest","Reclass Within State Reserves -
Tax","Reclass Within State Reserves - Interest","Cumulative Translation
Adjustment - Tax","Cumulative Translation Adjustment -
Interest","Payments/Refunds","Other Balance Sheet Activity - Tax","Other
Balance Sheet Activity - Interest");

tbl_Issues
IssueID
IssueDescription
Country
TaxYear
ReserveCategory
SubCategory

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

An Issue will never have more than one reserve.

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP


:

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate) as
TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;


I tried this, but I get a Syntax Error in TRANSFORM statement

Try this:
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate) as
TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

You will need to set the query parameters:
Forms!frm_IssueDates.txtReportDate Date/Time
--
Duane Hookom
Microsoft Access MVP

Duane Where do I put this code in relation to the code I'm using now? And how
do I incorporate user input from the form frm_IssueDates.txtReportDate?

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
tbl_ReserveQuarter.ChangeDate, Sum(tbl_ReserveQuarter.Value) AS [Total Of
Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;

You stated earlier "Shows the previous quarter ending balance (which is all
the values in the DB up to the prior quarter)" . Try this SQL which should
group all ChangeDate prior to 1/1/2007 together:

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate) as TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

--
Duane Hookom
Microsoft Access MVP


:

Duane,
I apologize for the multiple postings... however, I created a crosstab query
using this table:

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

this produced the Query with the following fields (column headings)
IssueID
ChangeDate
ChangeType
Value
Total Of Value: Value
(and this shows the totals for each of the categories I need)

I have not had a chance yet to display them in the report... however, I
wanted to know the formula to show the previous quarter ending balance. I
don't know how to do this...

Here's the SQL for the Query too!
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate,
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;


The Total Of Value shows all the columns where changes can occur.

Thanks again... and I apologize... this is a work in progress and while I'm
waiting for help ( I appreciate it), I am trying things to figure it out
myself.

Thanks again!!!!!

:

Duane,
I apologize... I just noticed I pasted the wrong SQL statement.... This is
what I came up with. I keep getting the error message about problems with my
INNER JOIN. I'm doing something wrong, but I don't know what. I've posted the
table information again as well.

PARAMETERS [Forms]![frm_issuedates]![txtReportDate] DateTime;
TRANSFORM Sum([changetype].value) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS SalesDate
FROM (changedate INNER JOIN changetype ON issueid = IssueID) INNER JOIN
(Orders INNER JOIN [Value]
ON tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON
Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frm_issuedates]![txtReportDate],Null,[ChangeDate])
PIVOT ChangeType.changetype In
("Beginning Balance","Acquisition/Disposition - Tax",
"Acquisition/Disposition - Interest","Other Balance Sheet Reclass -
Tax","Other Balance Sheet Reclass - Interest","Tax Reversal","Tax Accrual",
"Interest Accrual","Interest Reversal","Other Provision Activity -
Tax","Other Provision Activity - Interest","Reclass Within State Reserves -
Tax","Reclass Within State Reserves - Interest","Cumulative Translation
Adjustment - Tax","Cumulative Translation Adjustment -
Interest","Payments/Refunds","Other Balance Sheet Activity - Tax","Other
Balance Sheet Activity - Interest");

tbl_Issues
IssueID
IssueDescription
Country
TaxYear
ReserveCategory
SubCategory

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

An Issue will never have more than one reserve.

:

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP


:

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.

 

[Guest]

You really need to be able to proof and correct my answers. You will also
need to add the data type of your query parameters as I suggested.
Every field/column in the SELECT statement must have a comma between them.

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate) as
TheChangeDate ,
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

--
Duane Hookom
Microsoft Access MVP

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate) as
TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;


I tried this, but I get a Syntax Error in TRANSFORM statement

Try this:
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate) as
TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID,
tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory,
tbl_Issues_Log.SubCategory,
IIf(ChangeDate<=Forms!frm_IssueDates.txtReportDate,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

You will need to set the query parameters:
Forms!frm_IssueDates.txtReportDate Date/Time
--
Duane Hookom
Microsoft Access MVP

Duane Where do I put this code in relation to the code I'm using now? And how
do I incorporate user input from the form frm_IssueDates.txtReportDate?

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
tbl_ReserveQuarter.ChangeDate, Sum(tbl_ReserveQuarter.Value) AS [Total Of
Value]
FROM tbl_ReserveQuarter INNER JOIN tbl_Issues_Log ON
tbl_ReserveQuarter.IssueID = tbl_Issues_Log.IssueID
GROUP BY tbl_ReserveQuarter.IssueID, tbl_Issues_Log.IssueDescription,
tbl_Issues_Log.PlanningCategory, tbl_Issues_Log.SubCategory,
tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;


:

You stated earlier "Shows the previous quarter ending balance (which is all
the values in the DB up to the prior quarter)" . Try this SQL which should
group all ChangeDate prior to 1/1/2007 together:

TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate) as TheChangeDate
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID,
IIf(ChangeDate<=#12/31/2006#,Null,ChangeDate)
PIVOT tbl_ReserveQuarter.ChangeType;

--
Duane Hookom
Microsoft Access MVP


:

Duane,
I apologize for the multiple postings... however, I created a crosstab query
using this table:

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

this produced the Query with the following fields (column headings)
IssueID
ChangeDate
ChangeType
Value
Total Of Value: Value
(and this shows the totals for each of the categories I need)

I have not had a chance yet to display them in the report... however, I
wanted to know the formula to show the previous quarter ending balance. I
don't know how to do this...

Here's the SQL for the Query too!
TRANSFORM Sum(tbl_ReserveQuarter.Value) AS SumOfValue
SELECT tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate,
Sum(tbl_ReserveQuarter.Value) AS [Total Of Value]
FROM tbl_ReserveQuarter
GROUP BY tbl_ReserveQuarter.IssueID, tbl_ReserveQuarter.ChangeDate
PIVOT tbl_ReserveQuarter.ChangeType;


The Total Of Value shows all the columns where changes can occur.

Thanks again... and I apologize... this is a work in progress and while I'm
waiting for help ( I appreciate it), I am trying things to figure it out
myself.

Thanks again!!!!!

:

Duane,
I apologize... I just noticed I pasted the wrong SQL statement.... This is
what I came up with. I keep getting the error message about problems with my
INNER JOIN. I'm doing something wrong, but I don't know what. I've posted the
table information again as well.

PARAMETERS [Forms]![frm_issuedates]![txtReportDate] DateTime;
TRANSFORM Sum([changetype].value) AS SumOfQuantity
SELECT
IIf([ChangeDate]<[Forms]![frm_Issuedates]![txtReportDate],Null,[ChangeDate])
AS SalesDate
FROM (changedate INNER JOIN changetype ON issueid = IssueID) INNER JOIN
(Orders INNER JOIN [Value]
ON tbl_Issues_log.IssueID = [tbl_ReserveQuarter].IssueID) ON
Products.ProductID
= [Order Details].ProductID
GROUP BY
IIf([ChangeDate]<[Forms]![frm_issuedates]![txtReportDate],Null,[ChangeDate])
PIVOT ChangeType.changetype In
("Beginning Balance","Acquisition/Disposition - Tax",
"Acquisition/Disposition - Interest","Other Balance Sheet Reclass -
Tax","Other Balance Sheet Reclass - Interest","Tax Reversal","Tax Accrual",
"Interest Accrual","Interest Reversal","Other Provision Activity -
Tax","Other Provision Activity - Interest","Reclass Within State Reserves -
Tax","Reclass Within State Reserves - Interest","Cumulative Translation
Adjustment - Tax","Cumulative Translation Adjustment -
Interest","Payments/Refunds","Other Balance Sheet Activity - Tax","Other
Balance Sheet Activity - Interest");

tbl_Issues
IssueID
IssueDescription
Country
TaxYear
ReserveCategory
SubCategory

tbl_ReserveQuarter
IssueID
ChangeDate
ChangeType
Value

An Issue will never have more than one reserve.

:

Isn't your second table the same as the first only it looks like the results
of a crosstab? If so, I would use a crosstab rather than a two tables.

Replies
1. Lets the user pick the quarter they want to see the detail for.
This is done with a form (frmDateSelect) and a control (txtQtrStart) to
select a date.

2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
This sounds like a totals query based on your crosstab where the change Date
is < the date on the form.

3. Show the quarter chosen in detail (using the ChangeType as column headers)
Combining a totals row with multiple details row can be done with a union
query or possibly a single crosstab. Consider this query in the Northwind
sample that would total quantities prior to a date and display daily totals
for each following the date. The "category" field is used as the derived
column headings:
PARAMETERS [Forms]![frmDateSelect]![txtQtrStart] DateTime;
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate]) AS
SalesDate
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY
IIf([OrderDate]<[Forms]![frmDateSelect]![txtQtrStart],Null,[OrderDate])
PIVOT Categories.CategoryName In
("Beverages","Condiments","Confections","Dairy
Products","Grains/Cereals","Meat/Poultry","Produce","Seafood");

4. Add the previous quarter to the current quarter to get a grand total.
This would be report totals in the report footer.

5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).

6. Show all records, even if they have a 0 balance.
The crosstab will show the results of all transactions with every column
heading represented.


--
Duane Hookom
Microsoft Access MVP


:

Duane,
I have a report that I'm trying to do (a different one), and I set up my
table two different ways....

I have table 1

IssueID
ChangeDate
ChangeType
Value

and Table 2 (same as table 1 with the fields actually listed instead of
choosing the change type)

IssueID
ChangeDate
BeginningBalance
TaxAcc
TaxInt
IntAcc
Payment/Refund
Cumulative
etc.... 17 fields in all.

In the first table, the user puts in the date, the type of change (combo
box), and then the amount.
In the second table, the user puts in the date, and puts thier value into
the correct field.

I am trying to create a report that does the following:

1. Lets the user pick the quarter they want to see the detail for.
2. Shows the previous quarter ending balance (which is all the values in the
DB up to the prior quarter)
3. Show the quarter chosen in detail (using the ChangeType as column headers)
4. Add the previous quarter to the current quarter to get a grand total.
5. Show totals after every Category (this I can do, I'm not a complete
buffoon, but I really feel like one since I've been working on this report
since December and I cannot figure it out).
6. Show all records, even if they have a 0 balance.

If you need to see the report format, I can post it.

I've actually gotten the report to work with user input on another form to
show the current quarter and get totals. I thought that maybe someone else
could help since there may be another way to skin this cat.

Klatuu was trying to help me, but he's really busy right now and I don't
think he has the time to help. I have to be finished with this database by
this coming Friday and I don't want to have to tell them that Access cannot
handle this type of report.

I've posted this a couple (a few, really) times and I don't seem to be
getting anywhere.

Please help.
Thanks.