Trying again - Header summing problem

[Dan Johnson]

This is a second post to the same problem. I am hoping someone can offer suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table; the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks within
the entirety of the table. I then wrote the report with a Container header
and a pallet header then listed the blocks in the detail. Within the header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I know
how many pallets were bad in each container. The quantity returns the total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the bottom
line problem that I am having. I am not literate, at all, in VB and am using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Duane Hookom]

You can create a query similar to your report's record source however group by Container and Pallet. Name this query qgrpContainerPallet. Then create another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's record source. Join the ContainerNumber fields and add NumOfPallets to the report's fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table; the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks within
the entirety of the table. I then wrote the report with a Container header
and a pallet header then listed the blocks in the detail. Within the header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I know
how many pallets were bad in each container. The quantity returns the total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the bottom
line problem that I am having. I am not literate, at all, in VB and am using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Dan Johnson]

Hi Duane,

Thanks for the response. I have the query done and it does an accurate count for the number of pallets per container. However, when I try to include it in the query and run the report, Access bombs completely out and closes. The only way I can get into the query is to delete the count query. Any further suggestions?

--
Dan Johnson
You can create a query similar to your report's record source however group by Container and Pallet. Name this query qgrpContainerPallet. Then create another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's record source. Join the ContainerNumber fields and add NumOfPallets to the report's fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table; the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks within
the entirety of the table. I then wrote the report with a Container header
and a pallet header then listed the blocks in the detail. Within the header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I know
how many pallets were bad in each container. The quantity returns the total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the bottom
line problem that I am having. I am not literate, at all, in VB and am using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Duane Hookom]

Did you attempt to join the fields in the query?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the response. I have the query done and it does an accurate count
for the number of pallets per container. However, when I try to include it
in the query and run the report, Access bombs completely out and closes. The
only way I can get into the query is to delete the count query. Any further
suggestions?

--
Dan Johnson
You can create a query similar to your report's record source however group
by Container and Pallet. Name this query qgrpContainerPallet. Then create
another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's record
source. Join the ContainerNumber fields and add NumOfPallets to the report's
fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer
suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table; the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks within
the entirety of the table. I then wrote the report with a Container header
and a pallet header then listed the blocks in the detail. Within the header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I know
how many pallets were bad in each container. The quantity returns the total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the bottom
line problem that I am having. I am not literate, at all, in VB and am using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Dan Johnson]

Yes, with all variations of direction. Same result


--
Dan Johnson

Did you attempt to join the fields in the query?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the response. I have the query done and it does an accurate
count for the number of pallets per container. However, when I try to
include it in the query and run the report, Access bombs completely out
and closes. The only way I can get into the query is to delete the count
query. Any further suggestions?

--
Dan Johnson
You can create a query similar to your report's record source however
group by Container and Pallet. Name this query qgrpContainerPallet. Then
create another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's record
source. Join the ContainerNumber fields and add NumOfPallets to the
report's fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer
suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table; the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks
within
the entirety of the table. I then wrote the report with a Container header
and a pallet header then listed the blocks in the detail. Within the
header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I know
how many pallets were bad in each container. The quantity returns the
total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the
bottom
line problem that I am having. I am not literate, at all, in VB and am
using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Duane Hookom]

It sounds like corruption. You might want to check Tony Toews corruption
page http://www.granite.ab.ca/access/corruptmdbs.htm.

--
Duane Hookom
MS Access MVP

Yes, with all variations of direction. Same result


--
Dan Johnson

Did you attempt to join the fields in the query?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the response. I have the query done and it does an accurate
count for the number of pallets per container. However, when I try to
include it in the query and run the report, Access bombs completely out
and closes. The only way I can get into the query is to delete the count
query. Any further suggestions?

--
Dan Johnson
You can create a query similar to your report's record source however
group by Container and Pallet. Name this query qgrpContainerPallet. Then
create another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's record
source. Join the ContainerNumber fields and add NumOfPallets to the
report's fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer
suggestions to the problem below:

I am trying, without any success, to count a number of units on an Access
report within one of two headers. The table structure is very simple with
only one table in the database. There are six fields within the table;
the
first one is a counter (AutoNumber), the second is a text field. The next
three fields are all Number fields and the last is a Memo field. There
are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number field)
+ Pallets [Pallet #] (within the container - usually 11 per
container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per
pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks
within
the entirety of the table. I then wrote the report with a Container
header
and a pallet header then listed the blocks in the detail. Within the
header
of the pallets, I get a Count of the blocks that works properly. I am now
trying to get a count of the pallets within the Container header so I
know
how many pallets were bad in each container. The quantity returns the
total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding pallet
even though several blocks may be on the same pallet. Is there a way (via
function or whatever) that can filter out the duplicates which is the
bottom
line problem that I am having. I am not literate, at all, in VB and am
using
the Report Wizard initially to create the basis for the report. I do know
how to use most of the controls with the Report module, just not with VB.
Thanks, in advance, for the help.

Dan Johnson

 

[Dan Johnson]

Duane,

After much ado, I got it to work with your suggestions. Had to do some
finagling with the queries but it did work. Thanks for the advice.

--
Dan Johnson

It sounds like corruption. You might want to check Tony Toews corruption
page http://www.granite.ab.ca/access/corruptmdbs.htm.

--
Duane Hookom
MS Access MVP

Yes, with all variations of direction. Same result


--
Dan Johnson

Did you attempt to join the fields in the query?

--
Duane Hookom
MS Access MVP
--

Hi Duane,

Thanks for the response. I have the query done and it does an accurate
count for the number of pallets per container. However, when I try to
include it in the query and run the report, Access bombs completely out
and closes. The only way I can get into the query is to delete the count
query. Any further suggestions?

--
Dan Johnson
You can create a query similar to your report's record source however
group by Container and Pallet. Name this query qgrpContainerPallet. Then
create another query
SELECT ContainerNumber, Count(PalletNum) as NumOfPallets
FROM qgrpContainerPallet
GROUP BY ContainerNumber;

Save this second totals query and then include it in your report's
record source. Join the ContainerNumber fields and add NumOfPallets to
the report's fields.

--
Duane Hookom
MS Access MVP


This is a second post to the same problem. I am hoping someone can offer
suggestions to the problem below:

I am trying, without any success, to count a number of units on an
Access
report within one of two headers. The table structure is very simple
with
only one table in the database. There are six fields within the table;
the
first one is a counter (AutoNumber), the second is a text field. The
next
three fields are all Number fields and the last is a Memo field. There
are
only 689 records in the table.

The information is:

Overseas container numbers [Container #] (total of 18) -- (Number
field)
+ Pallets [Pallet #] (within the container - usually 11 per
container) -
(Number field)
+ Blocks [Block #] (on the pallet - between 3 and 4 per
pallet) -
(Number field)
+ Memo [Comments] (comments about the block) - (Memo
field)

I have a query that filters the memo field to only the damaged blocks
within
the entirety of the table. I then wrote the report with a Container
header
and a pallet header then listed the blocks in the detail. Within the
header
of the pallets, I get a Count of the blocks that works properly. I am
now
trying to get a count of the pallets within the Container header so I
know
how many pallets were bad in each container. The quantity returns the
total
number of pallets that is equivalent to the total number of blocks, not
pallets. Long story short, for each block there is a corresponding
pallet
even though several blocks may be on the same pallet. Is there a way
(via
function or whatever) that can filter out the duplicates which is the
bottom
line problem that I am having. I am not literate, at all, in VB and am
using
the Report Wizard initially to create the basis for the report. I do
know
how to use most of the controls with the Report module, just not with
VB.
Thanks, in advance, for the help.

Dan Johnson