0

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 ?.

June7
  • 19,874
  • 8
  • 24
  • 34
Prasad
  • 23
  • 2
  • what does your design view look like? – Brad Feb 01 '18 at 22:09
  • Possible duplicate of [ConcatRelated function in a query](https://stackoverflow.com/questions/18940038/concatrelated-function-in-a-query) – June7 Feb 01 '18 at 22:18
  • You can put the quantity and price in a sub-report and place the sub-report at the right-hand end of the detail section of the main report. Then link the sub-report to the master by the appropriate field(s) (id?). The sub-report will grow for each line in the master report according to how many sub-rows there are. – andrew Feb 02 '18 at 01:05

1 Answers1

1

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.

andrew
  • 1,723
  • 2
  • 12
  • 24
  • Thank you for your detailed answer. It helped me to get what I wanted. But I still have one issue, if the Quantity and Price is more than 1 value then detail section gets repeated for the number of values in quantity and price. I have set **MoveLayout for all the Group header section to FALSE** and the **Detail Section MoveLayout to TRUE**. I'm not sure if that is what causing this problem. In the detail section I have subreport(with quantity and price) and all other fields are in their respective group headers. – Prasad Feb 02 '18 at 17:12
  • @Prasad - it sounds like the query is wrong. What is the SQL query in the main report? – andrew Feb 02 '18 at 23:43
  • Andrew : I Linked sub report with main report by Id , that's why the records were repeating(because in the main report I grouped by both Id and name. Some Id's having 2 or 3 names. So the quanties and price of each names were repeating for all the names in the same id). I should link by both Id and Name , But when I try to link by Name it is showing a dialog box to enter parameter value for the field "Name". Thank you andrew for getting back to me. Let me know If you know how to fix this issue . I am new to access, trying hard to make it work. – Prasad Feb 05 '18 at 21:13
  • The dialog box means Name is not in the sub-report's RecordSource. Is this a field in the table containing the price/quantity data? – andrew Feb 05 '18 at 22:18
  • @andrew- "Enter parameter value" issue got fixed. It's because of the column name called "Name" in the table , just renamed to something else and now it's working perfectly. I'm not sure if its because the "Name" is a common identifier or not the appropriate column name. Thank you andrew for helping me out. It really saved me a lot of time. – Prasad Feb 06 '18 at 14:40