By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,316 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

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

P: 294
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:
Expand|Select|Wrap|Line Numbers
  1. BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
  2. 135            Mark       100200      5.3333       1 
  3. 135            Mark       100200      3.0000       2
  4. 136            John       100200      5.3333       1 
  5. 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

Expand|Select|Wrap|Line Numbers
  1. BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
  2. 135            Mark       100200      5.3333/2       1/2 
  3. 135            Mark       100200      3.0000/2       2/2
  4. 136            John       100200      5.3333/2       1/2 
  5. 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
Expand|Select|Wrap|Line Numbers
  1. BeneficiaryID  Name  EmployeeID  AwardUnits  Units Remaining
  2. 137            Dan       100200      5.3333/3       1/3 
Feb 18 '14 #1
Share this Question
Share on Google+
23 Replies

Expert Mod 15k+
P: 31,768
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.
Feb 18 '14 #2

P: 294
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?
Feb 18 '14 #3

Expert Mod 15k+
P: 31,768
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 ;-)
Feb 18 '14 #4

Expert 5K+
P: 8,701
What you are suggesting can easily be accomplished via Recordset Programming, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim intCountOfBenIDs As Integer
  5. Set MyDB = CurrentDb
  6. Set rst = MyDB.OpenRecordset("SELECT * FROM tblTest", dbOpenDynaset)
  8. With rst
  9.   Do While Not .EOF
  10.     intCountOfBenIDs = DCount("*", "tblTest", "[BeneficiaryID]=" & ![BeneficiaryID])
  11.       .Edit
  12.         ![AwardUnits] = ![AwardUnits] / intCountOfBenIDs
  13.         ![Units Remaining] = ![Units Remaining] / intCountOfBenIDs
  14.       .Update
  15.         .MoveNext
  16.   Loop
  17. End With
  19. rst.Close
  20. 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.
Feb 18 '14 #5

P: 294
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.
Feb 18 '14 #6

Expert Mod 15k+
P: 31,768
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().
Feb 18 '14 #7

P: 294
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 19 '14 #8

Expert Mod 15k+
P: 31,768
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.
Feb 19 '14 #9

P: 294
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.
Feb 21 '14 #10

P: 294
So I tried a couple things and threw them into VBA in the form of
Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. SELECT TempBenePayoutTbl.BeneID, TempBenePayoutTbl.EmployeeID, Sum(TempBenePayoutTbl.UnitsRemaining) AS SumOfUnitsRemaining, TempBenePayoutTbl.BeneFirst, TempBenePayoutTbl.BeneLast, TempBenePayoutTbl.PrimaryBene
  2. FROM TempBenePayoutTbl
  3. GROUP BY TempBenePayoutTbl.BeneID, TempBenePayoutTbl.EmployeeID, TempBenePayoutTbl.BeneFirst, TempBenePayoutTbl.BeneLast, TempBenePayoutTbl.PrimaryBene;
Expand|Select|Wrap|Line Numbers
  1. UPDATE (AstBeneficiaryTbl 
  2. INNER JOIN AwardTbl ON AstBeneficiaryTbl.EmployeeID = AwardTbl.EmployeeID) 
  3. INNER JOIN PayoutTbl ON AwardTbl.AwardID = PayoutTbl.AwardID SET PayoutTbl.IsBene = -1
  4. WHERE (((AstBeneficiaryTbl.EmployeeID)=[Forms]![AstProfileFrm]![EmployeeID]) 
  5. AND ((PayoutTbl.CheckRequestDate) Is Null));
My VBA code is probably despicable, but I'm not sure how else to do it.

