Partial transpose access table

[VAer]

I am working on a project (using Access as backend database, front end is Visual Studio application), first, I am not sure if I should do it in Access or Visual Studio. It is complicated for me, let me explain as clearly as I can.



I have three Access tables: Employee, Team, WorkingHours

Table Employee: Username is unique
Table Team: Every employee can belong to multiple teams.
Table WorkingHours: It records how many hours each employee has worked for the company. So maximum one record per employee per day. Assume they are all part-time employee. It is totally possible that an employee does not work during certain days.

Front end application: It has ComboBox for Team. For example, user select team1 from the dropdown list. I want to display last 7 days of data for team1, by employee & by date

So I want the program to do below things:

1. Create field names: First 3 fields are same as table Employee fields(these 3 fields are not transpose), the last 7 fields are the last 7 days. it is even better to include Sun/Mon/Tue...., something like this: Username FirstName LastName 12/27/2020(Sun) 12/28/2020(Mon) 12/29/2020(Tue) 12/30/2020(Wed) 12/31/2020(Thu) 1/1/2021(Fri) 1/2/2021(Sat)
2. I really want to have dates as field name in the displayed table, which makes project become complicated, at least too complicated for me. It can be fixed number of days, such as 7 days in this example, but it needs to be dynamic, it is 7 days before Now(). So 10 field names should be like this(it is better to add Sun/Mon/Tue ... to the field name): Username, FirstName, LastName, Now()-7, Now()-6, Now()-5, Now()-4, Now()-3, Now()-2, Now()-1
3. Now program should fill out first 3 fields based on table Teams information.
4. Then program looks into table WorkingHours and fill out remaining 7 fields. The output result should be something like the screenshot.

Now the question is: The output table should be displayed in front end application DataGridView, so should I make a query inside Access first (the query will show output table format), then front end application just needs to run the query and display the data in DataGridView? I feel like it is easier to set up the query in Access, then front end application just needs to run the query. But it is also too complicated for me to set up such kind of query.

Any comments? Any suggestions?

Thanks.

[isladogs]

With the structure you have now, it could be done with a crosstab query on table WorkingHours

However, your tables have repeated data and that's not appropriate.

The data in table Employee shouldn't be in the other tables apart from using the Employee primary key field (username?) as a foreign key in the other 2 tables.
At the very least, remove both name fields from the Teams & WorkingHours tables
Better still use an autonumber ID field as the PK field and use that as the FK in the other two tables removing username as well.

The design could I'm sure be improved further but that's a starting point

If you've done the above, create a select query joining tables Employee & WorkingHours. Save it as e.g. qryEmployeeWorkingHours
Then create a crosstab query based on that. Use the wizard to help you do so

[VAer]

Use a crosstab query

How to do it in my example? Could you explain more?

Thanks.

[June7]

First thing, Teams and WorkingHours tables should not store employee names, just Username. You are not using autonumber primary key?
Second, advise not to use reserved words such as Date as names.

The query you want is a dynamic CROSSTAB with filter parameters. Forcing column headers when data does not exist is a challenge. One approach is to include a dataset of all possible dates. This can be a table or calculated in a query. The latter can definitely cause slow performance but even the table might as well.

Review this tutorial on dynamic CROSSTAB https://www.fmsinc.com/MicrosoftAcce...%20expression.

An alternative involves VBA and writing data to a 'temp' table - table is permanent but data is temporary. This is usually done when frontend is another Access file and temp table is in frontend.

[isladogs]

First make the table changes I described earlier.
You should also change Date to e.g. WorkDate and Hours to WorkHours to avoid conflict with Access function names.

Then read about crosstab queries at https://support.microsoft.com/en-us/...0-2cd8484667e8.
If that seems too complicated for you, do a Google search for Access crosstab queries. There are many other Web pages to choose from.

Then using the crosstab query wizard, add username, first name and last name as your three row headings, Date as your column heading and Hours as the Value
That will make more sense after reading the kinks

[VAer]

Thank you, all. Probably I will need to remove FirstName and LastName from some table first. It makes a lot of sense to remove it, no primary key for those two tables.

I will look into the details in a few days.

But the bottom question is: I should do it in Access, not Visual Studio, correct?

Thanks.

[June7]

A CROSSTAB SQL statement could possibly be constructed in VS and used as source for DataGridView. Regardless of where query is built, complication is dynamic nature of column headers. One way to handle is described in tutorial.

[VAer]

First thing, Teams and WorkingHours tables should not store employee names, just Username. You are not using autonumber primary key?
Second, advise not to use reserved words such as Date as names.

The query you want is a dynamic CROSSTAB with filter parameters. Forcing column headers when data does not exist is a challenge. One approach is to include a dataset of all possible dates. This can be a table or calculated in a query. The latter can definitely cause slow performance but even the table might as well.

Review this tutorial on dynamic CROSSTAB https://www.fmsinc.com/MicrosoftAcce...%20expression.

