I'm creating a report in access, which is grouped by id, case number and Name
But I want the quantity and price aligned together in a single row with the rest of the column fields..
Is there a way to do it ?.
Put the quantity and prices in a sub-report:
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.
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:
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:
The sub-report will grow as needed for the number of items listed and they will appear beside the details of the main report.