Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Sep 26, 2022

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 NumberBucket SequenceTotal ChargesTotal PaymentsTotal AdjustmentsTransfer Out To Next BucketCurrent Balance
1234513250140011007500
12345232502003002500
123453325015000100

 

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 NumberBucket 1Total ChargesTotal PaymentsTotal AdjustmentsTransfer Out To Next BucketCurrent BalanceBucket 2Total ChargesTotal PaymentsTotal AdjustmentsTransfer Out To Next BucketCurrent BalanceBucket 3Total ChargesTotal PaymentsTotal AdjustmentsTransfer Out To Next BucketCurrent Balance
12345Plan 13250140011007500Plan 232502003002500Self Pay325015000100

 

Any advice on if SubQueries may get me to this set of results?

 

Thanks

Joe

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    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.
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    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 ?

Resources