By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,899 Members | 1,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,899 IT Pros & Developers. It's quick & easy.

Help With Query using Form referencing table with group of items

P: 4
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.
Oct 19 '10 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
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 ?
Oct 19 '10 #2

P: 4
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....
Oct 19 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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.
Expand|Select|Wrap|Line Numbers
  1. =([Forms]![frmRETIREMENT_REPORT]![cboGroup])
  2.  
Oct 19 '10 #4

P: 4
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....
Oct 19 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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
Oct 19 '10 #6

P: 4
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!
Oct 20 '10 #7

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. select Unit from tblLinked Group By Unit;
  2.  
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
Oct 20 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.