Expand|Select|Wrap|Line Numbers
  1.             If MsgBox("Update status for death/disability?", _
  2.                 vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
  3.                 Cancel = True
  4.                 Me.StatusCbx.Undo
  5.             Else
  6.                 DoCmd.RunCommand acCmdSaveRecord
  7.                 DoCmd.Hourglass True
  8.                 DoCmd.SetWarnings False
  9.                 DoCmd.OpenQuery "DeathVestingDateUpdateQry"
  10.                 DoCmd.OpenQuery "BeneficiaryAwardsQry"
  11.                 DoCmd.OpenQuery "BeneficiaryAwardsDelQry"
  12.                 DoCmd.OpenQuery "BenePayoutAwardSumQry"
  13.                 DoCmd.OpenQuery "BenePayoutUnitsCorrectQry"
  14.                 DoCmd.OpenQuery "BenePayoutNAVUpdateQry"
  15.                 DoCmd.OpenQuery "BenePytUpdateQry"
  16.                 DoCmd.OpenQuery "BeneEmployeePytDelQry"
  17.                 DoCmd.Hourglass False
  18.                 DoCmd.SetWarnings True
  20.         DoCmd.OpenReport "BeneficiaryAPRpt", acViewPreview
  22.                 Me.Refresh
  23.             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."
Feb 21 '14 #11

Expert Mod 5K+
P: 5,397
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:

Expand|Select|Wrap|Line Numbers
  1. '...aircode and other omitted scripting
  2. Dim zDB as DAO.Database
  3. '...
  4. Set zDB = CurrentDB
  5. '...
  6. zDB.Execute "DeathVestingDateUpdateQry", dbfailonerror
  7. '
  8. 'cleanup code such as closing record sets and seting objects to Nothing
Feb 21 '14 #12

Expert Mod 10K+
P: 12,430
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:
Expand|Select|Wrap|Line Numbers
  2.    x.groupField,
  3.    x.valueField,
  4.    x.valueField / y.NumRecords AS Expr1
  6. FROM
  7.    Table1 AS x 
  9.    INNER JOIN (
  10.       SELECT
  11.          groupField,
  12.          COUNT(*) AS NumRecords
  13.       FROM 
  14.          Table1
  15.       GROUP BY
  16.          groupField
  17.    ) AS y
  18.       ON x.groupField = y.groupField
Feb 21 '14 #13

P: 294
@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.
Feb 21 '14 #14

Expert Mod 15k+
P: 31,768
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.
Feb 23 '14 #15

P: 294
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:
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:
Expand|Select|Wrap|Line Numbers
  1.  [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:

Expand|Select|Wrap|Line Numbers
  1.         Case 5
  2.         'Death/Disability: set vesting date for unvested awards to the current date
  3.             'DoCmd.OpenForm "SeparationDateFrm"
  4.             If MsgBox("Update status for death/disability?", _
  5.                 vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
  6.                 Cancel = True
  7.                 Me.StatusCbx.Undo
  8.             Else
  9.                 Dim db As DAO.Database
  10.                 Set db = CurrentDb
  11.                 DoCmd.RunCommand acCmdSaveRecord
  12.                 DoCmd.Hourglass True
  13.                 DoCmd.SetWarnings False
  14.                 db.Execute "DeathVestingDateUpdateQry", dbFailOnError
  15.                 db.Execute "BeneficiaryAwardsQry", dbFailOnError
  16.                 db.Execute "BeneficiaryAwardsUpdateQry", dbFailOnError
  17.                 db.Execute "BenePayoutAwardSumQry", dbFailOnError
  18.                 db.Execute "BenePayoutUnitsCorrectQry", dbFailOnError
  19.                 db.Execute "BenePayoutNAVUpdateQry", dbFailOnError
  20.                 db.Execute "BenePytUpdateQry", dbFailOnError
  21.                 db.Execute "BeneEmployeePytDelQry", dbFailOnError
  22.                 DoCmd.Hourglass False
  23.                 DoCmd.SetWarnings True
  25.         DoCmd.OpenReport "BeneficiaryAPRpt", acViewPreview
Edit: Thanks for catching that, zmbd.
Feb 24 '14 #16

Expert Mod 5K+
P: 5,397
WRONG: DoCmd.Execute
Right: zdb.Execute
Regard the code box post #12
Feb 24 '14 #17

P: 294
Now I'm getting "Too Few Parameters. Expected 1."
Feb 24 '14 #18

Expert Mod 15k+
P: 31,768
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.
Feb 24 '14 #19

P: 294
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
Expand|Select|Wrap|Line Numbers
  1. [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:
Expand|Select|Wrap|Line Numbers
  1. Award Units Remaining: [AwardUnits] / DCount("*", "AstBeneficiaryTbl", "[EmployeeID] = " [Forms]![AstProfileFrm]![EmployeeID])
Feb 24 '14 #20

Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [EmployeeID]
  2.          Count(sQ.BeneficiaryID) AS [NumBeneficiaries]
  3. FROM     (SELECT   [EmployeeID]
  4.                  , [BeneficiaryID]
  5.           FROM     [SomeTable]
  6.           GROUP BY [EmployeeID]
  7.                  , [BeneficiaryID]) AS [sQ]
  8. 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.
Feb 24 '14 #21

P: 294
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?
Feb 24 '14 #22

Expert Mod 15k+
P: 31,768
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?
Feb 24 '14 #23

P: 294
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.

When I was calculating the [UnitsRemaining] field in my previous posts, it is the summation of
Expand|Select|Wrap|Line Numbers
  1. [AwardUnits]-[ForfeitedUnits]-[PaidOutUnits]

What does the dataset look like at this point?
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:
Expand|Select|Wrap|Line Numbers
  1. [Forms]![AstProfileFrm]![EmployeeID]

How I currently tried to do it:
Expand|Select|Wrap|Line Numbers
  1. SELECT TempBenePayoutTbl.BeneID, TempBenePayoutTbl.EmployeeID, 
  2.     Sum(TempBenePayoutTbl.UnitsRemaining) AS SumOfUnitsRemaining, 
  3.     TempBenePayoutTbl.BeneFirst,
  4.     TempBenePayoutTbl.BeneLast, 
  5.     TempBenePayoutTbl.PrimaryBene
  6. FROM TempBenePayoutTbl
  7. GROUP BY TempBenePayoutTbl.BeneID, 
  8. TempBenePayoutTbl.EmployeeID, 
  9. TempBenePayoutTbl.BeneFirst, 
  10. TempBenePayoutTbl.BeneLast,
  11.  TempBenePayoutTbl.PrimaryBene;
By summing the [UnitsRemaining] in this ^^^ query.

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BenePayoutTbl ( BeneID, EmployeeID, 
  2.                       UnitsRemaining, BeneFirst, BeneLast, PrimaryBene )
  3. SELECT BenePayoutAwardSumQry.BeneID, 
  4.         BenePayoutAwardSumQry.EmployeeID,
  5.         [SumOfUnitsRemaining]/(DCount("*","BenePayoutAwardSumQry")) AS Expr1,
  6.         BenePayoutAwardSumQry.BeneFirst, BenePayoutAwardSumQry.BeneLast,
  7.         BenePayoutAwardSumQry.PrimaryBene
  8. 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.
Feb 24 '14 #24

Post your reply

Sign in to post your reply or Sign up for a free account.