0

MS Access table proforma and report proforma

I tried to generate a report in ms access by selecting all fields from the table but, i want to restrict the report that to be show only records and column name and non-empty value only

Date Unit Name Tasks completed 01-10-2022 A 1- True 02-10-2022 B 3- True, 8-True 03-10-2022 C 5- True ---- etc....

userRP
  • 1
  • 1
  • I think you'll need to simulate UNPIVOT, by a union on something like `select date,key,col1` for each of the columns – Nathan_Sav Dec 02 '22 at 12:48
  • Can you please elabirate – userRP Dec 02 '22 at 13:38
  • select date,key,col1 union all select date,key,col2 union al select date,key,col3 etc.etc https://www.google.com/search?q=simuate+unpivot+in+access&oq=simuate+unpivot+in+access&aqs=chrome..69i57j33i10i160l2j33i22i29i30.8020j1j7&sourceid=chrome&ie=UTF-8 – Nathan_Sav Dec 02 '22 at 13:40
  • 1
    Either normalize schema or live with 'workaround' solutions. Dynamically removing columns for each record is not possible. Column is either in RecordSource or it isn't. A workaround is VBA procedure writing data to a 'temp' table to present the desired output. – June7 Dec 02 '22 at 17:39
  • A UNION query could certainly rearrange to more normalized structure but that would mean multiple rows for each date/unit. – June7 Dec 02 '22 at 17:43
  • The UNION query could be source for a CROSSTAB PIVOT using DCount() as demonstrated in https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access – June7 Dec 02 '22 at 18:09
  • However, building a stable report based on CROSSTAB is difficult due to dynamic field headers of CROSSTAB. – June7 Dec 06 '22 at 21:38

0 Answers0