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 Date Count Problem, Access 2016    
 
   
rmcafee
post Sep 27 2019, 05:29 PM
Post#1



Posts: 6
Joined: 26-September 19



Have this DCount function in an Access query.

RunningCount: DCount("[Student ID]","[Tardy Data Query]","[Student ID] = '" & [Student ID] & "'" & "And [Date]<=#" & [Date] & "#")

It works great except that it counts instances of the student ID as the same instance if it occurred on the same day.
So if a student ID occurred on the same day, it will count it as the same instance.
If two instances are on the same date it counts it like 3 and the next instance on the same date as 3 too.
So it can go like 1,2,3,3,4,5,5,6 in its count. Repeating 3 and 5 because they occurred on the same day.

I want it to count each instance separately no matter what date it occurred on.

Go to the top of the page
 
Doug Steele
post Sep 27 2019, 06:01 PM
Post#2


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry, but it's behaving exactly as it was intended to.

Why do you need this? If you're using it in a report, you can easily have the report generate consecutive numbers so that you don't need to do it in the query.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
rmcafee
post Sep 27 2019, 06:24 PM
Post#3



Posts: 6
Joined: 26-September 19



Yes, it is.
I need it to behave "differently" to count each one. I need help to get it to count the other instances separately.
I have to get an accurate account of all instances even though 2 or more of them may have occurred on the same day.
This is for a project I'm working on for my school.

If this can't be altered to do this, then I can move on.
thanks
Go to the top of the page
 
Doug Steele
post Sep 27 2019, 06:37 PM
Post#4


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Does your "Yes, it is." comment mean that it IS a report?

Then have you tried to have the report add the row numbers, as I suggested?

To be honest, though, perhaps I don't really understand what you're trying to do. Why would there be two entries for a student for the same day? Are you counting tardiness per class during the day?

You may need to show your data model, as well as the SQL for [Tardy Data Query]. Some sample data would be useful as well. Hmm, that suggests uploading your database, doesn't it? smile.gif

BTW, while not relevant to the problem you're trying to resolve, it's seldom, if ever, advisable to include special characters (which include blanks) in your object names in Access.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
RJD
post Sep 27 2019, 06:42 PM
Post#5


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


welcome2UA.gif

As Doug pointed out, this can be done easily in a report, using a control with =1 and a RunningSum Over Group.

However, if you want the count in a query, for whatever reason, this can be done if you have a unique record ID (such as an AutoNumber) as a field in [Tardy Data Query]. I will assume that this record ID is called simply RecordID. If you don't have such a primary key, you should add one in the appropriate underlying table.

RunningCount: DCount("*","[Tardy Data Query]","[Student ID] = '" & [Student ID] & "' And [RecordID]<=" & [RecordID])

We don't know what else you have in your query, or the underlying table(s), but this should be a start. And it assumes that [Student ID] is text.

You also showed a field called [Date]. This is not a good idea, since Date is a reserved word and can cause problems in some cases. Also it is generally helpful not to use spaces in object names, although you can work around that if you are careful to add the square brackets in all cases.

If you need further assistance with this, perhaps you could attach a small db with relevant objects and some non-sensitive data, zipped.

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
 
rmcafee
post Sep 27 2019, 06:48 PM
Post#6



Posts: 6
Joined: 26-September 19



Attached File  Tardy_Sample.zip ( 137.53K )Number of downloads: 1
No, the "yes" was in reply to your first question.
No, haven't tried it in a report. I may turn to that if this doesn't work out.

But....Yes, thanks, trying to count each tardiness (late to class) per day and there are 7 periods per day, so there may be many occurrences per day and over several days.

To your last statement- just trying to find something that works.

Here is the SQL, the last column was just experimental and doesn't mean anything
Database attached - the query I'm working with is the "New Tardy Data query"

