Counting Repeated Dates as 1

[lazydave]

That's all it told me to do.
Is there supposed to be more? It doesn't do anything.

The other query's I created I used the wizard for.

What am I supposed to know that I don't, about creating a Query manually?

 

[vbaInet]

I see where you're coming from but the query needs some data to work with.

List out the fields in your table, the data type and one sample record. For example:
ID (Number) - 20
AttendanceDate (Date/Time) - 16/07/2014

 

[lazydave]

OK

Now have a Query that shows a list of Entity Numbers(ID) and only single dates.

What's the next step?

Will have to get back tomorrow it's 1.30am here time for bed

 

[vbaInet]

May I see the SQL (copy and paste it here), just to be sure. Not that I don't trust you of course

 

[lazydave]

SELECT DISTINCT [Participation List].[Entity Number], [Participation List].Date
FROM [Participation List];

Ok What next?

 

[vbaInet]

Perhaps you didn't realise that Date is a reserved keyword in Access. Here's a comprehensive list:

http://allenbrowne.com/AppIssueBadWord.html

Are you grouping by any fields in your report? What fields does your report contain?

 

[lazydave]

Entity Number and Date are the only 2 Fields.


Only 2641 words that have special meaning, how could I have not known that

Does this mean I need to rename the Date Field to something else? 'Cause I don't know what else to call it.

I'm starting to think that a sub form on the front page might be better than for each entity through the report.

It's getting the distinctions for some of the counts that may be hard

Is there a way to attach the DB to here for you to see maybe that will help?

 

[vbaInet]

Only 2641 words that have special meaning, how could I have not known that

I had great expectations of you Dave

So are you going to group by Entity Number? If you're not and all you want is a unique count of all the dates in the table then the next step is quick.

 

[Uncle Gizmo]

Only 2641 words that have special meaning, how could I have not known that

No way you could have, and I don't know them either.

Heres the thing you don't need to remember them, I don't, I use a little trick.

Most of the words you naturally want to use in your database will be reserved words, you already found one:- "Date" the trick is NEVER USE a real word!

So instead of "Date" use myDate or thisDate, or any combination that takes your fancy.

There is a "Naming convention called the Leszynski naming convention which should give you some ideas for prefixes (or a sufix) that will help youn avoid using reseved words.

 

[lazydave]

Grouped by Entity Number

I'm hoping to be able to get totals for each field for each entity eventually.

I can see it being a query for each field some will have count restrictions like the Date we are working with now, some will be restricted by date and another field.

Is that a light at the end of the tunnel? I hope it doesn't have wheels and carriages.

 

[Uncle Gizmo]

Heres a better display of the naming convention....

http://access.mvps.org/access/general/gen0012.htm

 

[lazydave]

No way you could have, and I don't know them either.

Heres the thing you don't need to remember them, I don't, I use a little trick.

Most of the words you naturally want to use in your database will be reserved words, you already found one:- "Date" the trick is NEVER USE a real word!

So instead of "Date" use myDate or thisDate, or any combination that takes your fancy.

There is a "Naming convention called the Leszynski naming convention which should give you some ideas for prefixes (or a sufix) that will help youn avoid using reseved words.

are you taking the piss

 

[lazydave]

Heres a better display of the naming convention....

http://access.mvps.org/access/general/gen0012.htm

You do know that date doesn't appear in that list anywhere

I don't have this problem with WYSIWYG Web Builder

 

[lazydave]

ok so joking aside eventdate is what I should call Date Field

 

[vbaInet]

Grouped by Entity Number

I'm hoping to be able to get totals for each field for each entity eventually.

I can see it being a query for each field some will have count restrictions like the Date we are working with now, some will be restricted by date and another field.

Is that a light at the end of the tunnel? I hope it doesn't have wheels and carriages.

Yes you're seeing a bright light... don't run towards it

The easiest route is to use a DCount() function to get the count of records per [Entity Number] (by the way which still has a space in its name and the same goes to Participant List - again it's not the end of the world as long as you keep enclosing it in square brackets).

The DCount will look something like this:

=DCount("*", "[[COLOR="Blue"]qryDistinctPartList[/COLOR]]", "[Entity Number]=" & [Entity Number])

Substitute the name of the query you created a few posts ago in the highlighted bit. The whole code will go in the Control Source of your textbox.

 

[lazydave]

Yes you're seeing a bright light... don't run towards it

The easiest route is to use a DCount() function to get the count of records per [Entity Number] (by the way which still has a space in its name and the same goes to Participant List - again it's not the end of the world as long as you keep enclosing it in square brackets).

The DCount will look something like this:

=DCount("*", "[[COLOR=blue]qryDistinctPartList[/COLOR]]", "[Entity Number]=" & [Entity Number])

Substitute the name of the query you created a few posts ago in the highlighted bit. The whole code will go in the Control Source of your textbox.

It worked where else can I employ this code, can I use it to pull data from other queries for other fields

As I have other fields that need sorted totals

 

[vbaInet]

Anywhere else you feel the need. Read up the link I sent. There are also other functions you'll find useful on that site.

 

[lazydave]

Anywhere else you feel the need. Read up the link I sent. There are also other functions you'll find useful on that site.

Thanks for your help, I will be following up on that site, it's these sort of functions that are what makes me marvel at how complex a simple thing can be and a what looks like it should be complicated turns out to be simple.

I have just made the adjustments to the "Real" Database and it worked there too oh and I have edited out all the spaces in the field headings by adding_underscores and that worked too.

Now I am going to try and build a Summary Report which will have some of the fields sorted/filtered and counted.... wish me luck

Cheers
Lazy Dave

 

[vbaInet]

Underscore is fine too. Whichever floats your boat!

Good luck!

 

[Uncle Gizmo]

are you taking the piss

Why on earth would you think that?