Duplicate values in crosstab report

  • Thread starter Thread starter Guest
  • Start date Start date
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?
 
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.
 
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...
 
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...
 
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...
 
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...
 
Back
Top