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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CUSTOMER
[/TD]
[TD]SALES MANAGER
[/TD]
[TD]ORDER
[/TD]
[/TR]
[TR]
[TD]Cus-0001
[/TD]
[TD]Ringo
[/TD]
[TD]SO-564
[/TD]
[/TR]
[TR]
[TD]Cus-0002[/TD]
[TD]Paul[/TD]
[TD]SO-828
[/TD]
[/TR]
[TR]
[TD]Cus-0002
[/TD]
[TD]Paul
[/TD]
[TD]SO-912
[/TD]
[/TR]
[TR]
[TD]Cus-0003
[/TD]
[TD]George
[/TD]
[TD]SO-231
[/TD]
[/TR]
[TR]
[TD]Cus-0004
[/TD]
[TD]Ringo
[/TD]
[TD]SO-310
[/TD]
[/TR]
</tbody>[/TABLE]


The other lists

• Customer Number
• Authorized-to-Order

Each customer can have multiple Authorities:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CUSTOMER
[/TD]
[TD]AUTHORIZED-to-ORDER
[/TD]
[/TR]
[TR]
[TD]Cus-001
[/TD]
[TD]Pete T
[/TD]
[/TR]
[TR]
[TD]Cus-001[/TD]
[TD]Roger D
[/TD]
[/TR]
[TR]
[TD]Cus-002
[/TD]
[TD]Alan L
[/TD]
[/TR]
[TR]
[TD]Cus-002[/TD]
[TD]Derek L
[/TD]
[/TR]
[TR]
[TD]Cus-002
[/TD]
[TD]Gordon C
[/TD]
[/TR]
[TR]
[TD]Cus-003
[/TD]
[TD]Bruce S
[/TD]
[/TR]
[TR]
[TD]Cus-004[/TD]
[TD]Jack B
[/TD]
[/TR]
[TR]
[TD]Cus-004
[/TD]
[TD]Eric C
[/TD]
[/TR]
[TR]
[TD]Cus-004
[/TD]
[TD]Ginger B
[/TD]
[/TR]
</tbody>[/TABLE]



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.