Can I use DCount() to find number of different "ID"

[mcupito]

I am trying to figure out a way (I seem to be stuck in a rut this morning) to find the Count of different BeneficiaryID in a table, and divide 2 fields by that number.

Let me explain further.

Here's a typical row of data:

BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
135            Mark       100200      5.3333       1 
135            Mark       100200      3.0000       2
136            John       100200      5.3333       1 
136            John       100200      3.0000       2

Any ideas are welcome! Thanks

So the records are repeating because these people are both to be paid out the [UnitsRemaining] in the event of the [EmployeeID]'s death, because they have been chosen by the Employee to receive these [Units].

To pay out these Beneficiaries, I am going to need to divide the AwardUnits and UnitsRemaining by the number of different BeneficiaryIDs.

In the scenario I gave, it would be

BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
135            Mark       100200      5.3333/2       1/2 
135            Mark       100200      3.0000/2       2/2
136            John       100200      5.3333/2       1/2 
136            John       100200      3.0000/2       2/2

Note: I need to differentiate by [EmplyoeeID] also. By that I mean, there may be more than just these people with records, so I need to only divide by the "Count" of BeneficiaryIDs where the EmployeeID is the same and the BeneficiaryID are different.

I only need to pay Beneficiaries for whom they are the beneficiary of (EmployeeID).

Also, there may be more than 2 beneficiaries, and there may be less than 2. I just used 2 as an example. So there may also be

BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
137            Dan       100200      5.3333/3       1/3

[NeoPa]

Not unless you pass the name of a QueryDef which either groups the records or uses SELECT DISTINCT.

DCount() (and Domain Aggregate functions generally) cannot take SQL as the Domain parameter.

[mcupito]

Thanks for the reply, NeoPa.

What do you think of going another route. As in going to the Beneficiary Table, using DCount() for all [BeneficiaryID]'s per [EmployeeID] I am running the queries for, and using that in the query I am asking about in my OP?

[NeoPa]

Context is everything Mark. Generally I avoid Domain Aggregate functions from within a query. Each call has to set up and clear a query that has to be run. The efficiency of SQL is based in the fact that it can do multiple operations all together in a single go with the overhead of setting it up just once. Repeated use of D...() functions loses all this.

Without an understanding of your context I can only say it looks like a very bad approach. It's possible that it's the best available. Highly unlikely though ;-)

[ADezii]

What you are suggesting can easily be accomplished via Recordset Programming, as in:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intCountOfBenIDs As Integer

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("SELECT * FROM tblTest", dbOpenDynaset)

With rst
  Do While Not .EOF
    intCountOfBenIDs = DCount("*", "tblTest", "[BeneficiaryID]=" & ![BeneficiaryID])
      .Edit
        ![AwardUnits] = ![AwardUnits] / intCountOfBenIDs
        ![Units Remaining] = ![Units Remaining] / intCountOfBenIDs
      .Update
        .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

P.S. - I am just a little confused on the following:

Note: I need to differentiate by [EmplyoeeID] also. By that I mean, there may be more than just these people with records, so I need to only divide by the "Count" of BeneficiaryIDs where the EmployeeID is the same and the BeneficiaryID are different.

[mcupito]

ADezii - I did think of going the programming route, however that was my last resort.

Clarification: So a single Employee, thus EmployeeID, may be linked to 1, 2, 3, 4 or more different BeneficiaryIDs.

These are the people who should be paid out the remaining award units if the Employee passes away.

That being said, I would like to divide all AwardUnits BY the number of BeneficiaryIDs linked to each EmployeeID.

This would give me the correct number of awards to pay to each Beneficiary.

Hope this helps.

[NeoPa]

There are a number of ways, but it makes sense to start with a SELECT query that is designed to return the relevant values. That way you can simply use DLookup().

[mcupito]

NeoPa, I am having trouble seeing the light at the end of the tunnel on this one..

I am not really sure how to use DLookup() in this scenario.

I mean, I can see it being something like:

DLookup("[BeneficiaryID]", "[BenePayoutTbl]", "[EmployeeID = " [Forms]![AstProfileFrm]![EmployeeID])

But I'm not sure how I would use that to achieve dividing all of the units by how many beneficiaries an employee has.

[NeoPa]

I can see why that might be complicated Mark. Hence I suggested the first step should be to create a query to provide something you can use DLookup() on.

1. Do that first.
2. Try to use DLookup() on the resultant query.
3. LMK if, at that point, you're still struggling.

[mcupito]

I am really doubting my intelligence right now because I am still confused.

I created a query from the BeneficiaryTbl with the appropriate format (the Select query you were referring to). It gets me these results, for example.

BeneficiaryID EmployeeID SumOfUnitsRemai ning
135 100600 10.7438
136 100600 10.7438

Now it's a matter of getting that 10.7438 / # of beneficiaryID's associated with the employeeID.

I apologize, I think you may have misunderstood my question. So I don't believe I need a DLookup() because I have the correct information in the Table I am pulling the data from. I have ONLY the data I am concerned with. I am just not sure about how to divide [SumOfRemainingU nits] by the number of BeneficiaryID's .

[mcupito]

So I tried a couple things and threw them into VBA in the form of

DoCmd.OpenQuery

and I'm getting all sorts of errors.

The first error I get is "Recordset is not updateable"
The second error I get is "Cannot find the object QueryName"

Each query does it's particular thing fine when ran alone, but I think there might be a problem with running them all via VBA?

