Best Practice?

[qwert231]

K, I have a table where records are associated with groups. Like this:

RecID Desc Path Group
1 FirstItem c:\path FirstGroup
2 SecondItem c:\newPath FirstGroup
3 AnotherItem c:\path SecondGroup
4 SomeThing c:\path2 ThirdGroup

I am going to have another table that will collect options for orders which specify the groups they want, like this:

OrderID FisrtGroup(Yes/No) SecondGroup(Yes/No) ThirdGroup(Yes/No)
123456 0 -1 0

I want a query that will give me all the items and their paths from only the groups that were selected.

What's the best way to do this?

If I need to restructure the tables that is fine, I simply used these to demonstrate what I want.

 

[Golom]

Your second table isn't in first normal form and that will create instant problems.

If you build the second table as

OrderID   SelectedGroup

123456     FirstGroup
23456      SecondGroup
23456      ThirdGroup

Then a simple join query will give you the result that you want. You will need a common field to link the tables (presumably OrderID) although I don't see that in your first table.

 

[qwert231]

The first table is a listing of files. Each file is associated with a group. There can be more than one file in a group.

Also, per order, more than one group can be shown. So your table wouldn't work Golom.

 

[Golom]

You may be right. I don't know the details of the business problem as you do ... but, if you will notice, the table I suggested DOES show more than one group per order. If you have this structure (or one similar to it) then you can run simple SQL like

Select T1.Desc, T1.Path, T2.SelectedGroup
From Table1 As T1 INNER JOIN Table2 As T2
ON T1.Group = T2.SelectedGroup
Order By T1.Desc, T1.Path

With all the selections stored as separate columns in Table2, you will need to resort to complex nested IIF statements to sort out what has or has not been selected.