Need help with reducing amount of queries!

[DeV]

I have a bunch of queries at the moment and they practically all do the same thing. I'm writing a program that makes a top 10 of events. These events occur in a certain area:

• 6-2
• 6-3
• 6-4
• Pilot
• General

All of these areas can generate some sort of event. The eventtypes are:

• Alarm
• Change DDP
• Change Point
• OAR
• Rest

I want a top ten of all the Alarms made by 6-2, all of the ChangeDDP's by 6-2, all of the Changepoints made by 6-2 etc etc all the way down to all of the Rests made by General. I currently have to use 25 queries (one for each combination) to get the top 10's into one table. Something I'm definetly not proud off but hey it was the easiest solution so far. Now that I'm adjusting the flaws and bugs I would like to make this in a little bit less queries.

That's why I'm asking you guys for help on this, since my main Access knowledge isn't that big.
Thnx in advance,

DeV

 

[Louie]

You have two tables? Can we see the structure please?

 

[jjturner]

DeV,

FYI, without some more detail about how your tables are setup or what a Top 10 is meant to represent I'm at a loss as to how to help. Maybe someone else has a better grasp of what your scenario is, but just a suggestion . . .

Regards,
John

 

[Pat Hartman]

You can do it all in a single query. How's that for a reduction? This article shows how to build the query:
ACC: How to Create a Top Values Per Group Report

 

[DeV]

Sorry Pat but once again I'm just too stupid to understand that. Anyway I'll lay down the structure of the two databases. Anyway try to imagine the following, a factory that produces stuff in reactors. All reactors have particles like seals, valves, pumps etc. All these things can generate errorcodes. These are being imported from csv files. They get imported into the EventJournal table. This table has the following layout:

Amount - Amount of that specific error generated
MessageStr - The message it displays
PointTag - Unique tag that represents the valve, seal etc.
PointDesc - A description of the point
ChangedAttr - What got changed at that point
AlarmActInactiveA - AlarmActInactiveD - If the alarm is active or not
EventMonth, EventYear - Month and year it occured
AreaCode - The ones written in my first post.
Type - The ones written in my first post.

My goal is to put the following fields:
Amount, PointTag, PointDesc, EventMonth, EventYear, Areacode and Type into another table called Top10. This table is supposed to contain the 10 most generated errors in a specific area of a specific type. The areas and types are written above. I currently use the following code to do so:

INSERT INTO tblTop10
SELECT TOP 10 [EventJournal].[Amount] AS Amount, [EventJournal].[PointTag] AS PointTag, [EventJournal].[PointDesc] AS PointDesc, [EventJournal].[EventMonth] AS EventMonth, [EventJournal].[EventYear] AS EventYear, [EventJournal].[AreaCode] AS AreaCode, [EventJournal].[Type] AS Type
FROM EventJournal
WHERE AreaCode='6000/2' And Type='Alarm';

With areacode ranging in the values written in the first post, and type ranging like written above too. So now I have 25 friggin query's to do so. One for each combination (5 areas, 5 types = 25 queries). So I'm currently looking for a way to reduce that. I noticed Pat's post, and it sounds good, but the problem is that I don't understand it, since I'm normally not working with Access that much so a noobish explanation would be of use.

Thnx,

DeV

 

[Pat Hartman]

Did you try to build the example the article describes? Short of doing it for you, I'm not sure what to say.

 

[DeV]

I made the example you linked too, and that works.
Then I tried implementing it into my own DB but that refused to work even after a lot of fiddling around.
I wouldn't dare asking you to make it for me, that's your own decision.
Not going to force you to say the least

 

[Pat Hartman]

Why don't you post your try.

 

[DeV]

Because it might contain private/confidential data.

 

[Pat Hartman]

How can the SQL contain private data? We don't need to see the recordset.

 

[DeV]

I found the solution for it by messing around in VBA.
Thankyou for your help though Pat. This is it if you're interested.

Public Sub FillTable() 
    
  Dim db As Database 
  Dim i, j As Integer 
  Dim Areas(5), Types(5) As String 
  
  Areas(1) = "6000/2" 
  Areas(2) = "6000/3" 
  Areas(3) = "6000/4" 
  Areas(4) = "Pilot" 
  Areas(5) = "Algemeen" 

  Types(1) = "Alarm" 
  Types(2) = "ChangeDDP" 
  Types(3) = "ChangePoint " 
  Types(4) = "OAR" 
  Types(5) = "Overig" 

  Set db = CurrentDb 
  
  For i = 1 To 5 
    For j = 1 To 5 
      db.Execute "INSERT INTO tblTop10 SELECT TOP 10 [EventJournal].[Amount] AS Amount, [EventJournal].[PointTag] AS PointTag, [EventJournal].[PointDesc] AS PointDesc, [EventJournal].[EventMonth] AS EventMonth, [EventJournal].EventYear] AS EventYear, [EventJournal].[AreaCode] AS AreaCode, [EventJournal].[Type] AS Type FROM EventJournal WHERE AreaCode='" & Areas(i) & "' And Type='" & Types(j) & "';" 
    Next j 
  Next i 
  
  db.Close 
  Set db = Nothing 
  
End Sub