Solved Report Records don't match my table's!!! :(( (1 Viewer)

[silversun]

Hi,
Continue working on my project, close to the end I have a new issue with my report. Please help me on this.
I have a larger table (Table A) and a smaller one (Table B) that has only 4 fields. Five values that are Foreign key to table A are stored in table B. The way I have my relationships set up I need to have 5 instances of table B to get connected to table A according to attached image. I am expecting to see five different values in my related fields in my report but it is showing and repeating the same first value in my report. Please look at the images and if you understand my design then help me to fix the issue.
This is a part of report with 2.5 records in it:

This is a part of table B:

Here is a snapshot of my Automatically Generated Query as source of my report and I had to add last five fields manually:

And finally this is a snapshot of my Relationships that connects tables in my simple database.

As you can see the same tbl_risks has been repeated five times to provide the primary keys to five fields of tbl_all_Logs. There should be a way to make it work properly and you are the one who knows it, please help.
Silversun

 

[plog]

Your structure's still not right. As discussed in Your prior thread:

Solved - Table Fields calculation

Hi everybody. I have a primary table as source of a data entry form. Textboxes of the form are getting their values from the second column of different child tables. All child tables are related to primary table by their primary key's. I need to sum the values in third column of child tables...

www.access-programmers.co.uk

You need a junction table (tblRiskLogs). Consult my posts in the prior thread, specifically the one with the Image.

 

[silversun]

Your structure's still not right. As discussed in Your prior thread:

Solved - Table Fields calculation

Hi everybody. I have a primary table as source of a data entry form. Textboxes of the form are getting their values from the second column of different child tables. All child tables are related to primary table by their primary key's. I need to sum the values in third column of child tables...

www.access-programmers.co.uk

You need a junction table (tblRiskLogs). Consult my posts in the prior thread, specifically the one with the Image.

I have created the junction table tbl__RiskLogs and filled up some of the records manually.
Now, if I want to get my report based on this new table, each record shows only one of the risks and related info from tbl_all_Logs.
How can I get a report of all five records in this table and show them as a single record? I don't think the grouping is my answer here.
Also if I take my report based on each record in tbl_all_Logs then I still have the same original issue, am I right?
Can you please help me on this?
Thank you

 

[plog]

All those old risk value ids come out of tbl_all_Logs (social_distancing_id, purpose_id, etc). That connection is made by tblRiskLogs.

How can I get a report of all five records in this table and show them as a single record?

Technically, you don't. To generate the layout of the report in your first post of this thread you will need a sub-report. The left side of your report (everything from tbl_All_Logs) will be the main report. The right side (all the risks) will be a subreport. Hee's a link on how to create those:

Create and use subreports - Microsoft Support

Subreports let you view records from the "many" side of a one-to-many relationship on an Access report.

support.microsoft.com

The datasource of that subreport will be a query of tblRisks and tblRiskLogs. Specifically this query:

SELECT tblRiskLogs.Log_ID, tblRisks.risk_type, tblRisks.risk_Desc
FROM tblRiskLogs INNER JOIN tblRisks ON tblRiskLogs.Risk_ID = tblRisks.risk_ID;

 

[silversun]

All those old risk value ids come out of tbl_all_Logs (social_distancing_id, purpose_id, etc). That connection is made by tblRiskLogs.



Technically, you don't. To generate the layout of the report in your first post of this thread you will need a sub-report. The left side of your report (everything from tbl_All_Logs) will be the main report. The right side (all the risks) will be a subreport. Hee's a link on how to create those:

Create and use subreports - Microsoft Support

Subreports let you view records from the "many" side of a one-to-many relationship on an Access report.

support.microsoft.com

The datasource of that subreport will be a query of tblRisks and tblRiskLogs. Specifically this query:

SELECT tblRiskLogs.Log_ID, tblRisks.risk_type, tblRisks.risk_Desc
FROM tblRiskLogs INNER JOIN tblRisks ON tblRiskLogs.Risk_ID = tblRisks.risk_ID;

Your instructions about modifying tables and sub-report helped me to solve the issue. I now have my project close to finish and appreciate all your helps and others people in Access Programmers.
Thank you