count a range

[mnjohn]

hi experts.

i have an attendance table. its fields are ( auto id number, names, and 1,2,3,4,5 up to 30 that represents 1 month). now in each field that name 1,2,3,4 etc contains "p" as presents, "d/o" as day off.

now what i want to make a query is to count total presents of each employ. so please tell me how to count a range of fields or coloumns same as like an excel.

thanks

id	name	1	2	3	4	5	total
1	robert	p	p	p	d/o	p	=count(p)
2	john	p	p	p	d/o	p	4
3	peter	p	p	p	d/o	p	4

[ArviLaanemets]

=Iif(1="p",1,0)+Iif(2="p",1,0)+...+Iif(1="p",1,0)+ Iif(30="p",1,0)

But what about months with 31 days?

[CJ_London]

your table is not set up to do this easily. data should be stored vertically not horizontally

e.g.

id...name....daynum..status
1....Robert..1..........p
1....Robert..2..........p
1....Robert..3..........p
1....Robert..4..........d/o
1....Robert..5..........p
2....John.....1..........p
2....John.....1..........p
2....John.....1..........p

further the name should be in a separate table so it does not need to appear in this one.

and again you can use the absence of a record to indicate something - for example not having a record for d/o means they are having a day off - or perhaps no record means it is a weekend/bank holiday

Also field names should not be numeric

but to answer your question, you will need a long formula

=iif([1]="p",1,0)+iif([2]="p",1,0)+iif([3]="p",1,0)....

done the proper way it would be much simpler

[mnjohn]

dear experts.
first of all thanks for replying me.
now coming to my question. i want to make an attendence sheet of my employies....... now suppose i have 10 employies and i want to keep their daily attandence reocords. and at the end i want to calculate total monthly attendence.

so below is a table that what i want to ask.


attandence sheet of month january

sr #	Names	monday	tuesday	wednesday	thursday	friday	saturday	sunday	monday	total
1	john	p	p	p	p	p	p	d/o	p	=count(p)
2	robert	p	p	p	p	p	p	d/o	p	7
3	kevin	p	p	p	p	p	p	d/o	p	7
4	alex	p	p	p	p	p	p	d/o	p	7

so now in excel i can easily get result at the last coloum that is = countif(A1:H1,"p")

but in access it is not so easy....... so tell me how i design a query to get totals of presents

and same rules i can apply to others months if they have 29 days or 31..... like feburary , march , april ,,,,, etc.......

[Homegrownandy]

how are your employees "signing in" how does this data get entered? It may be easier to track absence.

[mnjohn]

sir.
i get attendance record mannually....i mean in a4 papers i have records of presents......... there is no signing system....... in access i just want to keep records and make their salaries according to their presnets.

[Homegrownandy]

could you not have a table that contains employee ID and the date of absence?

all you would need then is a count for each person and subtract that from the total working days.

Seems like a much easier approach to me.

[mnjohn]

thanks to all experts.....

i think my question is not clear thats why every one asking me different things that actually i not want to ask...........

leave every thing and start from scrach.........


actually i want to calculate or sum or count a range of coloumns horizontally same like in excell by putting the formula at the end coloumns that is suppose h1.

for instance in excel i have 8 coloumn. start a1 to h1.....

now if i want to calculate or sum or count any thing between a1 to g1 i put a formula in h1... that is ( =sum(a1:g1 ) or ( =countif(a1:g1,"p" )....
this give to sum of values between coloumn a1 to g1 or count word p between a1 to g1 and giving me the result in h1.......

this same thing i want in access.... either in table or query or form or report....... so please tell me how i do that..

thanks

[CJ_London]

we understand what you are asking and two solutions have been provided in posts #2 and #3.

And to repeat, Access is not a bigger Excel, it is completely different.

repeating the question and using larger font + bold does not take you any further forward.

Explain why the solutions provided do not do what you want.

[mnjohn]

mr ajax
i know access is not like excel. and also i read post # 2 and 3.... but this method is very complicated and long....
so i want an easy solution that how i do a horizontal calculation that is i have done in excel..
so as i repeated many times that i want to sum or count records not fields.... i mean horizontally...

so tell me the easyiest solution how i do this.......... and also i mention my posts that it is no problem what method be used , i mean this can be get by adding more table or making queris ,, or form or reports ........... anything...

thanks

and one thing i want to say that i am totally new in access and also new in this form........ so i don't know that how to post.............. in my post i put bolds properties is just to understand words easily... nothing else..........

anyway i am sorry if something is mistakenly i post.........


thanks

[mnjohn]

as everyone know software are build to make things easier ....

i am an excel expert ..... and in excel there are many method to make data sheet. some are very complex and some are very easy.... but the result are very same.... so why we go complexity...........

so same thing i want in access too.... that is i want to make count monthly attendance based on presents , absents , dayoff, etc.


and in my first post i put simple question.. and some members give me its answer too... but as i told that these are very complex. so i ask again that i want to simple method for doing such calculation.............

thanks to all

[CJ_London]

but as i told that these are very complex.

That is what happens when your data is not structured correctly. The answers provided are the only way to do it for your table design.

so i ask again that i want to simple method for doing such calculation.............

There is no simple method for your table design.

Excel stores data (not very efficiently) and typically presents it horizontally in one sheet. Access (any database) stores data vertically and is not concerned with presentation. For presentation, Access uses forms and reports.

if your data was structured as I suggesting in post #3 then you can run a simple query.

But since you are fixated on the excel way, the solutions provided are the only solutions available to you. Unless you are prepared to rethink how you do things I recommend you stick with Excel or if you want to go with Access, google 'normalisation' and 'database relationships' and understand the basic concepts and principles involved, then apply to your data.

[mnjohn]

thanks to all

[June7]

As already stated, the data structure is not 'normalized' and the calcs you want will not be simple to produce. Another approach to dealing with this structure is to build a UNION query to emulate 'normalized' data. Then use that query in another query to do the aggregate calcs.

Time and Attendance database is one of the more difficult to construct because normalized data structure will not replicate the structure of many timesheet paper forms which often have dates run horizontally.

Your example data is lacking date values.

Review https://www.accessforums.net/showthread.php?t=29025 Post #17 has the OP's database you can download and examine to get ideas.

[mnjohn]

mr. june7
thanks so much...... its good reply with a reference link....
and also you state that attendence is most difficult part to create in access....
so i decide, first i clear my basic concept about database and practice it and hope become familiar with it soon.......


now from start what should i do ????

i have very good concept about how to create tables , forms, and report....... but i have a little knowledge about query......

i have only problem in how and why to join table to each other and what kind of query i should use to create a database...............




i am working in such a company that have daily sales and weekly purchase......... i mean we sales daily our product and give orders to other company once every week.....
now first i want to create a data entry form of purchase order . that when i have a purchasing invoice i want to select a company and enter date and invoice number....
and in below i want to build a continuous subform that i enter all orders at once..........and it updates with my remaining quantity.

for example

as shown in an image file that i attached....



thanks and hope you suggest me what i should do .....