Solved Convert Null to zeroo

[nirmal]

Why no JOIN clauses? Without any output is a Cartesian association of records. Each record of every table will associate with each record of other tables. With 5 tables, this could result in a massive dataset that performs very slowly. Why are these counts not all in one query? Provide sample raw data. If you want to provide db for analysis, follow instructions at bottom of my post.

Sir after what you guided me, I have designed as per your guidelines a single query to count age and issues in the month and get zero for null values.
Sir now I am trying to get a progressive yearly number for the certain fields in query like progressive advanced age and progressive total customers, where the last month or months total is shown with the current months total.
Sir, and how to start renumbering from a certain date on monthly and yearly level in a append query. so for every year from example 1st April to 31st March we get a new serial number and similarly on monthly ground from 1st date of every month

Sir attaching a zipped copy of my sampleDb

 

[arnelgp]

see the progressive query and your report.

 

[nirmal]

see the progressive query and your report.

Sir the progressive values are reflecting as expected, but Sir, how to start renumbering from a certain date on monthly and yearly level in a append query. so for every year from example 1st April to 31st March we get a new serial number and similarly on monthly ground from 1st date of every month

 

[Pat Hartman]

Sequence numbers, when used, should be assigned when a record is added, not after the fact. Is this some kind of cleanup which will be done one time?

 

[arnelgp]

we get a new serial number and similarly on monthly ground from 1st date of every month

Show some expected result to fully understand your request.

 

[nirmal]

Sir sending the SampleDb with a CustomerVisit_tbl with few data of different date. I have labelled its Yearly and Monthly serial number incremental on year and monthwise manually. How to get the same as I add the rows to the table by use of a append query, so I need not do it maanually. So to get the monthly report I will use the CustomerVist_tbl as record source for the query

 

[arnelgp]

how do you compute the YearlySerialNumber?
it is but sequencial now?
should it be reset to 1 on 2022?

 

[nirmal]

Yes Yearly should reset to 1 on 1st april every year
and
Monthly to 1 on 1s of every month

 

[arnelgp]

i added code in Module1.
i create CustomerVisit_tbl Form, you test by adding some records there.

 

[nirmal]

Sir I am unable to implement the public function in my query. Please help
Sir I am using a dd/mm/yyyy Date format, so please correct the same
attaching the Db