Query required for adding formula in a field value2 = Value1 + 1 and so on....

[Rupeshns03]

I seek help from the experts in the forum. I have to add 1 to the consecutive values in a field as shown below, as shown in the table below. If I make any value in column- COUNT zero (manually) as highlighted, the count reset to 1,2,3,4,5,6..... Kindly suggest how to write the query in Access or VBA code to make this happen.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[arnelgp]

is this the "Real" scenario?
where would you use it?

 

[MajP]

Makes Zero sense.

 

[theDBguy]

Hi. Welcome to AWF!

Not sure a query alone can do it.

 

[arnelgp]

With the help of autonumber (without Gap) see Query1.

 

[plog]

You're posting in an Access forum, but your issue reeks of Excel. From an Access standpoint this is just a tweaked running totals query:

Content retirement

The content that you're looking for is now retired.

docs.microsoft.com

For a running totals query you need a field to order your data by. Normally that's a date, but you could use [Cell Ref] if that is in fact a field in your data. I fear you've genericized your data so much that it's not possible for us to help you specifically. Hopefully the link I posted helps, or you can post back your actually table.

 

[Rupeshns03]

is this the "Real" scenario?
where would you use it?

I am using this tracker for the count of Incident Free Days on Construction Project which goes beyond 1 year in duration. In the event of any incident the incident free day's count becomes zero and count is reset to 1,2,3,... for the upcoming days. I want to automate in using access tool where I have used other queries to automate a comprehensive report.

 

[arnelgp]

sooo... you're counting the Zero Incident/Accident and Reset when an incident happens.
safety first!
for you to be able to do that you need daily recording of how many incident/accident.
are you a Safety officer (OSHA)?

 

[Rupeshns03]

With the help of autonumber (without Gap) see Query1.

Hi Arnelgp, Thanks for the suggestions, however it didn't work. Pls find attached database with the table with the content on which I have to apply the query. The password "STAR9999!" if required.

 

[Rupeshns03]

sooo... you're counting the Zero Incident/Accident and Reset when an incident happens.
safety first!
for you to be able to do that you need daily recording of how many incident/accident.
are you a Safety officer (OSHA)?

Nope, I am project controls engineer (Civil Engineer by Qualification). I am trying to automate the reports to the extent possible. I am require to reset the count manually, whenever any incident happens.

 

[theDBguy]

Nope, I am project controls engineer (Civil Engineer by Qualification). I am trying to automate the reports to the extent possible. I am require to reset the count manually, whenever any incident happens.

Just wondering if a simple DateDiff() would do the same job...

 

[Rupeshns03]

Hi plog,
Many thanks for your reply. I am looking forward to add this part of my Access tool which have several other simple queries to automate the report. Pls find attached db for your reference with a table. am using this tracker for the count of Incident Free Days on Construction Project which goes beyond 1 year in duration. In the event of any incident, the incident free day's count becomes zero and count is reset to 1,2,3,... for the upcoming days. The password is <STAR9999!>, if required to open the db.

 

[CJ_London]

so what prompts a reset? a value entered in the appropriate column? And how is the data presented in reports? 289+ rows and counting? or a summary of some sort?

your data implies you have and will only ever have one construction project which does not seem very likely unless you are talking about HS2 or crossrail for example. Also table is not normalised which makes any solution more complex.

You should only need to know the start date of the project and the dates and types of any incident - which since your table has none, means just one record.

 

[arnelgp]

see Query1.
if you change any thing to 0 on your table, click the "Refresh" button on the Ribbon
if your Query1 is also being displayed.

 

[plog]

That table is not the way to achieve this. I think the DBguy was correct with his DateDiff solution.

The table you need isn't a table of all days. The table you need is an Incidents table where you simply put in the incidents that occured:

tblIncidents
incident_ID, autonumber, primary key
incident_Date, date, date of incident
incident_Injury, text, description of injury
incident_Description, text, description of incident
incident_Enivromental, text description of environmental incident

In that table you only put in a record if an incident occured, you don't populate it with every day. Then , when you want to find out how long between incidents you use a subquery to find the prior incident for every record (based on incident_Date) and use DateDiff to determine that.

 

[Rupeshns03]

see Query1.
if you change any thing to 0 on your table, click the "Refresh" button on the Ribbon
if your Query1 is also being displayed.

Amazing.... Cheers arnelgp. You made my day.
It works the way I desired. I will have the result of query copied using a macro to the table. Thanks again for the solutions suggested.

 

[Rupeshns03]

That table is not the way to achieve this. I think the DBguy was correct with his DateDiff solution.

The table you need isn't a table of all days. The table you need is an Incidents table where you simply put in the incidents that occured:

tblIncidents
incident_ID, autonumber, primary key
incident_Date, date, date of incident
incident_Injury, text, description of injury
incident_Description, text, description of incident
incident_Enivromental, text description of environmental incident

In that table you only put in a record if an incident occured, you don't populate it with every day. Then , when you want to find out how long between incidents you use a subquery to find the prior incident for every record (based on incident_Date) and use DateDiff to determine that.

Thanks for the suggestions plog.... Much appreciated.
let me try working on it.

 

[Rupeshns03]

so what prompts a reset? a value entered in the appropriate column? And how is the data presented in reports? 289+ rows and counting? or a summary of some sort?

your data implies you have and will only ever have one construction project which does not seem very likely unless you are talking about HS2 or crossrail for example. Also table is not normalised which makes any solution more complex.

You should only need to know the start date of the project and the dates and types of any incident - which since your table has none, means just one record.

Thanks for your comments Cj_London. As I mentioned, the count of incident free days, injury free days is reset, manually, only when there is an incident, of course it does not happen each day and thats what we strive for. There are celebrations on project sites when we have 100 consecutive incident free days. Each project will have, regardless of its nature, it own timelines and HSE records. My job as a project controls is to set the system to monitor and track each KPI, for all ongoing projects.

 

[Rupeshns03]

This is a typical first page of daily progress report starting with HSE Summary. The lower section shows the count of incident free days. The background of this report is the tracker in Access which I wanted to automate. Unless it is not set zero, the count of incident free days goes on till the end of the project and is fed in the table in the database with the dates till end of the project. In the following example, I set the count of Incident Free days to zero and the column recounts 1,2,3,4.....for consecutive days. The reseting of count is required to be done manually by copy, edit and repasting the data in table. The end users on project site do not have access to the background tables / form for this particular tracker and I reset the count from head office, on the occurrence of any incident. I wanted to create a button on the form with the help of queries and macros, which are linked to date picker of that particular day on the home page (see the second image attached). This is the simple tool I developed for the daily progress report.