ms-accessvbams-access-reports

How to overlap microsoft access - report detail section layout to the previous one to avoid extra rows?


I'm creating a report in access, which is grouped by id, case number and Name This is the report i have created so far

But I want the quantity and price aligned together in a single row with the rest of the column fields.. I want the report to look like this

Is there a way to do it ?.


Solution

  • Put the quantity and prices in a sub-report:

    enter image description here

    The RecordSource of the subreport should be the table that holds those data.

    In your main report, add the subreport from the control toolbox. The wizard will ask you to link the two together based on relationships that it can determine from your table structures.

    enter image description here

    The orange highlighted area in the screen shot is the sub-report now positioned within the detail section of the main report.

    If the wizard was not able to link the two properly, click or drag to select the sub-report (inside the detail of the main report) and then go to the Properties Sheet for the subreport control:

    enter image description here

    The Link Master Fields is a list of fields in the master report's RecordSource that are the key of that record. I'm guessing based on your question that it's either ID or Case. The Link Child Fields is a list of fields in the sub-report's RecordSource that are the join key to the master report's RecordSource, and should typically be the same (based on the fact that the child query ought to have a foreign key relating to the main query). In my example the master report's RecordSource is a Cases table with a primary key called ID and the sub-report's RecordSource is a table containing items each with a foreign key field called CaseID relating to the cases table.

    Now preview the report:

    enter image description here

    The sub-report will grow as needed for the number of items listed and they will appear beside the details of the main report.