Forum Discussion
is a subquery the appropriate method to capture this data?
Relative newbie to MS Access here. Definitely green, though i have the skills to build/modify simple scripts involving a few tables with linked/matched data points. New scenario i can't easily solve, and in reading it appears a subquery may be a workable option.
I have data stored in a table, which unfortunately stores multiple rows with different info for the same account, a sample of which is shown below so someone can gather the organizational structure.
| Account Number | Bucket Sequence | Total Charges | Total Payments | Total Adjustments | Transfer Out To Next Bucket | Current Balance |
| 12345 | 1 | 3250 | 1400 | 1100 | 750 | 0 |
| 12345 | 2 | 3250 | 200 | 300 | 250 | 0 |
| 12345 | 3 | 3250 | 150 | 0 | 0 | 100 |
The report I want is to have all this data on a single line as below, but my current abilities don't allow it, as access basically forces me to an export that mimics the table above, which is unfortunately useless without a ton of manual intervention to get the result below...
| Account Number | Bucket 1 | Total Charges | Total Payments | Total Adjustments | Transfer Out To Next Bucket | Current Balance | Bucket 2 | Total Charges | Total Payments | Total Adjustments | Transfer Out To Next Bucket | Current Balance | Bucket 3 | Total Charges | Total Payments | Total Adjustments | Transfer Out To Next Bucket | Current Balance |
| 12345 | Plan 1 | 3250 | 1400 | 1100 | 750 | 0 | Plan 2 | 3250 | 200 | 300 | 250 | 0 | Self Pay | 3250 | 150 | 0 | 0 | 100 |
Any advice on if SubQueries may get me to this set of results?
Thanks
Joe
- you can use VBA in the Report to build that Table or
if you have that Format table in your Report, add code to the Detail Section
to Calculate those Column Values.
Calculating in Report can only be done when in Print Preview View of your report. - You could create a query to pivot the data as shown, but I think Arnel's approach is more appropriate because of the variability of data possible in the real world. Your example shows three "buckets", and as long as there are three and only three such "buckets" in your data, a query would be fine, if somewhat complex. But in the real world, data seldom stays the same. VBA or a report design probably offer a better chance of flexibility.
That said, I'm unclear as to why the proposed report design is required, or requested. What does it offer that a more traditional report design wouldn't? Do people really want to read a super-wide report like that? - Except for the changed column label and addition of the word "Plan ", the second table has the same data as the first row in the first table - so it's difficult to envisage the result you're after. Do you want output that shows a single row for each Account Number that sums all the values for that account number ?
