Create a report with sub reports related to two independant tables

[Gari]

Hello,

I have turned around this problem but because of my little knowledge of Access, I did not find any solution. So I am posting once again my request in the forum (that really helped me so far!) with the hope to find a solution.

I have two tables: Table1 and Table2.

Both tables are independent. However, they share one common field: [Client Name]. Key Field is AutoNumber.

The tables record the excesses of customers over their current account.

There are two types of excesses: Overdrafts & Irregulars.

Table1 records Overdrafts.
Table2 records Irregulars.

Customers can have Overdraft only, Irregulars only or both.

The excesses can either be reviewed or not (a check box is to be ticked if the excess has been reviewed). One query for each table filters the same.

The customers are identified through the field [Client Name], which is the same for both tables.

I want to create a report that lists me, for one particular customer, the list of Overdraft and Irregulars that have not been reviewed.

I thought about creating sub-reports but it did not work as I wanted. The main problem being that I don’t know how to retrieve the companies that appears either in Table1 or Table2 or both. I thought about doing some filtering but the problem is that it does not give me a list but two columns, with a duplication of data. Maybe the ideal would be first of all to create a list with all the companies that appear either in Table1 or in Table2 or both, get rid of the duplicates and then use this list to create the report, with sub report being linked to this main field.

I apologise if I am a bit confusing but it is not even clear to me on how to proceed. So, should you have any questions, I would be more pleased than answer you back.

I have almost finished my database and this is one of the last steps to go !

Thank you very much for your help.

Best regards,

G.

[nico5038]

You should have one table tblCustomers with a CustomerID, CustomerName, etc.
This to make sure you'll see the total set of customers. Now you can have a customer in table1, table2 or both tables.
To create a complete tblCustomer you can use:
select customername from table1
UNION
select customername from table2;
SAve the above query and change it into a MakeTable query.

This table can be JOINed to table1 and table2 in a query with a so-called "outer join". To create the outer join place all three tables and connect them by the CustomerName using drag and drop. Next doubleclick the connectionline and make the tblCustomer "leading" by chosing option 2 or 3.
This query will show all customers with the field(s) you need from table1 and 2

Getting the idea ?

Nic;o)

[NeoPa]

Assuming that, being a bank, you actually PK the accounts on the Name field. Not the best idea generally, but as in this case you are certain that it's unique, you can get away with it.
To create your list you will need to select the required client somehow - I will assume that you will do it with a prompted parameter in the query unless / until told otherwise. {Other Common Details} refers to other common fields that you want to show from both tables. You say you have none but I fail to see the use of a query simply showing the Client Name over and over again. I suspect you've missed out some info maybe.

SELECT [Client Name],{Other common Details}
FROM [Table1]
WHERE ([Client Name]=[Select Client Name])
UNION ALL SELECT [Client Name],{Other common Details}
FROM [Table2]
WHERE ([Client Name]=[Select Client Name])

Nico's solution includes the table that holds your client information. It's hard to think that you could want a report without this information, so look at his solution, but if you're certain you do, this should do it for you.
At the end of the day, we can only respond to the question as asked with the info provided.

[Gari]

Dear both,

Merging two tables in one using UNION solved my problem and I could design the report I wanted.

Thank you very much for this tip. It is very useful and I might use it more often !

Best regards,

G.

[nico5038]

Glad we could help Gary and success with your application !

Nic;o)