Here's my list of what they do:
1) Append records to temp table
2) Delete records that were appended
3) Select temp table records and get the Sum of Units (this gives me the appropriate info I am looking for)
4) I use the Select temp table records query and append the records to a Payout table.
5) Update a field in the payout table
6) Delete records from another payout table where they will no longer be paid out

7) Open report

The SQL where I am getting the errors are: (3)

SELECT TempBenePayoutTbl.BeneID, TempBenePayoutTbl.EmployeeID, Sum(TempBenePayoutTbl.UnitsRemaining) AS SumOfUnitsRemaining, TempBenePayoutTbl.BeneFirst, TempBenePayoutTbl.BeneLast, TempBenePayoutTbl.PrimaryBene
FROM TempBenePayoutTbl
GROUP BY TempBenePayoutTbl.BeneID, TempBenePayoutTbl.EmployeeID, TempBenePayoutTbl.BeneFirst, TempBenePayoutTbl.BeneLast, TempBenePayoutTbl.PrimaryBene;

(5)

UPDATE (AstBeneficiaryTbl 
INNER JOIN AwardTbl ON AstBeneficiaryTbl.EmployeeID = AwardTbl.EmployeeID) 
INNER JOIN PayoutTbl ON AwardTbl.AwardID = PayoutTbl.AwardID SET PayoutTbl.IsBene = -1
WHERE (((AstBeneficiaryTbl.EmployeeID)=[Forms]![AstProfileFrm]![EmployeeID]) 
AND ((PayoutTbl.CheckRequestDate) Is Null));

My VBA code is probably despicable, but I'm not sure how else to do it.

            If MsgBox("Update status for death/disability?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "DeathVestingDateUpdateQry"
                DoCmd.OpenQuery "BeneficiaryAwardsQry"
                DoCmd.OpenQuery "BeneficiaryAwardsDelQry"
                DoCmd.OpenQuery "BenePayoutAwardSumQry"
                DoCmd.OpenQuery "BenePayoutUnitsCorrectQry"
                DoCmd.OpenQuery "BenePayoutNAVUpdateQry"
                DoCmd.OpenQuery "BenePytUpdateQry"
                DoCmd.OpenQuery "BeneEmployeePytDelQry"
                DoCmd.Hourglass False
                DoCmd.SetWarnings True
     
        DoCmd.OpenReport "BeneficiaryAPRpt", acViewPreview
     
                Me.Refresh
            End If

Edit: I changed all of the .OpenQuery's to .Execute and added dbFailOnError. I received an error "Method or Data member not found."

[zmbd]

Edit: I changed all of the .OpenQuery's to .Execute and added dbFailOnError. I received an error "Method or Data member not found."

Did you do this? Change the code from: DoCmd.OpenQuery "DeathVestingDa teUpdateQry"
to: DoCmd.Execute "DeathVestingDa teUpdateQry", dbfailonerror

If so that wont work (^_^)
The Execute method is a DBEngine construct, requiring a database object, I usually use DAO; thus, you need something like this:

'[B][U]...aircode[/U][/B] and other omitted scripting
Dim zDB as DAO.Database
'...
Set zDB = CurrentDB
'...
zDB.Execute "DeathVestingDateUpdateQry", dbfailonerror
'
'cleanup code such as closing record sets and seting objects to Nothing

[Rabbit]

A little late to the party but you shouldn't store the calculated value in your tables. The values would become wrong when a beneficiary was added or deleted. And what happens when you rerun the query? It's going to update all the rows, including the ones you already ran the update on. There is currently no way to track the rows that were already updated.

Instead you should just have a view that joins to an aggregate subquery to return the calculation without affecting what is stored. Something like:

SELECT
   x.groupField,
   x.valueField,
   x.valueField / y.NumRecords AS Expr1

FROM
   Table1 AS x 

   INNER JOIN (
      SELECT
         groupField,
         COUNT(*) AS NumRecords
      FROM 
         Table1
      GROUP BY
         groupField
   ) AS y
      ON x.groupField = y.groupField

[mcupito]

@zmbd, yes - I did try. I received the same compile error "Method or data member not found".

@Rabbit: I am really next to useless when it comes to SQL...

To elaborate on your concerns, Rabbit, the way this is going to work is as follows:

An employee has died. A person or persons they have listed to receive their 'awards' are now the recipient of those awards and will be paid out a lump sum immediately. The awards that have not been paid out yet, need to be divided among the number of beneficiaries per employee. Beneficiaries are no longer important once these queries are ran and they are paid. They essentially leave the system.

I do see your point, Rabbit. Is there more information I could give you as to help us solve this? I would like to learn a thing or two along the way.

[NeoPa]

Mark,
Last time I was here I left you what was intended to be very simple instructions that broke down the way forward into some simple steps. I've not seen that or anything much like it in your subsequent posts. You've made some progress in understanding elsewhere, but if you ramble too much in a thread like this it will be more than tricky for us and you to keep track of the multiple mini-conversations you're embarking upon.

Instruction #1 was about producing a QueryDef to provide the required information. Maybe you should start by considering what information is required in that QueryDef as a first step. I've not seen anything yet which indicates you've managed to get that far.

As always, if an individual step is too complex then the approach is generally to break it down into smaller / simpler steps. What information are you looking for first? When you know that you have a better idea of what's needed in the QueryDef.

Never be afraid of going too slowly. It's much better to proceed as slowly as you need to if that means you can understand and appreciate where you're going.