SQL
SELECT [Tardy data Query].ID, [Last Name] & ", " & [First Name] AS [Student Name], [Tardy data Query].[Student ID], [Tardy data Query].Grade, [Tardy data Query].Date, [Tardy data Query].[Period Absence], [Tardy data Query].[Bell Period], [Bell Period] & " - " & [Date] AS [Date/Pd], DCount("[Student ID]","[Tardy Data Query]","[Student ID] = '" & [Student ID] & "'" & "And [Date]<=#" & [Date] & "#") AS RunningCount, DCount("[Student ID]","[Tardy Data Query]","[Student ID] = '" & [Student ID] & "'" & "And [Student Name]<='" & [Student Name] & "'") AS RunningTotal
FROM [Tardy data Query]
GROUP BY [Tardy data Query].ID, [Last Name] & ", " & [First Name], [Tardy data Query].[Student ID], [Tardy data Query].Grade, [Tardy data Query].Date, [Tardy data Query].[Period Absence], [Tardy data Query].[Bell Period], [Bell Period] & " - " & [Date]
ORDER BY [Last Name] & ", " & [First Name], [Tardy data Query].Date;
This post has been edited by rmcafee: Sep 27 2019, 06:53 PM
Go to the top of the page
 
RJD
post Sep 27 2019, 07:11 PM
Post#7


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


Hi again: Take a look at my revision to your db. See the NEW ... query revision for RunningCount. Is this what you are trying to do? It follows my suggestion in my previous post.

HTH
Joe
Attached File(s)
Attached File  Tardy_Sample_Rev1.zip ( 76.3K )Number of downloads: 1
 

--------------------
"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
 
rmcafee
post Sep 27 2019, 07:16 PM
Post#8



Posts: 6
Joined: 26-September 19



BOOM, that seems to be it.

Can you tell me what changes you made to make it work...was it to count all with the *
Or was there additional edits?

thanks no matter what!!
Go to the top of the page
 
RJD
post Sep 27 2019, 07:23 PM
Post#9


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


You are very welcome. Glad that works for you.

It was actually replacing the "[Date] <=#" ... etc. with "[ID] <=" ... etc. [ID] is unique within Student, whereas [Date] is not.

The "*" was there because that is all that is necessary in a DCount - an actual field is generally not necessary.

Continued success with your project.

Regards,
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
 
rmcafee
post Sep 27 2019, 07:28 PM
Post#10



Posts: 6
Joined: 26-September 19



Only other problem I have are the names with a ' in them, which is frequent at my school.
Those come up with an #error.

I think there is a way to eliminate these with additional " marks - any help here??
Just not sure where to put them?

thanks
Go to the top of the page
 
RJD
post Sep 27 2019, 07:39 PM
Post#11


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


Okay, I thought the last field was not important. But given that it is, it does not require the student name, just the student ID (which is unique and contains no ' characters).

DCount("*","[Tardy Data Query]","[Student ID] = '" & [Student ID] & "'") AS RunningTotal

See Rev2 attached.

There are ways to deal with the ' characters if needed, but I don't see any cases where that is necessary in what you posted. If you run into somewhere that it gives you problems, come back and we can walk you through that. Let's not get confused working with it somewhere where not necessary.

HTH
Joe
Attached File(s)
Attached File  Tardy_Sample_Rev2.zip ( 73.68K )Number of downloads: 10
 

--------------------
"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
 
Doug Steele
post Sep 27 2019, 08:01 PM
Post#12


UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)


To handle names with apostrophes in them, use

CODE
...[Student Name] = '" & Replace(StudentName, "'", "''") & "'"...

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
rmcafee
post Sep 28 2019, 12:36 PM
Post#13



Posts: 6
Joined: 26-September 19



Yes, good tip on the student id. This will work. I was actually looking at the wrong column.

....And thanks for the workaround for the apostrophes, even if I don't use it on this one, it will help me in the future.

Thanks for all the help!!!
This post has been edited by rmcafee: Sep 28 2019, 01:11 PM
Go to the top of the page
 
RJD
post Sep 28 2019, 01:45 PM
Post#14


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


thumbup.gif

--------------------
"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
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 02:51 PM