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

[skydivetom]

Experts:

I need some general recommendations for managing "calender-based" (dynamic data) in an Access database. Attached JPG provides a very generic query output/report. Please allow me to further elaborate on the information:

Generic Data (orange columns):
- Fields [LastName], [FirstName], [Service], [Type] are part of the "Employee" table
- Field [EmployeeID] is part of the "Billets" table
- Field [Office] is part of the "Organization" table
- In my actual DB, I have roughly 100+ fields across these tables; however, for illustration purposes, the sample data should suffice

Dynamic Data (blue columns):
- As part of the Coronavirus response, our organization tries to minimize "social interaction" (in the office) by allowing people to work from home on different days/times.


- The information in the blue fields is **totally arbitrary**.
- In this example though, we'd like to develop a matrix where we can create a report/output that will show us the "generic data" (orange fields) plus calendar-based information.
- In this case, for illustration purposes, I merely included two (2) weeks of proposed data (WHO will work WHERE & WHEN).

Here's What I Need Some Help With:
- The information in "orange" is generic and I can easily create an output query from across multiple tables.
- For those columns in blue, I'd like to get some general ideas/recommendations as to how I can create a table where **Dates are now field names**.
- In this example, I merely included a two-week range; in reality however this period of coordinating telework/office work could extent to several months.

My Questions:
- What would be the most efficient way to manage a view like shown in the attached picture?
- I am not sure if I want to manually create, e.g., 60 fields to cover a 60-day date range. So, do you have a recommendation that would allow me to store information such as "Telework" or "In Office" (by dates) without having to create a potentially infinite number of fields in new table, e.g., "Telework"?

Thank you,
Tom

[CJ_London]

yes - by doing it the database way, not the excel way

3 tables employees, status, and a table to join the two with a date (your calendar)

tblCalendar
CalPK autonumber
CalDate
EmployeeFK
StatusFK

[skydivetom]

Ajax -- yes, absolutely... I want to do it in the "database way". Attached is a very small **sample** DB which contains 3 tables (with some sample fields) + 1 junction table. "Query1" outputs 7 records with fields from all 3 tables.

I'm not entirely sure how do to work in your proposed table. Ultimately, all 7 staff members (employees) would have to be linked to N number of date fields. Do you mind providing me an example based on my attached sample DB?

Thank you,
Tom

[CJ_London]

see attatched db. open frmAssign

I created a table for status and another for the calendar

the only query is a crosstab to display the data

[skydivetom]

Ajax... wow, that's amazing!!! I'm super-impressed with your recommendation.

Now, here's the thing...
- We have nearly 1,200 in the organization.
- For the initial "data call", branch manager will -- more than likely -- return their team's feedback/input via a spreadsheet. On a macro level, I anticipate the individual spreadsheets (from about 40 teams w/, on average, 30 people/team) will pretty much look like the JPG attached in original post.

My question:
- Assuming each of the 30 spreadsheets is filled out completely (and whether or not they'll be merged or not), what would be the most efficient way to "load" them into the database?
- That is, in support of "tblCalender", translating an employee name into his/her ID is easy; similarly doing the same for the status FK.
- Trying to think through how to then create the "CalDate" ID in conjunction with the EmployeeFK and StatusFK

Any final thoughts on the data loading?

Thank you,
Tom

[CarlettoFed]

To do what you ask you must resort to the VBA code and it is necessary to have at least a sample of the excel file to see exactly the structure of the same.

[CJ_London]

As Carletto says - you'll need vba.

you would do the following

Open the excel file as a recordset - perhaps having linked to your employees table and the status table to convert names to ID's so you are actually opening a query
looping through the recordset fields you would construct a sql statement to append each (date) column using the field.name to convert the column name to a date

note that calDate is not an ID - there is not a table listing all the dates.

you'll need to do some unmatched queries to identify any employees or status's in the spreadsheet and not already in your employee and status tables. And perhaps to identify employees who should be in the spreadsheet, but aren't

[skydivetom]

Ajax:

Once I received some sample Excel files, I'll look into developing VBA for data batch load.

At this time, I have two follow-up questions:
1. Formatting: How can I format both the date selector (form header) and dates (field names in right subform) to display date format as = mm/dd/yyyy (vs. m/d/yyyy)?
2. Event: Once I selected a different date in the form header, is it possible to automatically put focus onto the left subform and immediately display the staff members already selected?

Again, thank you for the help and providing me a solid solution.

Cheers,
Tom

[CJ_London]

How can I format both the date selector (form header) and dates (field names in right subform)

you need to ask that question? use the format property (not function) for the control in the form and the field in the query

Once I selected a different date in the form header, is it possible to automatically put focus onto the left subform and immediately display the staff members already selected?

it will once the date control loses focus (i.e. click somewhere else) - perhaps put a button with a click event that does nothing so the uses moves the focus when they click the button.

only other way would be for the user to hit return after selecting a date - or disable the date picker so user has to type in a date and press return.

[skydivetom]

Ok... I figured out the date format. Any suggestions WRT to updating the staff member info once I change a date?

Thanks... I appreciate the help.

[skydivetom]

Ajax:

I apologize... one final questions:

Existing Process:
- User opens form "F172_N1S_AssignWorkStatus"
- Left subform is "empty" while right subform is populated
- At this time, user could place cursor into left subform and select staff member's names **WITHOUT** having picked a date first.
- If so, then a column "<>" is inserted without an actual date. See attached JPG.

My question:
How can I ensure to be able to select staff members/status only once a date has been selected? That is, how can I lock the left subform until the date is picked... once date has been picked, left subform should be unlocked.

[CJ_London]

disable the left subform until there is a date populated

[skydivetom]

Thanks... that worked perfectly.

Last piece (for proof of concept), I'll do some research on how to create a "dynamic" report based on the dynamic crosstabfields/headers. If you have recommendations, I'd welcome any suggestions.

In the meanwhile, THOUSAND THANKS for assisting me in this effort. Much appreciated.

[CJ_London]

the problem you will have with a dynamic report is the width - max 22 inches. Allow say 5 inches for the data on the left (in orange on your original post) leaves 17 inches. Say 3/4" per date gives you around 22 days. You want 60 days.

Using the query you have a maximum of 255 columns, less the 6 orange ones so can easily achieve 60 days.

Screens/paper/pdf are not going to be wide enough anyway so either you need to be able to scroll horizontally for screens or get the tape out for paper.

You can still create a report with a subreport using the query which can scroll and lock the leftmost columns - but this will only be useful in preview mode

So before creating a dynamic report think about the end use. It might be better to export the xtab to excel....

but by all means, provide some filters to limit to a date range, office, employee type etc. If you are going to limit to say 7 or 14 days then yes, you can use a dynamic report.

[skydivetom]

Good advice... I will follow your recommendation as expert to CLS.

Again thank you for all of your help on this thread.