Querying Access to form columns from records

Question

At work we've got a roster that's historically been maintained in a huge Excel workbook. That's getting a bit fragile because of staff changes and such, so I'm trying to re-implement it robustly as an Access database.

I've got a table of People (fields include name and some other attributes of people) and a table of Vacations -- each vacation has a person_ID, a start date and an end date.

What I'd ideally like to be able to generate from this is a roster grid like we had in the Excel workbook:

Date    Person A    Person B    Person C
01 Jan  -           -           -
02 Jan  -           -           -
03 Jan  VAC         -           -
04 Jan  VAC         VAC         -
05 Jan  VAC         VAC         -
06 Jan  -           VAC         -
07 Jan  -           -           -
08 Jan  -           -           -
09 Jan  -           -           -

(In this example the Vactions table has two records -- one for Person A, and one for Person B.)

Is that kind of thing possible with Excel? Or am I going to have to write some code?

Thanks!

Answer 1

You could do that with a pivot table in access however displaying data like this is a classic symptom of “Excel to access/database” syndrome.

Mild Tangent

I was always told to thing that in a database there are only 3 numbers 0, 1 and n where n is any number between 2 and infinity. Displaying the data like that is fine when you have a small number of staff but what happens when you get 50 or 300 members of staff? I would look at displaying the data in a different way possibly an interactive form or other type of dynamic display. If it has to be paper then maybe some kind of report broken down per person?

/Mild Tangent

Answer 2

I would recommend

• Building yourself a date table (a table with a row for every date you're likely to use, say 01/01/2000 to 31/12/2020)

• With the help of this table build a view that lists each date for each person and shows if that person is on vacation or not on that date (left join between vacation and date table and join between vacation and person)

• Now the trickier bit. You now need to build yourself a cross-tab query.... but with access that can be fun (haven't used access for a while so maybe wrong on this).

• However, I would recommend building the view in the 2nd step above and then using the power of Excel to build a pivot table of the data for you. Link your Excel spreadsheet to your Access database and every time your user needs a refresh of the data, they, urm, refresh it!

HTH