ACCESS FEED: [MS_AccessPros] RE: (SQL) Query Builder

Jumat, 20 September 2013

[MS_AccessPros] RE: (SQL) Query Builder

I agree with Duane's belief of storing stuff in tables, but I think it's possible to achieve your goal within the query.

Try adding a field to the query with an expression similar to: "DeptGroup: iif([T_Personal Injury Accidents].Department in ("Weld Line","Weld"),"Welding",iif([T_Personal Injury Accidents].Department in ("Paint","Final Detail"),"Painting",[T_Personal Injury Accidents].Department))". Change your combo box's control source to DeptGroup.

As for doing the right thing and storing the group code in tables, you could either add a column to table [T_Personal Injury Accidents], or create a new table with columns [Department] (to link to your [T_Personal Injury Accidents] table) and [DeptGroup], and any other columns you might need. (Probably a good idea to make [Department] the primary key to avoid duplicates.) You can manage your groupings in this table and just link it to your [T_Personal Injury Accidents] table where needed.

Another option is writing a function to perform the same operation as the "DeptGroup" expression above.

The advantage of data stored in tables or functions is that you only need to manage it in one place. Otherwise you run the risk of having to update an expression among versions of reports, forms, queries, etc.

Hopefully I got all that right.

Randall

--- In ms_access_professionals@yahoogroups.com, <duanehookom@...> wrote:

Rohn,

I'm a believer in storing stuff in tables. If you want specific records grouped together then you should have something in data that stores which values are together.

Duane Hookom MVP
MS Access

----------------------------------------

> From: reverson@...
>
> This query works for selecting one department at a time. The HR department would like to select or group departments to display on the resulting report that is displayed by this forms combobox [Forms]![F_Accident_by_AREA]![Dept_FormCombo]. Currently you can select:
>
> Fabrication
> Weld Line
> Weld
> Paint
> Clean
> Crane
> Assembly
> Hydraulics
> Final Detail
>
> The HR department would like to group Weld & Weld Line, Paint & Final Detail, ect and other groupings. Is there a way to change my query or do I need a new approach for the Form/Query/Report process I am using?
>
> Thanks, Rohn
>
> SQL QUERY BUILDER:
> SELECT [T_Personal Injury Accidents].Department, [T_Personal Injury Accidents].[Date of Accident], [T_Personal Injury Accidents].[Description of Accident], [T_Personal Injury Accidents].[Corrective Action 1], [T_Personal Injury Accidents].[Corrective Action 2], [T_Personal Injury Accidents].[Corrective Action 3], [T_Personal Injury Accidents].[Corrective Action 4], T_Departments.[Dept#]
>
> FROM T_Departments INNER JOIN [T_Personal Injury Accidents] ON T_Departments.[Department Name] = [T_Personal Injury Accidents].Department
>
> WHERE ((([T_Personal Injury Accidents].Department) Like "*" & [Forms]![F_Accident_by_AREA]![Dept_FormCombo] & "*") AND (([T_Personal Injury Accidents].[Date of Accident]) Between [Forms]![F_Accident_by_AREA]![Date_From] And [Forms]![F_Accident_by_AREA]![Date_To]));