Need general recommendation for table design (i.e., store text info where Date = Fieldname)

[isladogs]

Hi Tom
I've not been following this thread in detail but I saw your comment about dynamic crosstab reports,
I have many of these in my commercial apps but also in some of my example apps using similar code.
For example, see the crosstab report in my Extended File Properties app http://www.mendipdatasystems.co.uk/e...ies/4594398115

The code can be adapted for any other dynamic crosstab report


Hope that helps

[skydivetom]

Colin -- thank you... I'll have a look. Appreciate you chiming in.

[skydivetom]

Ajax:

I discussed the framework w/ the team... your recommendation is solid.

However, I'd like to make one fundamental change in the structure. Instead of using "dates" (e.g., 03/16/2020 through 03/31/2020), I'd like to change it to "10 days out". So, as part of the change, I added another NUMBER field into table T171_N1S_JunctionCalendar and (temporarily) called it "CalendarDate2".

Afterwards, I updated the query (for the subform)... the columns "1", "2", "3", ..., ..., "10" are properly displayed in the form. Also, I changed the date picker to a combo with 10 values.

Here's what I couldn't get to work correctly though... once I change the value from "1" to "2" in the combo, the left subform does not show the seven associated staff members in the form.

What did I miss? How should it be changed so that I can use the combo (vs. date picker) but get the same results as before?

Thank you!!
Tom

[CJ_London]

10 days out? from when? today? If so then that is a moving feast so your calendateDate2 column needs changing daily. And what are you going to do when you reach day 10? delete all the records and repopulate? If you don't then day1 will be repeated and you will see the current day1 plus your new one. You might get away with it if your sort by calendar date descending (rather than calendardate2 ascending).

With regards why the form is not populating, since txtDate is now a combo with values 1 to 10, you need to change the subform linkmaster property to reflect that (i.e. change to calendardate2). The calendar date field then becomes irrelevant - subject to the above comment.

You could have just used the date field then for column header used a calculation - something like

DayNum:datediff("d",calendardate,date()-1) (assuming day 1 is today)

with a criteria

between 1 and 10

[skydivetom]

Basically, we'd like to schedule the telework for next two weeks. If I'm on a Thursday (in week 1), I know I have to reference day 4. If in week 2, it'll be day 9.

I am not sure where to locate the "subform linkmaster property". Also, not really clear on following you WRT to the DayNum:datediff("d",calendardate,date()-1) (assuming day 1 is today). Any additional help how to fix it would be greatly appreciated.

Thousand thanks again!

[CJ_London]

I am not sure where to locate the "subform linkmaster property"

in the main form design view, select the subform control - look on the data tab

Also, not really clear on following you WRT to the DayNum

your original comment was not clear, hence my questions. I just saying if you want the report for 'the next 10 days from today, whenever today is' then you could use a formula as I suggested to calculate the day number that you want based on the calendardate. If your 10 days are a specific 10 days (i.e. doesn't matter if you run the report today, tomorrow or next week, it will produce the same columns and values) then ignore my comments as they are not relevant.

[skydivetom]

Ajax - thank you for the additional feedback. It works like before again.

And, yes, to your last comment... days are a "specific days". Appreciate all recommendations/questions you have offered!