How to hold matrix data in a table?

[petko]

Hi

In my DB there is a set of company-running rules that are addressed to different groups within the company, like drivers, bookkeeping, warehouse, electricians etc. Both groups and workers scope and number might change from time to time.
I need to make a table that holds which worker belongs to which of these groups (one worker to one or more groups).
The easiest way of setting this for the user would be kind of a matrix-look form where lines would hold the name of workers, columns would hold the groups and at the cross points there would be check boxes to set or unset membership. However I can't find the way in what table sturcture this could be utilized.
Could someone give a hint? I would appreciate any ide for making such form, as well.

Thanks in advance

Petko

 

[MarkK]

This is called a Many-To-Many relationship and takes three tables. Your tables will be Worker, Group, and, WorkerGroup. Keys work like . . .

tWorker
WorkerID (Primary Key)
FirstName
LastName
OtherData

tGroup
GroupID (PK)
GroupName
OtherData

tWorkerGroup
WorkerGroupID (PK)
WorkerID (Foreign Key)
GroupID (FK)
OtherData

Hope that helps.

 

[petko]

Hi MarkK,

Thanks for your help, it works. However I can't find the way how to make a form on the tWorkerGroup table that would provide the data for the user in a matrix-look sheet. Something that looks like an Excel sheet where line heads are WorkerID, column heads are GroupID. Do you have any idea on that?

Thanks in advance

Petko

 

[plog]

I have an idea of how to do it, but I don't think you can accomplish it. It's one of those things, that if you have to ask about ideas, you probably don't have the skillset to do it.

I'm not trying to crap on your skills, but what you want to achieve doesn't involve just advanced Access knowledge, but pretty advanced Visual Basic coding skills. You'd need to create a dynamically built unbound form just to display the data you want. Then saving user changes adds an even more difficult level of complexity to achieve.

You'd be better off creating standard forms/subforms to input your data, then having a report to display your data in the matrix you desire. Or possibly moving this back to Excel.

 

[MarkK]

You can make that kind of grid with a crosstab query, but it won't be editable.