umbrella name for a group

[DKM]

Hi

Am slightly new to access and am working on some features in my db. What i am trying to do it to group a number of entries in a field under a genreic term so that my query returns all entries in the group rather than each on seperatly.

I currently have the tables set up like:

Tbl1
Formane - Surname - Unit
Jo - Bloggs - Unit 1
Jo - Bloggs - Unit 2
Jo - Bloggs - Unit 3

I have a query which brings in fields from another table as well as these using a 1 to Many relationship.

to select the unit i want to look at i have a combo box that contains that units, but this means i can only return all, Unit 1, Unit 2 or Unit 3.

What i want to do is adapt the combo box so that it groups Unit 1 and Unit 2 under 1 name such as Support and unit 3 under a different grouping.

I have seen something like this on another thread but cant find it again now, i believe it used a case statement to specify which entries fall under each heading but would appreaicte any advice on this one.

Dan

 

[Moniker]

Just add an extra field to that table and use it as your filter.

Tbl1
Formane   Surname   Unit     GroupName
Jo        Bloggs    Unit 1   GroupA
Jo        Bloggs    Unit 2   GroupA
Jo        Bloggs    Unit 3   GroupB

 

[DKM]

Sorry should have said the table this data is on is a linked table held outside of the db (excel format) so the fields are fixed. I have it set this way as the data used is produced from a seperate programme that i run a report from weekly.

could an IIF statement be used to create the new field in the query? Something like IIF (Field name = "unit 1" OR "Unit 2", Group A, Group B)

 

[Moniker]

Sort of. You could easily make a new field that way, but the maintenance would be hell.

A better (and much easier to maintain) solution would be to have a few lookups in there. In other words, import your Excel spreadsheet as normal, then either use an UPDATE or APPEND query to move that data to an Access specific table that contains the columns you need.

This would require some normalization on your part, but it's worth it in the long end. If you want me to spell it out for you, I can, but what I'm telling you is that once you have the data from Excel into Access, you can easily move it around in different tables. These tables should be normalized so that maintenance and updating become something you don't even think about.

The structure you have (or at least the example given) is highly denormalized and won't lend itself to this too well. However, once things are as they should be, this becomes a button-click operation, meaning it's just a few queries to accomplish what you want.

A simpler, but unclean, solution is to import the Excel data into an existing table structure that already contains the "GroupName" field. Again, you're walking into a maintenance nightmare with the existing structure as it was presented, and I can go into detail on it (and what the proper structure is) if you like, as can many other forum members.