MS Access (w/ linked SQL tables) form where rows are transformed to columns for data entry

Question

I have a MS Access w/ linked SQL tables. There is a table for recording the different projects and the people working on them. The table is of this form

ProjectID	PersonID	PersonTitle
12	123	VP
12	124	Senior
12	125	Junior
13	123	VP
13	124	Senior
13	126	Junior
13	127	Junior
14	456	VP
14	457	Senior
14	458	Senior
14	459	Junior
14	460	Junior

I am trying to see if it is possible to create a MS Access Form to enter the above information, but the form should appear in the following format,

ProjectID	VP1	VP2	Senior1	Senior2	Junior1	Junior2	Junior3	Junior4
12	123		124		125
13	123		124		126	127
14	456		457	458	459	460

So when a new project is added the PersonTitle columns will be blank and the user should be able enter information here. Is this possible?

I tried the crosstab feature but this did not allow for data entry.

For simplification, you can assume there will be at max 2 VPs, 2 Seniors and 4 Juniors for each Project. But if there is a way to dynamically update the Forms to allow for any number of roles that would be great (but not counting on it). Any of the title columns can remain blank

I am trying to see if there is a way to achieve this without creating new columns called VP1, VP2 etc as that would be difficult to manage

Answer 1

Without extensive VBA and SQL code, you may not be able to achieve that form to populate that table since both carry different rowwise granularity. Proposed wide-format form carries project level only and long-format table carries project and person levels. Instead, why not build a form in same long format aligned to original table structure?

Given you state the maximum possible person per project rules, consider pre-populating for every new project those maximum possible titles. Below query can be run with an MS Access data macro for every INSERT into projects table. Alternatively, you can use a trigger in SQL Server.

• Using the NOT EXISTS subquery, query avoids appending duplicates into table.
• The maximum possible persons on each project can be stored in a project_persons_lookup table.
• Notice the use of cross join (comma separated tables in FROM clause) for ever pairwise match between project and possible persons.

INSERT INTO project_persons (Project ID, PersonTitle)
SELECT p.ProjectID, ppl.PersonTitle
FROM projects p, possible_persons_lookup ppl
WHERE NOT EXISTS (
  SELECT 1
  FROM project_persons sub
  WHERE sub.ProjectID = p.ProjectID
    AND sub.PersonTitle = ppl.PersonTitle
)

Then create a form of project_persons that will only allow user to update (not add or delete) persons in long format via a datasheet/continuous subform on main project form:

ProjectID	PersonTitle	PersonID
1001	VP	...combo box to persons table...
	VP	"
	Senior	"
	Senior	"
	Junior	"
	Junior	"
	Junior	"
	Junior	"
1002	VP	...combo box to persons table...
	VP	"
	Senior	"
	Senior	"
	Junior	"
	Junior	"
	Junior	"
	Junior	"