An alternative involves VBA and writing data to a 'temp' table - table is permanent but data is temporary. This is usually done when frontend is another Access file and temp table is in frontend.

So are you saying: running a query with all possible dates is faster than running a query with only those 7 dynamic days? But what if the date range is big, like half an year?

[VAer]

First make the table changes I described earlier.
You should also change Date to e.g. WorkDate and Hours to WorkHours to avoid conflict with Access function names.

Then read about crosstab queries at https://support.microsoft.com/en-us/...0-2cd8484667e8.
If that seems too complicated for you, do a Google search for Access crosstab queries. There are many other Web pages to choose from.

Then using the crosstab query wizard, add username, first name and last name as your three row headings, Date as your column heading and Hours as the Value
That will make more sense after reading the kinks

Thank you.

I have already removed Name from those two tables. I am not sure if I use Date as field name, it is just an example, for the purpose of asking question. I did not know it conflicting with Access function. I should use something else as question example in the future. The whole example is made up. The project just has same data structure, but not same tables(nothing about employees' WorkHour)

[VAer]

First thing, Teams and WorkingHours tables should not store employee names, just Username. You are not using autonumber primary key?
Second, advise not to use reserved words such as Date as names.

The query you want is a dynamic CROSSTAB with filter parameters. Forcing column headers when data does not exist is a challenge. One approach is to include a dataset of all possible dates. This can be a table or calculated in a query. The latter can definitely cause slow performance but even the table might as well.

Review this tutorial on dynamic CROSSTAB https://www.fmsinc.com/MicrosoftAcce...%20expression.

An alternative involves VBA and writing data to a 'temp' table - table is permanent but data is temporary. This is usually done when frontend is another Access file and temp table is in frontend.

Let me ask another question, if working a crosstab query in access, can I make the date in order? Oldest date on the left and newest date on the right.

Thanks.

Edit : suddenly, your comment including a dataset of all possible dates gives me an idea. Actually project table does not have numeric field (WorkHour), for that table, it has three fields : one field is date, another two fields are string. So I can create a dummy username and include dummy in each team; since the field is string, I can make Sun/Mon/Tue/... for dummy username.

Now I have two new questions : is it possible to make dummy username on the top of other team members, that will look like a table with two header rows, which is perfect for me. Second question is : if dummy username can work, can I have the dynamic 7 days from there? Can I do it in Access?

Thanks.

[June7]

1. Not sure I understand your concept of dummy user name - if there are not the records in table, calculating 'dummies' is not simple. A UNION query might accomplish.

2. Have you reviewed tutorial that demonstrated dynamic CROSSTAB?

[VAer]

1. Not sure I understand your concept of dummy user name - if there are not the records in table, calculating 'dummies' is not simple. A UNION query might accomplish.

2. Have you reviewed tutorial that demonstrated dynamic CROSSTAB?

I have not reviewed tutorial, don't have time. Maybe I will review it at night or weekend. I would like to ask some questions first and get some general idea how to do it, if it can be done in Access.

Here is my idea of dummy:

1. Table Employee: an employee username called dummy, which will stay there forever
2. Table Teams: Whenever a new team is created, dummy will be a team member. dummy will be in every team forever.
3. Table WorkingHours: It has string field and Date/Time field only. dummy has one record for every day.

Now come to my question:

1. Can I use Accress crosstab query to create something like the screenshot output table.
2. Is it possible to make username dummy as first row in output table
3. Since table WorkingHours date range is large, is it possible to set date range in crosstab query, such as last 7 days?

Thanks.

Edit: I have another issue now, let me start with another post with ANOTHER screenshot. Don't want to confuse with this screenshot. This screenshot refers to content in this post.

[VAer]

Here is another issue: In the screenshot of above post, dummy looks like a second header row, but actually it is an employee. In dataGridView, user can sort the field, which will cause username dummy not showing as second header row. I need some other ideas to solve the problem.

[VAer]

Can access have Date format like this Excel screenshot(it is a date format in Excel)? But it is too long, if it is Tue, Dec 15, 2020 or Tue, Dec 15, 20, the shorter, the better, but it is better to include Tuesday information. If I can do this in Access field and DataGridView, I can ignore dummy username in above posts.

I think below output table is acceptable to me. It is for last 7 day range, but displaying only 3 days, since there are 4 days without records in table WorkingHours.

Is it possible to do it in Access crosstab table? If so, can I make the oldest date on the left, newest date on the right?

Note: I made some typo in all the screenshots, it is not December 2021, it is December 2020.

[isladogs]

I have not reviewed tutorial, don't have time. Maybe I will review it at night or weekend. I would like to ask some questions first and get some general idea how to do it, if it can be done in Access.

Then I suggest you make time. You cannot expect any of us unpaid volunteers to give up our time giving you a solution when you aren't prepared to make the effort yourself.
Good luck with your problem. I will drop out now