Access Learner - Need help to create report from 2 tables

[F.I.G.]

Hello,

I am learning access and need help with a report that I am trying to generate from two tables. The PDF attachment is a sample of that what I am trying to achieve or something similar, I would greatly appreciate some pointer. If this is not possible, I would greatly appreciate that you explain why and what I need to change.

Thank you in advance
Van

 

[Pat Hartman]

I can't identify how the two tables are related. LoanInfoID is the only common field but it is not the primary key in either table. I also can't identify how the report is supposed to be sorted and grouped. All of the obvious fields are duplicated.
Please explain your schema and how the tables are related.

 

[F.I.G.]

I can't identify how the two tables are related. LoanInfoID is the only common field but it is not the primary key in either table. I also can't identify how the report is supposed to be sorted and grouped. All of the obvious fields are duplicated.
Please explain your schema and how the tables are related.

Hi Pat, Thanks for getting back,

Each loan can have multiple requests (requestdate), it starts with "New Loan" as transaction type, then its can be modified as an "Amendment" or a "Renewal". Each Transaction, there will be documents generated and dated (different from the request date). On the form there is a "Comment" button to open the comment form to allow user to make comment about the transaction.

Now I am trying to create a sorted report to both tables combined. As an exemple of a "New Loan", group all generated documents for that transaction and immidiately follow by comment made for that specific transaction (linked by LoanInfoID, RequestDate and RequestType). Then group all documents generated for the "Renewal" and follow with the comment made...

I attached a more complete of my data base, please start by open the "F_BorrowerLoanHistory", then double click on any date in the "RequestDate" list box that would open "F_LoanDocuments". From there user will make the necessary update and comment by clicking on "Comment" button to open the "F_LoanDocumentsComment". I also attached a revised sample report that I am trying to achieve.

I would greatly appreciate that if you could use my first attachment to explain how this can be done.

Thanks for your help.
Van

 

[Pat Hartman]

As I mentioned earlier, your sample report duplicates information so there is no way to figure out how the app is supposed to work from it. So, I opened the relationships diagram and found that you hadn't made one. I created one based on your foreign key names (good job with that) but as you can see by the arrows, I was not entirely successful. The green arrows are missing relationships. One I couldn't make because the columns are different data types and the other I couldn't make because the tables contain data that prevent RI from being enforced. Both of those need to be fixed. Once you get rid of the duplicate data, the sample report will probably make more sense.
I also have a question about RequestComments. If it is comments about a request, it should be linked to the Request table on RequestID and so RequestID would replace the first three columns although you might want to add a CommentDate and a column to record who made the comment. If RequestComments really does relate to the loan, you should change its name and also remove the Transaction type and remove or rename the RequestDate.

There is also an orphan table that doesn't seem to relate to anything else.

Reports that take data from multiple tables are usually built with a main report and subreports similar to the way forms are built.

 

[F.I.G.]

Pat, thank you so much for your time.

I am working to make the necessary adjustment. Do you have a sample report that generated from two tables?

Again thank you.
Van

 

[Pat Hartman]

The simplest way to generate a report from two tables is to use the report wizard. Select the columns you need from both tables. The wizard will ask how to group and sort the data. Once the wizard finishes, you can tweak the design.