Creating One Page Report from Two Data Sets

[rjbinney]

It has been years since I've used Access, so please forgive the beginner question.

I have two reports in Excel.

One lists:

• Customer Number
• Sales Manager
• Order Number

Sales Managers are assigned to customers, so there will never be more than one Sales Manager for any Customer Number.
Each customer (therefore each Customer Number) has multiple orders. Each row is an order.
Example:

CUSTOMER	SALES MANAGER	ORDER
Cus-0001	Ringo	SO-564
Cus-0002	Paul	SO-828
Cus-0002	Paul	SO-912
Cus-0003	George	SO-231
Cus-0004	Ringo	SO-310

<tbody>
</tbody>

The other lists

• Customer Number
• Authorized-to-Order

Each customer can have multiple Authorities:

CUSTOMER	AUTHORIZED-to-ORDER
Cus-001	Pete T
Cus-001	Roger D
Cus-002	Alan L
Cus-002	Derek L
Cus-002	Gordon C
Cus-003	Bruce S
Cus-004	Jack B
Cus-004	Eric C
Cus-004	Ginger B

<tbody>
</tbody>

I'd love a "Customer on a Page" report, listing Customer Number, Sales Manager, a list of all sales orders, and a list of all authorized-signatures. For example:

Cus-001 (Ringo)
SO 564
Pete T
Roger D

--- new page ---
Cus-002 (Paul)
SO 828
SO 912
Alan L
Derek L
Gordon C

--- new page ---
Cus-003 (George)
SO-231
Bruce S

--- new page ---
Cus-004 (Ringo)
SO-310
Jack B
Eric C
Ginger B



I remember to import the tables, and create a query to connect the "Customer Number" on one to the other.

Then I forget everything I ever learned about Access.

BUt I can do this, yes?

Thanks

 

[rjbinney]

That's right.

What I DON'T want is each signature listed for each SALES order:

Cus-002 (Paul)
SO 828
Alan L
Derek L
Gordon C
SO 912
Alan L
Derek L
Gordon C

Once all the "real" data gets populated, that will start filling pages and pages as there are ten or more orders, with ten or more authorized people - it grows the line count geometrically!

I hope that makes more sense?

So it's

Customer

Sales Orders, 1-n
​

• 
  [*=1]Authorized Signatures a-z

​

NOT

Customer

Sales Order 1
​

• 
  [*=1]Authorized Signatures a-z

Sales Order 2
​

• 
  [*=1]Authorized Signatures a-z

Sales Order n
​

• 
  [*=1]Authorized Signatures a-z

 

[xenou]

So I might have been right with the subreports after all. You can create a customers report. then drag a query with Customers-Orders into the report, so that you create a subreport for the orders that go with the customer. And then you drag a query with Customer-AuthorizedSignatures doing the same thing. I dragged the queries into the header area rather than the detail area to avoid the duplicate listings. Then you spend about 5 hours trying to mess with backfill, borders, alignment, and so on, because, well that's what it's like creating reports unless you don't mind ugly.