Date count with crietria

syedadnan

Access Lover
Local time
Today, 18:59
Joined
Mar 27, 2013
Messages
315
Regards,

Looking to have a count function which calculate data in sense like if records found on 1-jan-2014 the it give answer as 1, same as records for 2-jan-2014 it should return 2 and so on .. in short the criteria must look and give same number for same dates starting from 1
 
are you looking for 1 from feb 1 as well? That case Day(yourdate) will help you

Otherwize look at the Datepart or Format functions.
 
are you looking for 1 from feb 1 as well? That case Day(yourdate) will help you

Otherwize look at the Datepart or Format functions.


Thanks for the reply,

Where and how to use these formula ?
 
Where and how are you trying to do your count?
Where and how does your database work?
Where and how do you want your day?
Where and how is it that banana's are curved?
Where and how does the burn in the Fridge when you close the door?
Where and how can you ask such general questions?
 
Where and how are you trying to do your count?
Where and how does your database work?
Where and how do you want your day?
Where and how is it that banana's are curved?
Where and how does the burn in the Fridge when you close the door?
Where and how can you ask such general questions?

Surprised .. and sorry if that question hurt you..

Sorry again
 
you didnt hurt me, just that I am missing a lot of information because your question(s) is very general... even the original question is not very precise...

And I still dont know ...
Where and how are you trying to do your count?
Where and how does your database work? ie why do you need this? What do you need it for?What is your target?
Where and how do you want your day?
 
you didnt hurt me, just that I am missing a lot of information because your question(s) is very general... even the original question is not very precise...

And I still dont know ...
Where and how are you trying to do your count?
Where and how does your database work? ie why do you need this? What do you need it for?What is your target?
Where and how do you want your day?

Thanks for a Coooool briefing...

Ok.. so i will explain you as what i need,

Having a requisition table, then PO table, "Auto" (auto number) is a field in requisition table, field auto is used as lookup (FK) in table PO means AUTO number is number for a specific doc.

Now i need a count of Auto in report .. and i need this count based on different dates in PO table with grndate field having same auto number like;

if 4570 having item A on 1st Jan 2014
if 4570 having item B on 1st Jan 2014
if 4570 having item C on 1st Jan 2014

........ then the count should be 1

same as;

if 4570 having item A on 2nd Jan 2014
if 4570 having item B on 2nd Jan 2014

........ then the count should be 2

if 5001 having item A on 1st Jan 2014
if 5001 having item B on 1st Jan 2014
if 5001 having item C on 1st Jan 2014

........ then the count should be 1

same as;

if 5001 having item A on 2nd Jan 2014
if 5001 having item B on 2nd Jan 2014

........ then the count should be 2

this is the maximum that i suppose to the best of my explanation, hope will work for you to make this easy for my...
 
Well I asume you will know how to make queries, so make a query that does something along the lines of:
Code:
Select Auto, GRNDate, count(Item) as Items from yourtable(s) group by Auto, GRNDate

Save this query to your database as qryCountSomething (give it a proper name)
Now make a new query and add above query as input
Code:
select auto, Count(GRNDate) as CountOfGRNDate, sum(Items) as TotalItems from qryCountSomething

CountOfGRNDate should be what you are after.
 
Well I asume you will know how to make queries, so make a query that does something along the lines of:
Code:
Select Auto, GRNDate, count(Item) as Items from yourtable(s) group by Auto, GRNDate

Save this query to your database as qryCountSomething (give it a proper name)
Now make a new query and add above query as input
Code:
select auto, Count(GRNDate) as CountOfGRNDate, sum(Items) as TotalItems from qryCountSomething

CountOfGRNDate should be what you are after.


Many thanks for the valuable time shared, just the requirement is changed now if you kindly look my this post as this is required finally..

http://www.access-programmers.co.uk/forums/showthread.php?t=259855
 

Users who are viewing this thread

Back
Top Bottom