Can't find the answer - Can anybody help !! (1 Viewer)

[anthonyphillips]

Hi Guys

I currently have a database and the powers at be want me to enable a button that will count how many times in the database a particular month is used. So for example how many times did we visit a particular place in September/october etc.

Is there a way of doing this that i can't see, I have tried searching the forum but to no avail.

my thanks in advance

Anthony

 

[neileg]

Create a totals query set to Count, or use DCount().

 

[anthonyphillips]

Totals Query

Hi Neil

Many thanks for the reply, i wasn't aware that you could do that in access, and have got little experience with access. (First DB to be honest) but it was better than working of 24 different spreadsheets.

I know this is a newbie question but have you got any idiot material to show me how to create one of these?

My Thanks again

Anthony

 

[rsmonkey]

Look up query's and how to create them. Once you worked out that press the totals button looks like this: ∑
Then change the drop down box to count where you want on the field you want it to count.

 

[anthonyphillips]

Count function returning no data

Thanks for the help rsmonkey however when i do this is returns no data

This is what i have

Field = BC Visits
Table = All Work Plans
Total = Count
Sort = (empty)
Show = Tick
Criteria = Like " * September * "

When searching this manually there are 56 entries with September using the above qry it returns no results.

Where am i going wrong !!

Thanks in advance

 

[neileg]

It's because your criterion, " * September * " is text. Your dates will be date/time. Asccess is fussy about datatypes and won't match text with date/time. If you add a calculated field in your query and use Format() to show just the month, you can apply September as a criterion to that column.

 

[The_Doc_Man]

You need to keep in mind something I refer to as one of the "Old Programmer's Rules." Access won't tell you anything you didn't tell it first.

If you want time to be returned from a query, time has to be avalailable to BE returned. In other words, when you are designing the DB or upgrading its design in the face of new requirements, always remember you must CAPTURE what you want to display - or make it possible for Access to COMPUTE it. (To show a list of dates, you must capture a list of dates. To show a total or difference, you must teach Access the formula.)

Therefore, if you have a need to see X, assure that X is available IN THE NEEDED FORMAT. Or that X can be available in the needed format through a conversion function.

 

[GaryPanic]

you need an extra table ..
Sitevisit
this needs to have
id
countfield
date
sitevistid (FK)

and a bvutton
to put in the count field the nubmer 1 and a date -

now you can have a qry that looks at the date range
between x and y and the list a lot of 1's which you can then total
either an append qry or an add fuction will work
qry easier - .add - smarter