Grouping, Selecting data from query.

[Ryath]

Hi all,

Currently i have a select query which looks like this.

SELECT [TBL Base Data].[Aircraft Model], [TBL Base Data].[Unit Description], [TBL Base Data].[Removal Date], [TBL Base Data].[Approved], [TBL Base Data].[Failure Type], [TBL Base Data].[Failure Mode]
FROM [TBL Base Data]
GROUP BY [TBL Base Data].[Aircraft Model], [TBL Base Data].[Unit Description], [TBL Base Data].[Removal Date], [TBL Base Data].[Approved], [TBL Base Data].[Failure Type], [TBL Base Data].[Failure Mode];


What can i do to change this so when the query is ranthe user can select:-

Aircraft Model is picked from a list from a table?

same with unit description?

Removal Date is automatically grouped by month? (current form "dd/mm/yyyy&quot

Approved is a check box which shld be "YES" for all records on search.

Failure Type shld be "F" for all records on search.

and the failure mode is a set choice of abbreviations whichare listed and a total is given for each month.

sorry for the long post.... but i'm gettin flustered here :/

thx all


Will

 

[GoDawgs]

First off you can completely get rid of the group by section of your query...since you are grouping by everything in your query then it's exactly the same as just running the query with the SELECT and FROM parts.

To do the rest of your needs you'll need to add a WHERE clause...I'll throw out some options for each if I can...

1. Picking aircraft model. The best way to do this seems to be to have a form on which you have a combo box pulling the data desired from a table (look up combo boxes in help, isn't too tough). Then in your query you just add something like this:
[Aircraft Model]= [FormName]![ComboBoxName]
Substitute the name of the form and the name of the combobox appropriately.

2. Same for the unit description

3. Not sure how you want to group by month (is this in a report or what?)...you can just sort this field ascending, that will basically get everything grouped by month since it will put the dates in order.

4. [TBL Base Data].[Approved]= 0 I think this is correct for "yes" check boxes...although it might be = -1...not sure

5. [TBL Base Data].[Failure Type]="F"

6. Not sure what this means

Basically this is what we have so far:

SELECT [TBL Base Data].[Aircraft Model], [TBL Base Data].[Unit Description], [TBL Base Data].[Removal Date], [TBL Base Data].[Approved], [TBL Base Data].[Failure Type], [TBL Base Data].[Failure Mode]
FROM [TBL Base Data]
WHERE [Aircraft Model]= [FormName]![ComboBoxName]
AND [Unit Description]= [FormName]![ComboBoxName]
AND [TBL Base Data].[Approved]= 0
AND [TBL Base Data].[Failure Type]="F"
ORDER BY [TBL Base Data].[Removal Date]

Hopefully this helps you out...

Kevin