Sumproduct in Access

[kt1978]

Hi all

I have been searching for a solution to this but can't find anything.

This thread is the closest I have found but I need advice on how to build the arrays, if that is the way to do it.
http://www.access-programmers.co.uk/forums/showthread.php?t=188959&highlight=sumproduct

Anyway, from the start.

In excel I have this formula
=SUMPRODUCT(($A$2:$A$11=$A2)*($B$2:$B$11<$B2))+1.
I have attached a small sample that shows what I'm after, and also a sample db just with the same data and my attempt from point 1 below.

I want to do the exact same thing in access, with exactly the same data.

1, Can this be achieved in a query. I was thinking something along the lines of a count(SELECT) statement. (My poor attempt is in the attached file).

2, If I do this in VBA, can I put an entire column in an array at once or do I have to build the array with a record set. I presume I would then write the result back into the table.

Look forward to your responses.

Thanks

p.s. you will also see in the sample file a couple of other formulas that work providing the time is in ascending order.I can use either =COUNTIF($F$2:$F2,$F2) or =IF($F2=$F1,$I1+1,1).

 

[Galaxiom]

I have not looked at the samples but, in Access, what you are attempting requires a different data structure from Excel.

Access does not uses Rows but Records. It cannot easily refer to other record in a calculation like Excel does with Rows.

You most likely need to change the table and add another field that holds a common value that defines whatever it is that makes rows 2 to 11 associated with each other in Excel. Then the process in Access becomes incredibly simple.

Hope this helps.

 

[kt1978]

Hi

I get what your saying in terms of Excel handling data differently but I don't see how my my table can change to solve my problem. Can you suggest any changes or provide and example.

If you have a look at my sample you will see what I mean. I have only put in two columns to keep it as simple as possible.

I'm sure this can be done as I had a similar issue with a time difference between the current and previous record.

I have modified the SQL and I think it is quite close. It works providing there are only 2 records with the same ref. Trouble is... I will have more than 2.

SELECT aTable.Ref, aTable.aTime, Count((SELECT min(aTime) FROM aTable AS Temp WHERE Temp.aTime > aTable.aTime and Temp.Ref = aTable.Ref)) AS [Order]
FROM aTable
GROUP BY aTable.Ref, aTable.aTime;

My two sample files show exactly what I'm trying to do.

 

[Galaxiom]

On seeing your sample you can disregard my comment about the way the data is structured as it is already suited to Access.

Solution attached implimented with a query with a self join on the table. Since you provided the database it was easier to do than explain.

aQuery1 is based on aQuery0. Otherwise there is aQueryWithSubquery that puts it all together.

It does reorder the records. However it is essential to realise that records in a table do not have an intrinsic order anyway. Although they may appear repeatedly in the original order as added this can never ever be relied on. Record order has no meaning in a table.

If order is important, enough information must be included in the records to recreate that order in a query. If the existing fields cannot do it then you may need to include something like a RecordNumber field.

 

[kt1978]

Thank you. That is spot on.

aQuery1 and aQueryWithSub are the same! Not that it matters, it works.

The order does matter, but there are other columns that relate to the order which I took out to keep it simple. I can sort it back in required order afterwards.

I was just about to put the following response down before you replied again. I'll still put in here, but its your solution that i needed.

This is not ideal in that it's not dynamic so it would need running each time another record was added. I was trying to get a DCount on two criteria to work in my query but no joy with that either.

This loops through each record and then use a DCount on on the info from the recordset.

Here is the code

Sub AddOrder()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("aTable", dbOpenTable)
  
  Dim iCount As Integer
  Dim Ref As Variant
  Dim aTime As Date
    
  Do While Not rs.EOF
  
    Ref = rs("Ref")
    aTime = rs("aTime")
    
    iCount = DCount("[Ref]", "aTable", "[Ref] = " & Ref & " AND [aTime] < #" & aTime & "#") + 1
    rs.Edit
    rs("Order") = iCount
    rs.Update
    rs.MoveNext
    
  Loop
  
  rs.Close
  db.Close
    
End Sub

Thanks again

 

[Galaxiom]

Thank you. That is spot on.

aQuery1 and aQueryWithSub are the same! Not that it matters, it works.

Functionally and probably in the the Designer View they are the same but you will see the difference in the SQL View. aQueryWithSubquery includes the equivalent of aQuery0 inside it and is independent of the existence of aQuery0.

This is not ideal in that it's not dynamic so it would need running each time another record was added. I was trying to get a DCount on two criteria to work in my query but no joy with that either.

Once you have the field with the existing data covered you can use a DMax to add new values. This is for adding records with a form.

Me.Ref = DMax("[Order]", "tablename", "Ref=" & Me.Ref ) + 1

Just run this on the BeforeUpdate Event of the Form and it will add the next number as the record is saved. If you need it to show earlier use the AfterUpdate Event of the Ref textbox.

Of course the new record must be later than any existing record in the corresponding Ref group and the new records must be entered in order. If these conditions are not met then you would have to renumber the records after additions anyway.

Your procedure would be slow compared to an update query because it runs a DCount for each record. Domain functions are relatively slow.

Queries are the preferred solution wherever possible because Access can optimise the query making them almost invariably faster than using recordsets. If you do use your code, try indexing the Order field. The Count will be available from the index and speed it up.

However one must be careful with indexes. Although they are good for retreival they slow down the addition of records while the index is updated so it is a balancing act.

BTW Another way to refer to a field in the recordset, which requires fewer characters to type is:

rs!fieldname

It is handy if you don't need to use a variable in the reference. Note it must use the bang operator (!). The recordset is one of the few places where the dot operator does not work.

 

[kt1978]

Thanks again for detailed explanation and advice on a few things.

Functionally and probably in the the Designer View they are the same but you will see the difference in the SQL View. aQueryWithSubquery includes the equivalent of aQuery0 inside it and is independent of the existence of aQuery0.

Yeah, I just looked at the design view - I would have expected it to look different?!?

rs!fieldname

This bit I did know, lol. Don't know why I use rs("Field"), just habit is I suppose.

_____

Not that I need it at present, but is there any mileage in the vba sumproduct. Can it be done in this type of scenario?

 

[Galaxiom]

Not that I need it at present, but is there any mileage in the vba sumproduct. Can it be done in this type of scenario?

I have never used that function in Excel. I see what it does but I don't understand how the name relates to that.

I guess it could be more or less reproduced in Access but it would need a very different syntax to define the range covered by cells in Excel.