MS Access. Editable Selection. Grouping of Items based on data other than primary key.

Question

I hope I can explain this clearly. I have a table, lets call it Widgets, that contains information (Color, Size, etc) about many many different widgets that are specified by an ID# which is the primary key. I also have a table, call it Tests, that is related to Widgets through a one to many relationship. Each row in this table represents a test on a particular widget and has information like WidgetID, Date, Info1, Info2, etc. Each WidgetID may have multiple tests in the table Tests. What I'm doing with these is simply displaying various reports that contain data from both tables based on various queryies. I do not have write privileges to either of these two tables. Ok now that that is set up here is what I want to do. I'd like to set up a new table that contains a correlation between the Color of a widget and a new piece of data that I'll call Group. In short, I'd like to be able to define "Groups" of widgets where all the blue, green, and red widgets would be in one group and all of the yellow and orange widgets in another group, etc. (there is no overlap...ie each color correpsonds to only one group) I also want to set up forms that allow the user to add a Group and define which colors go in it, delete a Group, or edit a Group (truthfully add and delete would be enough). What is the best way to do this? I'm not necessarily looking for code, but more so just direction. The best situation would be if each widget had a column in the Widgets table that contained the Groups data, but I do not have write access and neither will the users and I would like to make the Group data editable by the user.

Answer 1

It should be simple enough to set up a table:

Colour
Group

Users add data via a form by selecting the colour from the list and either entering a new or existing group. The whole thing should be possible without any coding, only wizards.

The new table is then joined to widgets on colour to create the various reports that you require.

EDIT re Comments

How you set up the table depends on the results you want returned, let us say that only the colours that are in groups are included, and we have this SQL to join to the widgets table:

   SELECT WidgetID, GroupID FROM Widgets 
   INNER JOIN GroupColour 
   ON Widgets.Colour=GroupColour.Colour

Only the widgets that have a colour that is listed in the GroupColour table will be included in the listing, this is because of INNER JOIN. If the SQL was:

   SELECT WidgetID, GroupID FROM Widgets 
   LEFT JOIN GroupColour 
   ON Widgets.Colour=GroupColour.Colour

You would get all widgets returned, but a Null for GroupID when there was no match in GroupColour. This can be quite useful.

You may wish to read http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/, which is about MySQL, but works quite well for Jet SQL.