Update Query Counter

[birdDBheadache]

I am trying to write an update query in MS Access to update a count field in my database. I have a table that has records with multiple fields for every time a surveyor goes to a particular survey point. The surveyor enters information into the database to populate the fields including the point number([Survey_point]), the date([Date_surveyed]), time, etc. and usually goes to point multiple times in a year (but not always).

I would like to add a field called "Visit_Count" and update the value to indicate which visit it is for a given year. So, if the surveyor visits the point a single time in one year, Visit_Count = 1 for that record. But if the surveyor goes to the point 10 times, the first record for a particular sample point Visit_Count = 1, the second = 2, and so on. I have no problem querying the total visits for each point, but I can't figure how to determine that on date X [Date_surveyed](which is stored in record Y[ID]), it was the surveyor's ?th visit ([Survey_count]).

So to simplify, here is a reduced list fields in the table:
ID
Surveyor_ID
Survey_Point
Date_surveyed
Survey_Count 'need to update this field

I would be grateful if anyone could point me in the right direction! Thanks.

[Seth Schrock]

You shouldn't store this information in a field as you are supposed to store calculated values. Instead what you can do is create a query that has the VisitCount field added to it as a calculated field. That way, if ever a record gets changed from one surveyor to another, then you don't have to fix your visit counts. This is a bit of a complicated query, and I don't have a test database available at the moment, so there might be some errors, but just let me know what they are and we can work through them.

SELECT ID
, Surveyor_ID
, Survey_Point
, DateSurveyed
, DCount("*", "Survey_Table"
    , "Surveyor_ID=" & Surveyor_ID & 
    " And Survey_Point=" & Survey_Point & 
    " And Year(Date_Surveyed) =" & Year(Date_Surveyed) & 
    " And ID<" & ID) + 1 As Survey_Count
FROM Survey_Table

All you should have to do is change the name of the table in lines 5 and 10.

[birdDBheadache]

Ok. I think Dcount is the right direction I should go. I've attached a reallllly simplified screen shot version of what the data look like. If I can see an example implemented in this, I think I can apply it to the real thing.

Right now, the problem I am having is that it seems to be returning a full count of all the records for some reason, not just returning an incremented value.

[Seth Schrock]

I'm assuming that Name_ID is replacing Surveyor_ID from your OP? In that case, line #6 should be

    ,"Name_id='" & Name_id & "'" &

[birdDBheadache]

I am getting the error 'Missing operator in query or expression'

SELECT 
(ID
, Name_ID
, Survey_Point
, Survey_date
, DCount("*", "tbl_survey_details"
    , "Name_ID='" & Name_ID & "'" &
    " And Survey_Point=" & Survey_Point & 
    " And Year(Survey_date) =" & Year(Survey_date) & 
    " And ID<" & ID) As Survey_Count
FROM tbl_survey_details;

[Seth Schrock]

Oops. You need to remove the opening parenthesis on line 2. Thats what happens when you write air code :)

[birdDBheadache]

IT WORKED! But it starts counting at zero instead of 1. This isn't a huge deal, but is there a quick fix?

...i should have noticed the open parenthesis...i have been staring at the screen too long

[Seth Schrock]

Just put a +1 after the ending parenthesis of the DCount, before the As Suvey_Count.

[zmbd]

Actually it sounds like you need to normalize your database.[*]> Database Normalization and Table Structures.

For example a very simplistic normalization:
tbl_employee (has basic emloyee names)
tbl_sites (has basic site information)
tbl_visits (has the basic information for each visit)

tbl_visit would be related to tbl_employee and tbl_sites
[visit_pk] autonumber; primary key
[visit_date] date/time
[visit_fk_employee] 1:M relationship from tbl_employee
[visit_fk_sites] 1:M relationship from tbl_employee
[visit_testvalue1] (just an example field - this might be removed as follows)

The test results (i.e. [visit_testvalue1] ) might be broken out of the tbl_visits such that we have a table to hold the data and a table for each test type...
tbl_testresult
[testresult_pk] autonumber; primary key
[testresult_fk_visit] 1:M relationship from tbl_vist
[testresult_fk_test] 1:M relationship from tbl_test
[testresult_result]

Using table tbl_visit, you could agregate query on the emplyee number and count the sites, or agregate qeuery on sites and see the total number of visits, or any number of other possiblities all without haveing to add another field, counters, logic to keep the counters straight, etc...

Something to consider especially if you are still early in design.

[Seth Schrock]

However, an aggregate query wouldn't give the visit count for each record as required in the OP, second paragraph. It would only give the total number of visits. I'm all for normalizing though.

[birdDBheadache]

I think normalizing would solve many of my problems I am having, and many of my previous problems, but I don't have that much experience with using normalized dbases and my collaborators like to be able to look at all my tables as if they are looking at a spreadsheet.

Thanks for all your help.

[Seth Schrock]

That is the purpose of queries. In reality, users should never look directly at the tables. You arrange the data the way the user needs it by manipulating it with the query.

[zmbd]

<<Seth>> However, an aggregate query wouldn't give the visit count for each record as required in the OP, second paragraph. It would only give the total number of visits. I'm all for normalizing though.

@Seth:
Yep, I missed returning the record with the nth visit...
However, with that said... the normalized table tbl_visit, filtered by year, could be sorted on the date/time provided the time was entered... so if the employee visited site 1 on 1/1/2014 08:00; 1/1/2014 09:00; etc... then the records sorted by date and the nth record returned therefrom. Easy enough to do in VBA (^_^) Pure SQL... I haven't thought about that in awhile, sure there's a way... another will post, I'm thinking ranking query


@birdDBheadache

I think normalizing would solve many of my problems I am having, and many of my previous problems, but I don't have that much experience with using normalized dbases

Hence the link to the normalization in my last post (^_^)
If you'd like I can PM you a copy of some basic tutorials and concept links. One is a hands on type that once you work thru it you'll have a good understanding of the basics.

my collaborators like to be able to look at all my tables as if they are looking at a spreadsheet.

Seth is very right in that the normal end user shouldn't be messing with the tables!

Collaborators.... ( grimace )
And, so long as they don't want to edit data (and there are workarounds, messy, but possible) a cross-tab-query can be used to present the information in a flattened/standard spreadsheet format. Allen Browne's •Crosstab query techniques

[GKJR]

As far as a visit counter, one popular method in Access reports is to create an unbound text box with the property for running sum set to on and the total over group option selected. Also set the default value to 1. You could then show a report of visits that is grouped by location and the text box will increment by one for each consecutive visit. This doesn't work for forms or queries, but like the others posted about users not seeing your tables, I would go a step further and say your collaborators shouldn't see your info in a query either. Why not just put it in a report based on your query?

As far as normalization goes, do yourself a favor and get your tables set up properly now before you get any further. It is only going to get more difficult the longer you wait. Normalized tables are an absolute must for a relational database.

[birdDBheadache]

I am interested in learning more about using normalized databases, so I appreciate all the information. I have made them in the past, but it has been several years. I think I will see this current project to the end and then rethink the approach. All of the data are entered so, the database won't be getting any larger for another year.

Thanks again!