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 SumOfUnitsRemaining
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 [SumOfRemainingUnits] 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 "DeathVestingDateUpdateQry"
to: DoCmd.Execute "DeathVestingDateUpdateQry", 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:

'...aircode 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.

[mcupito]

I think you're 100% correct, NeoPa. I tend to 'freak out' when I don't know how to do something, and sometimes that leads to multi-post threads like this one unfortunately.

So, as of right now. I have a query that moves the records to a temp table. All of the information there is correct.

I then update the old records via Yes/No field to identify them as belonging to beneficiaries.

I then use an aggregate SELECT query to Sum(UnitsRemaining). This gives me 1 record per beneficiary.

So at this point we have data similar to this:
BeneID-EmpID-PrimaryBene-AwardID-AwardUnits-UnitsRemaining
1100 ---- 100600 ----- True --------- 1 ------------- 10 ------------ 5
1200 ---- 100600 ----- True --------- 1 ------------- 10 ------------ 5

I am then appending those records ^^^ to a BeneficiaryPayoutTbl, and creating a field along the way:

 [SumOfUnitsRemaining]/(DCount("*","BenePayoutAwardSumQry"))

I then update a Date/Time field in the BenePayout table.

I then update the records in the EmployeePayoutTbl via Yes/No field (IsBeneficiary) to true. I then delete them.

