UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount Issue, Access 2016    
 
   
billstets
post Jun 20 2019, 09:10 AM
Post#1



Posts: 3
Joined: 20-June 19



First of all, I am not a developer and my Access skills are fairly basic. So I may be completely missing the boat on how to do this. Here's what I'm trying to do: Create a query using a master table that has a unique field [variable1] (this stupid field name is has a story behind it that has to do with my incompetence!). There is another a table [Player Shares] that has multiple records with this value. In the query, I want to create a field that counts all the records in Player Shares that match variable1 in the query. So for each unique variable1 record in the query, there will be a value in this field, like 1 or 3 or whatever. There may be records in the master table that don't exist in Player Shares. I don't know if that would creates a complication. Also, the fields "Revenue Stream - 2" and "variable1" are text fields. I am using Office 365. Here is the formula I have tried:

Expr1: DCount("[Revenue Stream - 2]","Player Shares","[Revenue Stream - 2] ='" & [variable1] & "'")

The query runs but returns a 0 on every record.

I am perplexed. Maybe what I'm trying to do isn't possible to do in this way.
Go to the top of the page
 
cheekybuddha
post Jun 20 2019, 09:30 AM
Post#2


UtterAccess VIP
Posts: 11,424
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

It's probably easier to use a sub-query and join it:
CODE
SELECT
  m.variable1,
  s.MyCount
FROM [MasterTableName] m
LEFT JOIN (
  SELECT
    [Revenue Stream - 2],
    COUNT(*) AS MyCount
  FROM [Player Shares]
  GROUP BY [Revenue Stream - 2]
) s
       ON m.variable1 = s.[Revenue Stream - 2]
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
billstets
post Jun 21 2019, 07:46 AM
Post#3



Posts: 3
Joined: 20-June 19



Thanks so much for the response. My Access skills are so rudimentary that I'm just using the interface to build tables and queries! However, I did find where to put the SQL code and tried this query. It actually ran, but the count field was blank for all records.
Go to the top of the page
 
cheekybuddha
post Jun 21 2019, 09:14 AM
Post#4


UtterAccess VIP
Posts: 11,424
Joined: 6-December 03
From: Telegraph Hill


Are you able to upload a copy of your db (or a db with just the two tables involved), and someone here can take a peek?

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Jun 21 2019, 10:09 AM
Post#5


UtterAccess VIP
Posts: 9,751
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, as David indicates, it would be helpful if we could see your db. This usually resolves the issue quickly.

However, if you want to do more testing, you might consider looking further into the data like this ...

First (borrowing from David's query selection) ...

SELECT [Revenue Stream - 2]
FROM [Player Shares];

... to see what the values are.

Second, if that produces a good list ...

SELECT [Revenue Stream - 2], COUNT(*) AS MyCount
FROM [Player Shares]
GROUP BY [Revenue Stream - 2];

... to see if you get counts - or errors.

Third, list out the variable1 values (is that a table field or a calculation ...).

That might start pointing you in the right direction.

Even though this might not matter here because of the brackets, I am always concerned about fields named this way: [Revenue Stream - 2]. Including a dash (a minus sign) followed by a space and a number in the field name, is always a red (or perhaps yellow) flag.

Just some thoughts while you are contemplating posting a db as David suggested.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
gemmathehusky
post Jun 21 2019, 12:34 PM
Post#6


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


[Revenue Stream - 2]

looks wrong.
You have a field called [Revenue Stream - 2] ?

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
billstets
post Jun 24 2019, 09:50 AM
Post#7



Posts: 3
Joined: 20-June 19



Thanks very much for the help. I'm heading out on vacation and will look at this when I return
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 10:43 AM
Post#8


UtterAccess VIP
Posts: 11,424
Joined: 6-December 03
From: Telegraph Hill


Happy Holidays!

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 06:02 PM