looping code

[Roncc]

I have a very unusual situation and wonder if someone might be able to give me some guidance. I am the volunteer "IT" guy for our local food bank and many years ago I wrote an Access program to allow them to track their clientele. The data is primarily used to support grant requests obtaining data for the number of people who use the food bank and how often they visit. I placed a field in the main table called "count" and each time a client applies for assistance the counter increases by 1. It is reset on January 1 each new year. If a client comes in in January, the counter field will have a 1 in it. If the client comes in again in February, the counter will have a 2 in it etc. Each client has a client Id number (unique to the client) and a record number (unique to the visit). For example, the first time the client comes in they are given a ClientId number say, 1000 and the RecordId number for that visit is also 1000. The second time the client comes in his ClientId number will be still be 1000 but the recordId number will be the next number 0f the series of numbers that records every clients visit and may be, for example 1356. The third time the client comes in the ClientId number will still be 1000 but the recordId number will be the next number of the series of numbers that records every clients visit, i.e. 1637. The counter field will have a 1 for the first visit, a 2 for the second visit, and a 3 for the third visit.
Unfortunately, I did some revisions on the program last year and all the count number fields were set to 1 so I now have no way of telling how many visits each client made. My thoughts are these: since each ClientId number is unique and each record number is linked to a specific client is there a way to write a query or visual basic code that could place the correct count for subsequent visits by a single client and have it loop back to do the same to the next client. For example, write a select query for a specific ClientId then if the count has a 1 in it for the first record, the count field for a second visit by the same client would become 2, for the third visit 3, etc. until there are no more records in the database for that client. Then, the query would reset to the next ClientId number and loop back on the process and set the count field to 2, for the second visit, 3 for the 3rd visit, etc.

I know this is fairly complicated, but I thought someone might enjoy solving the programmatic puzzle.
The name of the table is tblmain, the name of the fields are clientid, recordid, and count. There is also a date field which will allow me to remove all records that are not in the calendar year I am focusing on which, in this case is 2024.

[June7]

Calculating a sequential value and updating records is a common topic.

Unfortunately, not simple in Access. A query can do the calc but performance would likely be slow with large dataset. Updating existing records can get complicated. Solution usually requires either DCount() domain aggregate function in SQL or VBA procedure looping through recordset.

An option is to use RunningSum property of textbox on report to calculate this sequential value for display when needed.

Start with review of https://accessexperts.com/blog/2015/...ing-in-access/

[Roncc]

Thanks for taking the time to read my post and reply. I didn't think it was very doable but thought I'd ask.

[June7]

Didn't mean to be negative, just realistic. It is very doable, just not simply. It's been solved many times. Did you review the link?

[madpiet]

Why not have a design like

Client(ClientID, Name...)--(1,M)--Visit(VisitID,ClientID, VisitDate, ...)

Then you can filter by dates/date ranges etc and just do a simple count.

[Gustav]

You don't have to store the count. It is much simpler to calculate on the fly in a query, and it can even run very fast.

See my article https://www.experts-exchange.com/art...U3/YU%2BQm8%3D
Go to paragraph 2. Row Numbers

It also has a demo for download.

You will need the option for specifying a GroupId - the client id if you filter on the year, or year & client id if you list several years.
The query syntax is quite simple, basically:

SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *FROM SomeTableWHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

[jojowhite]

i don't think you will ever need a counter field.
all you need is a query to count each clients visit each month/year:

SELECT ClientID, Month(VisitDate) As Mo, Year(VisitDate) As Yr, Count(ClientID) As Counter 
FROM YourTableName GROUP BY ClientID, Month(VisitDate), Year(VisitDate)

To get the LastCounter for, say this Month:

=Val(DLookup("Counter","AboveQueryName","ClientID = " & [ClientIDVariable] & " And Mo = " & Month(Date()) & " And Yr = " & Year(Date())) & "")

To get the Next Counter:

=Val(DLookup("Counter","AboveQueryName","ClientID = " & [ClientIDVariable] & " And Mo = " & Month(Date()) & " And Yr = " & Year(Date())) & "") + 1

[/code]

[Roncc]

I did review the link but didn't understand most of it. I'm not a professional programmer and can do simple things but am easily overwhelmed by complex code.

[NoellaG]

If simple things get complicated it's mostly a sign of bad data structure; maybe post a picture of your relationships. It's possible we could give some tips to simplify things and get the result with a simple query without any programming.

[Roncc]

There are no relationships used in the table. It is quite simple in structure. Each client visit is recorded as one record in the table. Fields include ClientId, RecordId, ClientLastName, ClientfirstName, Date of Birth, size of household, date of visit, count, etc. On each visit a new record is created in the table.

[Welshgasman]

Well there you go.
Should at least be
Customer table
Visit table

[June7]

Roncc, your narrative has example of client visiting only once a month. Will that always be the case or could they visit more often?

If this counter field is just to indicate how many times a client visits in a year, that can easily be calculated with aggregate query or with DCount() or with report design. There is no need to save incrementing value into table. True, showing this annual count on a form is tricky but why would you need to? Forms are intended for data entry/edit, reports for data analysis/manipulation.

Whether saving to table or just calculating when needed, need to be prepared to learn some new tricks. First one you can try is DCount() domain aggregate function. DCount can be used in a SELECT or UPDATE (or just in textbox on form). So if you really must save into table, try:

UPDATE MyTablename SET MyCountField = DCount("*", "MyTablename", "ClientID=" & [ClientID] & " AND Year(DateVisit)=" & Year([DateVisit] & " AND DateVisit<=#" & [DateVisit] & "#")

Domain aggregate function can cause slow performance in query with large dataset so if that doesn't go well, next thing to try is VBA.

Many of us here are not professional programmers but we learn. As in my case, I learned on the job to accomplish a task that was thrown at me. Although, I was lucky enough my employer was willing to pay for a contractor to help me get started. After a couple of months I quit calling on the contractor for guidance.

[June7]

jojowhite, I don't think your query is incrementing the count for each month. To show yearly count by client, need to exclude month from the grouping. Then DLookup() would just pull count by client and year criteria.

[NoellaG]

There are no relationships used in the table. It is quite simple in structure. Each client visit is recorded as one record in the table. Fields include ClientId, RecordId, ClientLastName, ClientfirstName, Date of Birth, size of household, date of visit, count, etc. On each visit a new record is created in the table.

If all data are in one table and repeated in each new line, you're using Access as you would work in Excel. If you like working like this it will be easier if you do it in Excel and start a new file every year. No need to put the counters on 0 anymore.