That's where I stand at this point in time. All of those steps are necessary, however I may have over-complicated things because my VBA code won't run the queries when I try:

        Case 5
        'Death/Disability: set vesting date for unvested awards to the current date
            'DoCmd.OpenForm "SeparationDateFrm"
            If MsgBox("Update status for death/disability?", _
                vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
                Cancel = True
                Me.StatusCbx.Undo
            Else
                Dim db As DAO.Database
                Set db = CurrentDb
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.Hourglass True
                DoCmd.SetWarnings False
                db.Execute "DeathVestingDateUpdateQry", dbFailOnError
                db.Execute "BeneficiaryAwardsQry", dbFailOnError
                db.Execute "BeneficiaryAwardsUpdateQry", dbFailOnError
                db.Execute "BenePayoutAwardSumQry", dbFailOnError
                db.Execute "BenePayoutUnitsCorrectQry", dbFailOnError
                db.Execute "BenePayoutNAVUpdateQry", dbFailOnError
                db.Execute "BenePytUpdateQry", dbFailOnError
                db.Execute "BeneEmployeePytDelQry", dbFailOnError
                DoCmd.Hourglass False
                DoCmd.SetWarnings True
 
        DoCmd.OpenReport "BeneficiaryAPRpt", acViewPreview

Edit: Thanks for catching that, zmbd.

[zmbd]

@mcupito
WRONG: DoCmd.Execute
Right: zdb.Execute
Regard the code box post #12

[mcupito]

Now I'm getting "Too Few Parameters. Expected 1."

[NeoPa]

May I suggest again you forget the overcomplication. It's hard to work out what may or may not be an answer to the basic and fundamental question of what you want in your query that will enable you to get, from the query, the data you're trying to work out. You have to give some proper focus to the question if you want your answer to be helpful. There's only so much rambling that such a thread can recover from. It can get very difficult to follow when so many different ideas are brought up and everything bar the kitchen sink is discussed. Filter out anything unrelated and focus on the specific issue.

[mcupito]

Sure - no problem.

Originally all I wanted to do was take "AwardUnits" that were given to an employee and assign them to the employee's beneficiary (next of kin) and divide them up evenly by the number of beneficiaries.

I tried this by creating a temporary table that includes:
- BeneficiaryID
- EmployeeID
- AwardID
- UnitsAwarded
- UnitsRemaining

The problem at this point was that the UnitsRemaining field held ALL of the units, instead of the employee's total units / # of beneficiaries. (which would divide the units up evenly)

To try and solve this, I appended the records in the Temp Table to a Beneficiary Payout table, and replaced the UnitsRemaining with

[SumOfUnitsRemaining]/(DCount("*","BenePayoutAwardSumQry"))

.

All of these queries are to be ran after a user selects "Death" in a combobox on an employee profile form.

Looking back on it, I am wondering if I should have just calculated the awards when I first appended the records, something along the lines of:

Award Units Remaining: [AwardUnits] / DCount("*", "AstBeneficiaryTbl", "[EmployeeID] = " [Forms]![AstProfileFrm]![EmployeeID])

[NeoPa]

I can see you're trying Mark. Attitude 95%; Application 5%!

Let's see what we can do by piecing the bits of information that do make sense together and see what we can come up with.

I seem to recall a requirement to count the number of beneficiaries (BeneficiaryID) there are for each employee (EmployeeID). That would be done with double aggregation. Something like :

SELECT   [EmployeeID]
         Count(sQ.BeneficiaryID) AS [NumBeneficiaries]
FROM     (SELECT   [EmployeeID]
                 , [BeneficiaryID]
          FROM     [SomeTable]
          GROUP BY [EmployeeID]
                 , [BeneficiaryID]) AS [sQ]
GROUP BY [EmployeeID]

Does that make sense so far? Try it if it does and look at the results to see if you can see why this query is important for what you're eventually trying to achieve.

NB. Attitude is always much more important than application when working with people. We can work on the application.

[mcupito]

Yes! That works. That retrieves the EmployeeIDs and NumBeneficiaries correctly.

I do see the logic behind it, but I really would have never gotten there using SQL, unfortunately.

So is this a correlated subquery using aggregation (count) ?

Edit: So this is where I get confused also. Do I reference this query in another query and divide by NumBeneficiaries?

[NeoPa]

That depends on what you need Mark. I have to say trying to follow what that is in the swirl of different messages that get posted is a struggle. To find the avarage payment would require a correct total amount available. From looking at your data it seems unclear how that would be calculated from the data you store.

I suspect you've seen this link before (Database Normalisation and Table Structures), but your data seems to fall foul of the basic concepts if I understand it correctly. Therefore, trying to get information out of it accurately is going to prove difficult. I've tried to guess what's stored where but it's not clear. I'd be guessing as to what makes sense when I can't see much that does.

This all sounds very critical Mark. Believe me, that is not my intention. I would love to encourage you to approach this with a more precise mindset though. Working in logic relies heavily on precise logical thinking. Database work is possibly more demanding even than ordinary programming work. If things aren't absolutely where they should be then things just don't work.

This leaves us at - "What's the next step?" What do you need to know next in terms that don't rely on us understanding any more of your project than we need to for the question?

[mcupito]

The next step would be using the "NumBeneficiaries" field in the query you created to use as the denominator to determine the number of units a beneficiary would be paid out.

Here's an example:

Say you have 10 units of stock given to you by your company. You died - you have your wife and daughter listed as beneficiaries. They should receive (10 / 2) units each, because you have two beneficiaries.

The user of this system must go into Access, update a Combobox on your specific Employee Profile Form and select "Death" which then runs VBA code.

Now, how do we get there? We need to bring together data from the AwardTbl and BeneficiaryTbl (this table contains EmployeeID as FK so we can link to AwardTbl and get appropriate records).

The number of units come from the AwardTbl. The AwardTbl is comprised of this data:
AwardID (Auto#)
AwardDate (ShortDate)
EmployeeID (FK From AssociateTbl)
AwardUnits (Double)
ForfeitedUnits (Double)
PaidOutUnits (Double)

The Beneficiary table is comprised like this:
BeneficiaryID (Auto#)
EmployeeID (FK)
BeneficiaryName (Text)

I bring the appropriate records together in a temporary table.
BeneficiaryID
EmployeeID
AwardID
AwardUnits
UnitsRemaining

When I was calculating the [UnitsRemaining] field in my previous posts, it is the summation of

[AwardUnits]-[ForfeitedUnits]-[PaidOutUnits]

.

What does the dataset look like at this point?
BeneID-EmpID-PrimaryBene-AwardID-AwardUnits-UnitsRemaining
1100 ---- 100600 ----- True --------- 1 ------------- 10 ------------ 10
1200 ---- 100600 ----- True --------- 1 ------------- 10 ------------ 10

I need [UnitsRemaining] to be divided by the number of beneficiaries for the employee who has died. To reference the Employee who has died, in my queries I use this in the WHERE clause:

[Forms]![AstProfileFrm]![EmployeeID]

.

How I currently tried to do it:

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;
 

By summing the [UnitsRemaining] in this ^^^ query.

I, then, reference that [SumOfUnitsRemaining] field and append the rows to a BeneficiaryPayoutTbl.

INSERT INTO BenePayoutTbl ( BeneID, EmployeeID, 
                      UnitsRemaining, BeneFirst, BeneLast, PrimaryBene )
SELECT BenePayoutAwardSumQry.BeneID, 
        BenePayoutAwardSumQry.EmployeeID,
        [SumOfUnitsRemaining]/(DCount("*","BenePayoutAwardSumQry")) AS Expr1,
        BenePayoutAwardSumQry.BeneFirst, BenePayoutAwardSumQry.BeneLast,
        BenePayoutAwardSumQry.PrimaryBene
FROM BenePayoutAwardSumQry;
 

I, then, will use the beneficiary payout data to create a report to send to payroll and be done with the beneficiaries and their 'awards'.

That brings us to what I have now, which is the VBA code I posted earlier today, where I have a list of 'db.Execute "Query", dbFailOnError'. When I try to select 'Death' for someone particular, I receive: "Too few parameters. Expected 1."

I hope I have shed some new light for you. I can understand where one may have gotten confused because my posts were almost incoherent.