Help With Query using Form referencing table with group of items

[Kevin Sims]

Hopefully the title makes enough sense to get my point across.

Using Access 97 here.

I am trying build a query which uses a form to select the criteria. It works fine if the source contains just one item.
Here is the criteria example:
=([Forms]![frmRETIREMENT_REPORT]![cboUNIT])

Problem is the table I am referencing from cboUNIT is setup into groups and each group contains multiple items. For example GROUP1 is units 4,7 and 8.

I can make the query / form work just fine if cboUNIT is just bound to a table which contains one unit per record, but I need the form to be able to just select my defined groups.

So my question is, is it possible using my example criteria to have cboUNIT be bound to a table with a record that is something like ("Unit 04" or "Unit 07" or "Unit 08")? Please help.

[nico5038]

I would create a new table "tblUnitGroup" with:
UNIT
Group
And thus have rows with:
GROUP1 4
GROUP1 7
GROUP1 8
etc..

Now JOIN this table to the units in the other table and use the combo for filtering the UNITs

Getting the idea ?

[Kevin Sims]

Maybe not...how would I in turn use this as the criteria for my query? If I set it up to do just one unit, the query returns exactly what I am looking for on just that one unit. I created the table with groups in an attempt to be able to select multiple units on the form which belong to those assigned groups.

I know I can type into the criteria of the query manually ("Unit 04" or "Unit 07" or "Unit 08"), but I am looking for a way on the form to be able just select GROUP 1 and have it run the query with that criteria....

[nico5038]

Just create the table and next JOIN this new table in the form's and report's query by the Unit.
In the combo you now select the Group and all joined rows with this Group (and thus Units 4/7/8) will appear in the report.

=([Forms]![frmRETIREMENT_REPORT]![cboGroup])
 

[Kevin Sims]

I guess I'm still not clear what you mean. The table I currently have has the following: a Group Number and the Units contained in that group;

GROUP UNITS
"Group1" "("Unit 04" or "Unit 07" or "Unit 08")"
etc....

I have the form looking at this table. Should I just not be using this table at all? I am just very confused by what you are saying....

[nico5038]

I was under the impression that the Group wasn't recorded in your table. When your database is fully normalized you would have such a separate Group/Unit table.

When the group is in your table, all needed is to switch the combo from Unit to (Distinct) Group and your query can work on that.

Nico

[Kevin Sims]

I realized I need to give more info here. My access database is actually linked to another database. In the other database, the only ID that is on each asset it the Unit and not the group. So you are correct, Group is NOT recorded in a table and for that matter neither is the Unit name in my database. Now, how should I build tables and how many? One for Groups and one for Units? Please help clarify for me. Thanks!

[nico5038]

I would start with a Group-Unit table as described in my previous comment.
This can be filled with the Unit's from the linked table by using a Groupby query like:

select Unit from tblLinked Group By Unit;
 

Create this query and change it into an "Append" type that's filling your tblGroupUnit.

Now add manually the Groups the Unit's belong to and phase 1 is ready.

Next JOIN the form and report query with this new table and change the form's combo into a Group selection.

That would be all.

Nico
Thus all unique