Counting Repeated Dates as 1 (1 Viewer)

[lazydave]

G'day,
I have an activity report which has multiple entries for the same date by club members. I have managed to build the database:
2 Tables
2 Queries
2 Forms
1 Report

Which list attendance's and activities by Member and Date and Prints each one starting on a fresh page......

What I wish to do is count each Club Members total days attendance.

I have tried using the Totals button but I don't know enough about expressions to get it to count multiple dates as 1.

Everywhere I have looked can tell me how to count between dates, around dates, workdays in a year etc. etc. etc. but none give me multiple dates the same counted as 1 :banghead: :banghead: :banghead:

There are a couple of other totals that I'll need help with but this is a start hopefully

Cheers
Lazy Dave

 

[pr2-eugin]

Hello lazydave, Welcome to AWF

What exactly are you after? I am a bit unsure. If you only want to get the attendance from the table. Just use.

SELECT tableName.memberName, Count(tableName.attendanceDate) As CountOfAttendance
FROM tableName
GROUP BY tableName.memberName;

If this is not what you want, please give some example !

 

[lazydave]

Can't Post Pics/links yet

12/2/2014
12/2/2014
13/3/2014 this is what I get in my report when I use Totals Button
13/3/2014
14/4/1014
5

12/2/2014
12/2/2014
13/3/2014 This is what I need to get in my report
13/3/2014
14/4/1014
3

It looks pretty simple but I have tried everything and can't work it out or find info to do it. And code is not a language I speak, I only just found out today where button is for code.

Cheers
Lazy Dave

 

[pr2-eugin]

How about?

SELECT tableName.attendanceDate, Count(tableName.attendanceDate) As CountOfAttendance
FROM tableName
GROUP BY tableName.attendanceDate;

 

[vbaInet]

2 Tables
2 Queries
2 Forms

Everything in 2s

I'm sure those tables can be broken up further

@pr2-eurgin: Paul, I think he wants to count distinct dates.

 

[pr2-eugin]

@pr2-eurgin: Paul, I think he wants to count distinct dates.

LOL ! I just read what I answered. Thanks for jumping in and correcting it. Think my mind is allover the place right now.

 

[lazydave]

Thanks Paul, But

I don't know what to do with the code or how to make it active.

=Count([Date])
this is what is in the Control Source line of the Property Sheet for the "totals box"

Can't that have a definition or filter put in it to make it count duplicated dates as one?

Thanks
Dave

 

[vbaInet]

It looks like Paul is busy, so I'll give you some pointers:

You will need to create a query that returns DISTINCT dates per group. From this point you have two options:

* Use a DCount() function in the report that uses the distinct query

Or

* Use a subreport that query as it's source and Counts the dates per group. Then link the subreport (using the Link Master/Child Fields properties) to the main report via the group ID.

 

[essaytee]

How about?

SELECT tableName.attendanceDate, Count(tableName.attendanceDate) As CountOfAttendance
FROM tableName
GROUP BY tableName.attendanceDate;

Hi Dave,

Copy the above SQL code (posted earlier) and substitute with your table and field names, paste it into the SQL view of the query design. You'll see that the dates will only be displayed/printed once for each date. That is, for your table of 5 records, this query will produce only 3 records.

You will also be able to flick back to the normal query view and see how it looks.

Steve.

 

[vbaInet]

Hi Dave,

Copy the above SQL code (posted earlier) and substitute with your table and field names, paste it into the SQL view of the query design. You'll see that the dates will only be displayed/printed once for each date. That is, for your table of 5 records, this query will produce only 3 records.

You will also be able to flick back to the normal query view and see how it looks.

Steve.

As already explained Steve, that doesn't give a unique Count of records. It counts the duplicates and displays unique records. Two different things.

 

[lazydave]

I don't want to cull the records they aren't repeats.
I just want to count the individual days each member attends.
Members may sign in for multiple activities on the same date that's why I cant just count the total number of records.

 

[vbaInet]

I don't want to cull the records they aren't repeats.
I just want to count the individual days each member attends.
Members may sign in for multiple activities on the same date that's why I cant just count the total number of records.

Refer to post #8 Dave.

 

[lazydave]

Refer to post #8 Dave.

Thanks Everyone but :banghead: I have no training, I don't do this for a living, I left school in 1978 at 15, I am conversant with using modern technology but not building it.

My latest personal achievement has been to build 2 Tables, 2 Forms, 2 Queries and a Report in Access prior to that it was an Upright Drum Smoker with a PiD Temperature Controller.

To do this I did not have to learn which end of the ruler to use, which end of the drill cuts, which direction the drill should spin, how to set a welder up, how to weld, how to put a disc in a grinder or how to write code for the PiD Controller. This BBQ will run for 24hrs at a stable temp at 107Deg C on charcoal and heat beads and the meat comes out great. Once I got the components together it took me 10 hours to build the Smoker and 4 hours to build the control system.

I have now been working to get an understanding of this one question for over 8 hours and I still can't get it.

I understand that sometimes people come to these forums to pick your brains and take shortcuts without doing the hard yards and learning what you know, I understand the "Give a man a fish and he eats today, Teach him to fish and he eats forever" principle.

Answering this question will probably not be the end of my questions but it will never be this question again, maybe similar but never the same.

Please help me, I don't get Parent/Child relationships, I don't know what needs to go in DCount brackets, I don't know where DCount goes, I don't know correct syntax. I can drag and drop/cut and paste but I have to know where from and where to and what to rename.

Oh and I found out today that having spaces in your field headings is bad, why do they let you put headings in with spaces? can't it be like putting a username in for a forum, it wont accept spaces.

What I need is this:

Members Attendance Report
Date
12/2/2014
12/2/2014 Detail
3/7/2014
2/8/2014
4 Entity Footer

Property Sheet
Selection Type: Text Box
AccessTotalsDate
Data Tab
Control Source
=count([Date]) but I want it to ignore duplicates or count duplicates as 1
the answer is 3 not 4

Please don't take offence with what I have written it's just you don't know how little I know about this, how long I have been trying to get this Database off the ground to reduce a 50 hour once a year task into a 1 hour 12 times a year task.

Thanks
Dave

 

[vbaInet]

You don't need to give a long explanation Dave. We're help to help so if there's something you don't understand just ask.

Going back to my post I mentioned that you need to first create a query that will get distinct values. So create a query that will get rid of the duplicate values using the keyword DISTINCT. Here's a link that should get you started:
http://www.techonthenet.com/access/queries/unique_values.php

Once you've got that working we'll get to the other part.

Oh and I found out today that having spaces in your field headings is bad, why do they let you put headings in with spaces? can't it be like putting a username in for a forum, it wont accept spaces.

It's not bad but it's just not good practice. If you have a space in a field, let's call it "Field 1", and I wanted to refer to that field name in a query, I'll have to enclose it in square brackets [Field 1] otherwise Access will think the field name ends at 'd' which will eventually cause a syntax error. However if it was called "Field1" (without spaces) I don't need to enclose it in square brackets.

 

[lazydave]

Yeah just a measure of frustration, I do really appreciate people's assistance it just gets hard when the answers have a level of complexity beyond me.

I'll be giving that query a crack when I get home

 

[vbaInet]

Ok. We'll wait to hear back.

 

[lazydave]

OK Created Query:
Attendance Days Query
Unique Values set to Yes

Next?

 

[vbaInet]

Can I see the SQL of the query?

 

[lazydave]

 

[vbaInet]

Have you checked if that query actually works Dave?
If you read the link it explains where and how to use DISTINCT.