Report Formatting - Type/Sub-Type

  • Thread starter Thread starter Tokyo Alex
  • Start date Start date
T

Tokyo Alex

Dear all,

I'm having trouble formatting a report the way I want it.

I have a table of transaction records which looks like this:
tblXactionRecs
xrRecordID (Autonumber, PK)
xrTimeStamp (Date/Time)
xrXactionType (Text)
xrXactionSubType (Text)
xrXactionVolume (Number - Integer)

This table is used to record the volume of transactions in an EDI system.
Each record represents one batch upload.
Transactions can be either Quotation Data or Delivery Notice Data (Type),
and Quotation Data transactions can have Automated or Manually Generated
subtypes. Volume is the number of individual Quotes or DNs uploaded in that
batch.

I want my report to look something like this:

Quotation Data
Datestamp Total Volume (Auto Manual)
2010/1/22 1100 (500 600)
2010/1/21 1080 (450 630)
...

Delivery Notice Data
Datestamp Total Volume
2010/1/22 110
2010/1/21 142
...

So the data will be grouped by Type first, then by DateStamp grouped on day,
with the sum of transaction volume being returned for each day.

I'm not married to the exact format above, but I do want it to represent the
data in a way at least similar to this.

I'm a total newbie when it comes to reports, so any help or advice would be
very much appreciated.

Thanks in advance,
Alex.
 
I expect you want to use a crosstab query that has xrXactionType and
srTimeStamp (GROUP BY) and xrXactionVolume (Sum) as the Row Headings. Set
the xrXactionSubType as the Column Heading and Sum xrXactionVolume as the
value. Set the column headings property to the two values of
xrXactionSubType.

Duane Hookom
MS Access MVP
 
Back
Top