adding a column each time query is run...

[Pandorah]

I am building a db that lists number of entries by person each two week
period. So right now it shows a list of people with the number of entries
each under a column...period 1. The next time I run this query I want it to
take my current sheet and add the new information in a new column...so it
would then show the list of people with column period 1...and another column
period 2...ongoing every two weeks...I don't know how to tell Access to do
this.
Any help is appreaciated.
Thank you

 

[Michel Walsh]

You don't.

You append records vertically, rather than adding a field, horizontally. You
see, a table is designed to grow vertically which also has a lot of benefit
when it is time to retrieve the data.

Maybe a table design like:

Person, DayNumber, PeriodNumber, Quantity



for the fields, and add a full new record for a new {person, day, period)
as required will be much preferable.

Sure, that makes that data horrible to READ, for a HUMAN being....
sometimes. That is the beauty of Access, you don't have to save your data
the way you want to see it! Tables are for saving the data, and
forms/reports are to read it.

In this case, you can "pivot' the data in the previous table using a
crosstab query: you will GROUP over Person, your will PIVOT over, say,
the computed expression: periodNumber & "-" & dayNumber, and use, say
SUM(quantity) for fill each "cell" of the tableau. And you can use that
crosstab as source for your report.



Vanderghast, Access MVP