retrieving last eventID and second to last eventID (6 Viewers)

[bookrackonteur]

I have a query that sums sales totals based on EventID and School ID
So
ColA(totalSales) ColB(EventID) ColC(SchoolID)
1000 154 1
1200 134 2
900 155 1


Then I have a query (that could easily be a form
that lists all the schools and their last EventID and SalesTotal using a DMAX based on EventID and SchoolID as the criteria

I can't figure out how to get the row for that School ID to show the second to last EventIDs Sales total
School Last EventSales 2ndtolastEvent Sales
1 900 1000

I'm thinking of it like an Excel problem using the Large function so I'm sure I'm just thinking of the problem backwards

thanks for any assistance

 

[theDBguy]

Do you have a date/time field in your record to identify when the event happened?

 

[bookrackonteur]

I don't because its the sum of 2-10 different entries that are all around a certain date

 

[DHookom]

You can't get "last" without using a date/time field. It would help if you provided some more complete data with the significant fields and some sample records.

 

[The_Doc_Man]

The basic rule for this kind of question is, "what is your metric?" That is, what field inherently displays the order of something occurring. You just said you don't have a metric. That means you can't ask the question. So... let's see if I can help you find the metric.

You said this record that you showed us is the sum of entries around a certain date. Do the individual entries show that date? Further, is either the first date or the last date participating in that grouping likely to be the date of interest? Because if the record sample you showed us is the result of a summation query that includes a GROUP BY clause, that is an SQL Aggregate and you COULD ask, in the same query, for either the MIN or MAX date of the bunch.

 

[bookrackonteur]

The Metric is EventID Sorry if that wasnt clear - The event that just happened this week is EventID 192
So the query I showed above totals everything associated with EventID 192
So then I see
King School SchoolID 54 EventID 192 Total Sales 900
The previous King School Event last year was EventID 154 with total sales of 1000

We moved away from Date because it was a little too confusing to be doing Date()+/-7

 

[mike60smart]

Are you able to upload a copy of the database without any confidential data?

 

[The_Doc_Man]

OK, then this is a problem in what is called SUB-QUERY. You get two records, one of them in a subquery where your EventID is from a previous record.

Do the following web search: subquery to determine data from previous record

You will see quite a few videos to explain the technique.