Solved

Automatically add columns to a query or report if they exist in an input table or query

Posted on 2013-01-30
4
227 Views
Last Modified: 2013-01-30
I have a crosstab query which increases in columns as a feed table is populated to include data for new periods in the year.

An example of the crosstab output is detailed below.....

Measure 01 02 03 etc (pput eriods in a year)
ABC          1   3    7
DEF           2   9   12

From the crosstab i am running a report but i want to automatically include every period as and when the crosstab adds them, is this possible?

Apologies if its not well explained as i am struggling for the right words.

Thanks
0
Comment
Question by:SweetingA
  • 2
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38836974
Not easily, in a report.

Search EE on "dynamic crosstab report" for discussions of this concept.
0
 

Author Comment

by:SweetingA
ID: 38837032
is it easy in a query and then i can just add another step?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38837067
If your data is normalized with a Period or [SomeDate] field, which then becomes your column headers in the crosstab query, then you should not have to do anything to create the additional column in your query results.

The challenging part is displaying the output in a report.  The query should export just fine to something like Excel, but you eventually run out of page space with a report, and you then have to decide which columns to include in the report.

You could implement some form of paging in your query, which would prevent more than X# of columns per page, and this would not be too difficult, but exporting to Excel is pretty simple.
0
 

Author Closing Comment

by:SweetingA
ID: 38837374
Used a solution by GrayL which is very simple as long as you know the eventual names of all columns - simply edit the PIVOT line of the SQL.

PIVOT qry_PDM_Results_CY.Period In ("01","02","03","04","05","06","07","08","09","10","11","12");

Thanks, could have looking for days!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now