Duplicate values in crosstab report

G

Guest

I have a budget report based on a crosstab query. My column headings are
funds and my rows are budget amounts of accounts. I prints

No. 100 200 300 400
12900 $100 $100
$100 $100

and I would like it to print

No. 100 200 300 400
12900 $100 $100 $100 $100

I have a table for No., a table for funds, and a budgetdetail.
Relationships are No. table (1-M) to budgetdetail and funds table (1-M) to
budgetdetail.
The only thing I can figure is in the fashion I entered the data. For the
200 and 400 funds I imported data from an excel sheet and the 100 and 400 I
entered by data entry. Why would this happen and do you have any suggestions
on how to correct it?
 
G

Guest

TRANSFORM First(BudgetAmtByCategory.BudgetAmt) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription, BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, First(BudgetAmtByCategory.BudgetAmt) AS
[Total Of BudgetAmt]
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.[Budget?],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];

This is the sql view of the report's query; however, I think the problem
arises from the Transfer Spreadsheet cmd I used when I automated the import
from data. Maybe the imported information was variant and it was stored
differently than the data I manually entered in afterwards.
I did find a quick fix of deleting those records which showed on a separate
line and rentered manually. It worked.
Do you think I need to format the excel data to correspond with the access
table? and do you think that will solve this issue? I don't want to have to
manually enter information that's already available in excel.

Thanks.
 
D

Duane Hookom

So, your datasheet looks ok but not when it gets transfered to Excel? I also
removed some fields from your select and group by. I'm not sure why you used
"First" unless there is only on value.

TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription, BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];


--
Duane Hookom
MS Access MVP
--

Jan said:
TRANSFORM First(BudgetAmtByCategory.BudgetAmt) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, First(BudgetAmtByCategory.BudgetAmt) AS
[Total Of BudgetAmt]
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.[Budget?],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];

This is the sql view of the report's query; however, I think the problem
arises from the Transfer Spreadsheet cmd I used when I automated the
import
from data. Maybe the imported information was variant and it was stored
differently than the data I manually entered in afterwards.
I did find a quick fix of deleting those records which showed on a
separate
line and rentered manually. It worked.
Do you think I need to format the excel data to correspond with the access
table? and do you think that will solve this issue? I don't want to have
to
manually enter information that's already available in excel.

Thanks.
--
Jan


Duane Hookom said:
Share your sql view.
Click to expand...
Click to expand...
 
G

Guest

I used first thinking it would eliminate the second value.
Your changes worked, I just need to change the fields to reflect $ 0 amount
if there is no amount for that line. Thanks.
--
Jan


Duane Hookom said:
So, your datasheet looks ok but not when it gets transfered to Excel? I also
removed some fields from your select and group by. I'm not sure why you used
"First" unless there is only on value.

TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription, BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];


--
Duane Hookom
MS Access MVP
--

Jan said:
TRANSFORM First(BudgetAmtByCategory.BudgetAmt) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, First(BudgetAmtByCategory.BudgetAmt) AS
[Total Of BudgetAmt]
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.[Budget?],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];

This is the sql view of the report's query; however, I think the problem
arises from the Transfer Spreadsheet cmd I used when I automated the
import
from data. Maybe the imported information was variant and it was stored
differently than the data I manually entered in afterwards.
I did find a quick fix of deleting those records which showed on a
separate
line and rentered manually. It worked.
Do you think I need to format the excel data to correspond with the access
table? and do you think that will solve this issue? I don't want to have
to
manually enter information that's already available in excel.

Thanks.
--
Jan


Duane Hookom said:
Share your sql view.

--
Duane Hookom
MS Access MVP
--

I have a budget report based on a crosstab query. My column headings
are
funds and my rows are budget amounts of accounts. I prints

No. 100 200 300 400
12900 $100 $100
$100 $100

and I would like it to print

No. 100 200 300 400
12900 $100 $100 $100 $100

I have a table for No., a table for funds, and a budgetdetail.
Relationships are No. table (1-M) to budgetdetail and funds table (1-M)
to
budgetdetail.
The only thing I can figure is in the fashion I entered the data. For
the
200 and 400 funds I imported data from an excel sheet and the 100 and
400
I
entered by data entry. Why would this happen and do you have any
suggestions
on how to correct it?
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Didn't the Val(Nz(....,0)) change nulls to 0.

--
Duane Hookom
MS Access MVP


