Question about report

T

tom

I have data as below:

Discription Amount
------------ ---------
20ABC123 100
20ABC234 150
40ABC123 100
40ABC234 200
20BCD123 250
20BCD234 100
40ABC123 150
40BCD123 100

Report Result
Discription Vol Amount
------------ ---- ----------
ABC 5 700
BCD 3 450

Tks U very much for help!
Tom
 
L

Larry Daugherty

In the query on which your report is based, create a new field as
follows---

LetterKey: =Mid(DescriptionField, 3,3)

Use that field to get your totals.
 
T

tom

Darry Larry,

First of all, TKs U for yr information.

May be my sample data is not so good, actually at the discription field will
be difference as below :

Discription
------------
20ABCD XYZ1234
20ABCD XXX4567
40EFGHI XXX4567
40EFGHI XXX4567
40EFG XX1234

So base on above using function "Mid" is not good in my case.

Any others way to do this?

Rgds
Tom
 
L

Larry Linson

If you would state the rule, rather than giving examples that we have to
puzzle out, it would make it easier for us to help you. It is really
difficult to figure the grouping on the most recent set of descriptions,
because you did not even give an example of that.

Specifically, just what portion of the description field do you wish to use
for grouping? Otherwise you'll need to find an "Access psychics" newsgroup,
and I am not aware that one exists.

Larry Linson
Microsoft Access MVP
 
T

tom

Dear Larry,

I would like to give the actual figure as below:

ETD DESCRIPTION AMOUNT
7/1/2007 40' Dry Empty Container Inbound $1,138.00
7/1/2007 20' Full Container - Inbound $191.00
7/1/2007 40' Full Container - Inbound $861.00
7/1/2007 45' Full Container - Inbound $1,790.00
7/1/2007 20' Full Container - Outbound $1,528.00
7/1/2007 40' Full Container - Outbound $7,462.00
7/1/2007 45' Full Container - Outbound $3,580.00
7/1/2007 20' Transhipment Container - Inbound $860.00
7/1/2007 40' Transhipment Container - Inbound $14,040.00
7/1/2007 45' Transhipment Container - Inbound $6,156.00
7/1/2007 20' Transhipment Container - Outbound $2,924.00
7/1/2007 40' Transhipment Container - Outbound $7,540.00
7/1/2007 20' Transhipment Container Inbound $215.00
7/1/2007 40' Transhipment Container Inbound $323.50
7/1/2007 40' Transhipment Container Inbound $323.50

I want Dry Empty how many
I want Full container how many
I want Transhipment how many

Tks U very much for yr help !

Rgds
Tom
/////////////////////////////////
 
L

Larry Linson

If I understand correctly, you want to extract from the description three
particular text strings for grouping the data. That makes what you want to
do cumbersome, but not impossible. It would be much better structure to
have the "Type of Container" as a separate field, particularly if the
description is typed by hand... if so, it will not be long before you get
"Drt Emptu..." or "Fill..." or "Tanshipment".

If you are, for some reason, _compelled_* to take the chance on data entry,
you can use a Query with Calculated Fields for each Type, eg,

DryEmpty: If (InStr([DESCRIPTION], "Dry Empty")<>0,1,0)
and
Full: IIf (InStr([DESCRIPTION], "Full")<>0,1,0)
and
Transshipment: IIf (InStr([DESCRIPTION], "Transshipment")<>0,1,0)

And use the Sum function for each in the appropriate Footer (Group Footer or
Report Footer).

* do not let yourself be lulled by the thought that your data entry
will be careful and not make mistakes

Personally, I'd have a Combo Box on the Input Form to select "Type of Load"
(and another for "Inbound or Outbound" if you are going to use that value)
so that the user can choose but cannot mistype. I'd still have a calculated
Field in the Query, but the calculation would be different.

Also, I believe "Description" is an Access reserved word, and suggest you
change that field name to something like LoadDescription to avoid possible
confusion or conflict.

Larry Linson
Microsoft Access MVP
 
T

tom

Dear Larry Linson,

Thank you very much for yr help, I will try it.

Tom
////////////////////////////////

Larry Linson said:
If I understand correctly, you want to extract from the description three
particular text strings for grouping the data. That makes what you want to
do cumbersome, but not impossible. It would be much better structure to
have the "Type of Container" as a separate field, particularly if the
description is typed by hand... if so, it will not be long before you get
"Drt Emptu..." or "Fill..." or "Tanshipment".

If you are, for some reason, _compelled_* to take the chance on data entry,
you can use a Query with Calculated Fields for each Type, eg,

DryEmpty: If (InStr([DESCRIPTION], "Dry Empty")<>0,1,0)
and
Full: IIf (InStr([DESCRIPTION], "Full")<>0,1,0)
and
Transshipment: IIf (InStr([DESCRIPTION], "Transshipment")<>0,1,0)

And use the Sum function for each in the appropriate Footer (Group Footer or
Report Footer).

* do not let yourself be lulled by the thought that your data entry
will be careful and not make mistakes

Personally, I'd have a Combo Box on the Input Form to select "Type of Load"
(and another for "Inbound or Outbound" if you are going to use that value)
so that the user can choose but cannot mistype. I'd still have a calculated
Field in the Query, but the calculation would be different.

Also, I believe "Description" is an Access reserved word, and suggest you
change that field name to something like LoadDescription to avoid possible
confusion or conflict.

Larry Linson
Microsoft Access MVP

tom said:
Dear Larry,

I would like to give the actual figure as below:

ETD DESCRIPTION AMOUNT
7/1/2007 40' Dry Empty Container Inbound $1,138.00
7/1/2007 20' Full Container - Inbound $191.00
7/1/2007 40' Full Container - Inbound $861.00
7/1/2007 45' Full Container - Inbound $1,790.00
7/1/2007 20' Full Container - Outbound $1,528.00
7/1/2007 40' Full Container - Outbound $7,462.00
7/1/2007 45' Full Container - Outbound $3,580.00
7/1/2007 20' Transhipment Container - Inbound $860.00
7/1/2007 40' Transhipment Container - Inbound $14,040.00
7/1/2007 45' Transhipment Container - Inbound $6,156.00
7/1/2007 20' Transhipment Container - Outbound $2,924.00
7/1/2007 40' Transhipment Container - Outbound $7,540.00
7/1/2007 20' Transhipment Container Inbound $215.00
7/1/2007 40' Transhipment Container Inbound $323.50
7/1/2007 40' Transhipment Container Inbound $323.50

I want Dry Empty how many
I want Full container how many
I want Transhipment how many

Tks U very much for yr help !

Rgds
Tom
/////////////////////////////////
Click to expand...
Click to expand...
 
Top