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

 

[Micron]

Do it in stages. First create a query joined as you indicated, that returns all the fields. You've not mentioned anything about criteria such as date ranges. If that is needed, you'll need a form to get the dates or create a parameter query so that the query will prompt for them (in the criteria row of the query you'd put something like BETWEEN [Start Date] and [End Date] in the date field). I think I would create a concatenated field in this query since you seem to want to put the customer and sales person name together, so at the top of the design grid you'd have something like Cust_Rep: [Customer] & "_" & [Sales Manager]. Sorting and grouping is not necessary here - the report can do that. When it looks right, you can use the wizard to create your report based on the query. It will ask you about grouping. Based on what I see, you want to group on Customer and again on Order. However, you will get
Cus-002 (Paul)
SO 828
Alan L
Derek L
Gordon C
SO 912
Alan L
Derek L
Gordon C
and I suspect there is no way around that. You can choose to have page breaks after each detail section. I recommend renaming all the report controls by prefacing the names given by the wizard to avoid any issues of ambiguity that can arise when a control has the same name as the associated field. E.G. rename "Customer" as txtCustomer (txt for textbox, cmb for combo, etc.). You might want to Google Access naming conventions' for help.

 

[rjbinney]

Hm. Thought I replied earlier, but it seems to have disappeared.

There is no relationship between "Sales Order" and "Authorized Signatures". So it's not a matter of grouping one by the other, it's just listing the population of one group and then the population of the other group, on one page. The key here is the "One Page" thing...

 

[Micron]

I made a connection between the two based on customer - didn't look exactly right, but I went for it. Not sure why you don't have the two tables related. However, I would not repeat the Customer info in both tables as you seem to have - it's not properly normalized. You should have Customers in tblCustomer and CustID in the other. Other normalization suggestions I might have would depend on the business scenario.

If designing a query isn't the problem (which I now think would have to be a UNION query) then you must be wondering how to split into pages. For that, grouping is very much an issue if you want to split into pages - unless you would be content with having a separate page for each detail row (I think not).

 

[rjbinney]

I don't know that I understand your question about "the tables aren't related".

What's not related is WHO at a customer is authorized to place an order, and WHICH sales orders are open. The information comes from two different existing reports, that The System can't combine. So I am trying to use Access to combine.

The consistent data is the customer ID - one report lists customer ID and sales orders (and sales manager, that's irrelevant except that I want it to print), one lists customer ID and individual names.

All I want is a report that lists open sales orders and individual's names, one page per customer.

 

[xenou]

You can combine two reports in an Access report by using subreports. Pretty much anything you can write a query for you can put into an Access report.

 

[rjbinney]

I keep getting reports that look like the one micron described. How do I get one like my "sample", xenou?

 

[xenou]

I didn't see part of your post so forget about subreports I guess.
What do you call your tables?

 

[rjbinney]

OK. It doesn't have to be formatted like that, but I do need to avoid having a one-to-many type thing (where if there are, say, two sales orders it will list all authorized signatures for each order).

The tables are called.... Wait for it... "Orders" and "Auth"...

I think I'm at the point where I will print out "By Orders" at the top of the page and then re-load the paper and print "Auth" at the bottom of the page, and hope like heck they line up.

 

[xenou]

OK. It doesn't have to be formatted like that, but I do need to avoid having a one-to-many type thing (where if there are, say, two sales orders it will list all authorized signatures for each order).

Now you've confused me. I thought you did want to print all the authorized signatures for each order. For instance in the original post:

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

That's one customer, with all the orders for the customer (2), with all the authorized signatures for the customer (3).