Jan said:
I used first thinking it would eliminate the second value.
Your changes worked, I just need to change the fields to reflect $ 0
amount
if there is no amount for that line. Thanks.
--
Jan


Duane Hookom said:
So, your datasheet looks ok but not when it gets transfered to Excel? I
also
removed some fields from your select and group by. I'm not sure why you
used
"First" unless there is only on value.

TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];


--
Duane Hookom
MS Access MVP
--

Jan said:
TRANSFORM First(BudgetAmtByCategory.BudgetAmt) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, First(BudgetAmtByCategory.BudgetAmt) AS
[Total Of BudgetAmt]
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.[Budget?],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];

This is the sql view of the report's query; however, I think the
problem
arises from the Transfer Spreadsheet cmd I used when I automated the
import
from data. Maybe the imported information was variant and it was
stored
differently than the data I manually entered in afterwards.
I did find a quick fix of deleting those records which showed on a
separate
line and rentered manually. It worked.
Do you think I need to format the excel data to correspond with the
access
table? and do you think that will solve this issue? I don't want to
have
to
manually enter information that's already available in excel.

Thanks.
--
Jan


:

Share your sql view.

--
Duane Hookom
MS Access MVP
--

I have a budget report based on a crosstab query. My column headings
are
funds and my rows are budget amounts of accounts. I prints

No. 100 200 300 400
12900 $100 $100
$100 $100

and I would like it to print

No. 100 200 300 400
12900 $100 $100 $100 $100

I have a table for No., a table for funds, and a budgetdetail.
Relationships are No. table (1-M) to budgetdetail and funds table
(1-M)
to
budgetdetail.
The only thing I can figure is in the fashion I entered the data.
For
the
200 and 400 funds I imported data from an excel sheet and the 100
and
400
I
entered by data entry. Why would this happen and do you have any
suggestions
on how to correct it?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Well, it actually works but I just needed to tweak the format of the report.
Instead of using borders on the fields, I used lines to give it that
spreadsheet feel.
Thanks for the help.
--
Jan


Duane Hookom said:
Didn't the Val(Nz(....,0)) change nulls to 0.

--
Duane Hookom
MS Access MVP


Jan said:
I used first thinking it would eliminate the second value.
Your changes worked, I just need to change the fields to reflect $ 0
amount
if there is no amount for that line. Thanks.
--
Jan


Duane Hookom said:
So, your datasheet looks ok but not when it gets transfered to Excel? I
also
removed some fields from your select and group by. I'm not sure why you
used
"First" unless there is only on value.

TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];


--
Duane Hookom
MS Access MVP
--

TRANSFORM First(BudgetAmtByCategory.BudgetAmt) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, First(BudgetAmtByCategory.BudgetAmt) AS
[Total Of BudgetAmt]
FROM BudgetAmtByCategory
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.[Budget?],
BudgetAmtByCategory.Importance
PIVOT BudgetAmtByCategory.[Fund#];

This is the sql view of the report's query; however, I think the
problem
arises from the Transfer Spreadsheet cmd I used when I automated the
import
from data. Maybe the imported information was variant and it was
stored
differently than the data I manually entered in afterwards.
I did find a quick fix of deleting those records which showed on a
separate
line and rentered manually. It worked.
Do you think I need to format the excel data to correspond with the
access
table? and do you think that will solve this issue? I don't want to
have
to
manually enter information that's already available in excel.

Thanks.
--
Jan


:

Share your sql view.

--
Duane Hookom
MS Access MVP
--

I have a budget report based on a crosstab query. My column headings
are
funds and my rows are budget amounts of accounts. I prints

No. 100 200 300 400
12900 $100 $100
$100 $100

and I would like it to print

No. 100 200 300 400
12900 $100 $100 $100 $100

I have a table for No., a table for funds, and a budgetdetail.
Relationships are No. table (1-M) to budgetdetail and funds table
(1-M)
to
budgetdetail.
The only thing I can figure is in the fashion I entered the data.
For
the
200 and 400 funds I imported data from an excel sheet and the 100
and
400
I
entered by data entry. Why would this happen and do you have any
suggestions
on how to correct it?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report based on Crosstab Query 3
formatting report using crosstab query 1
Excel Complex Summing 3
Crosstab Report 8
Access 2007-Report by Month; Year to Date Total 2
Criteria in a report and calculating the running sum 0
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Running Totals 2

Top