Text Box

[ana1]

I am new to Access and am not sure how to do this. I have two tables
Table1 Customer
Fields Cust#,Name, Address, City etc.
Table2 Bank
Fields AutoId,Cust#,Bank Id, Bank Account#

The Bank table can have multiple Account Number for the same Customer with a particular bank. EG
AutoId,Cust#, Bank Id, Bank Account#
1,123,4,123456
2,123,4,789101

I have to create a report which would show Customer Name and all the Bank Account Number for Bank ID '4' for Customer Number '123'on the same page. I did a query and placed Text Box on my report however this prints only one Bank Account# per page. Can it be done? Do I need to add any code to the Text Box?

 

[THWatson]

ana1
Before you go any further, I would suggest you do this...

1. Change the field names in your table to remove # from them. Where you have Customer#, change it to CustomerNbr.
The problem is that Access uses # as a Date delimiter, such as in #12/31/2005#

2. This is optional but it is better not to have spaces in field names. You can have spaces, but then you always have to remember to surround the field name with square brackets, such as in [Bank AccountNbr].

Then, I assume that the two tables are related, on a one-to-many relationship, on the CustNbr field. If that is the case, bring both tables into a query, and have the report use the query as the record source.

You can also use the Customer table as the record source for the report, and use a subreport to reference the Bank table...but using the query as the record source will be probably be easier to design.

Tom

 

[MajP]

ana1,
Here is some additions to what Tom said, and some design thoughts. Although you call Table 2 your bank table it really looks like its an account table. I assume there is a table 3 (which is really the Bank Table) with Bank details such as name of the Bank, address, phone, etc, and Table 2 then links to Bank Details by the Bank ID. If that is the case then you can link Customers to Table 2 by the Customer ID and Table 2 to the Bank Details by the Bank ID. This way you could put a Bank Name on your report not just an ID.
Regardless, link your tables to make a query. If you need to show all customers, even ones without accounts, you need to set the join property of the query to include all Customers and only those Banks that are equal. By default you will only show customers with accounts.
Like Tom said use your query to build the report. If you use the Report wizard it will ask if you want to add grouping to your report. You want to Group by Customer #. If a customer can have accounts at more than 1 bank, then also choose group by Bank ID. Then you will see each person, each bank they have accounts at, and each account at each bank. In the report there will be sections for the grouping. Put Customer information such as name in the customer section and if you use a bank grouping put bank information in the bank section. In the details section put the account information.
Without the wizard use "View" "Sorting and Grouping". Select Group by Customer # and select add a header. In the header put customer information.

Hopes this helps.