Dynamically add fields to crosstab query

[qqp]

Hello everyone!

Working on an HOA database (Access 2016). Owners pay certain assessments: monthly, roads, dam, etc. These are of course viewable in a subform based on a cross-tab query IF I have fixed column headings. What I would like to do is allow the user to add assessments as necessary and have them show up in the subform. I realize I need to have some unbound fields and labels to accommodate the possibility of expansion. I am confused about how to set the values of the column headings based on the values in the AssessmentTypeandAmount table. Here is my crosstab query:

TRANSFORM First(MemberAssessments.Amount) AS FirstOfAmount
SELECT MemberAssessments.MemberID_FK, MemberAssessments.LotNumber_FK, First(MemberAssessments.Amount) AS [Total Of Amount]
FROM MemberAssessments
GROUP BY MemberAssessments.MemberID_FK, MemberAssessments.LotNumber_FK
PIVOT MemberAssessments.AssessmentType;

TIA!

 

[dhookom]

If you want the crosstab to appear as a datasheet subform, you should be able to set the source object property of the subform control to:
Query.[Your Query Name Here]

Otherwise you can use VBA code with DAO to change the SQL Property of a saved query to add column headings.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[qqp]

Thanks Duane! Fellow Minnesotan here! I downloaded your sample db (from another post I saw) but I can't open it, too old. I will try what you suggested. Thank you again!

 

[dhookom]

Let us know if you have other questions on this.

Southwest suburbs for me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[qqp]

Hello,

I do need additional help with this. I have had no success with modified (add/delete) assessments showing up in the subform as required. I am currently setting the recordsource of the subform in the on load event of the parent form:

With Forms![members]![ASMTSANDNAMES_Crosstab]
  .Form.RecordSource = "ASMTSANDNAMES_Crosstab"
     .Requery
End With

The crosstab:

TRANSFORM SUM(asmtsandnames.assessmentamount) AS sumofassessmentamount 
SELECT asmtsandnames.lotnumber, 
       asmtsandnames.memberassessments.asmtid, 
       asmtsandnames.memberid_fk, 
       SUM(asmtsandnames.assessmentamount) AS [Total Of AssessmentAmount] 
FROM   asmtsandnames 
GROUP  BY asmtsandnames.lotnumber, 
          asmtsandnames.memberassessments.asmtid, 
          asmtsandnames.memberid_fk 
PIVOT asmtsandnames.assessmentname;

I changed a member's assessment type from Monthly 3 to Monthly 1, and it still shows Monthly 3 in the subform. I've left a few assessment type choices as "Free" thinking users could add new ones as necessary, but this has no effect on the crosstab. I don't know what to do to make this LOOK like a fixed column heading crosstab and yet be dynamic? Too much to ask? TIA!!

 

[dhookom]

If you can live with a datasheet view then do as I suggested in my first post.

If you must have a continuous form then you will need to use code to set the control sources of your text boxes to the generated fields from your crosstab.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[qqp]

I have tried using the datasheet view, and still get the same assessments.:

It should contain these assessments:

I am setting the recordsource of the subform in the on load event of the parent. Is there something else I need to do?

Thanks again!

 

[dhookom]

Don’t set the Record Source. Try set the source object property of the subform control to:
Query.[Your Query Name Here]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016