is a subquery the appropriate method to capture this data?

[JoeCavasin]

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

[#2]

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.

[#3]

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?

[#4]

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 ?