Access 2007 Count and SUM in the background! (1 Viewer)

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
hey guys,
I have this little problem that I need some help on I tried to use 2 subforms and queries ... but I end up with 4 subforms and about 8 queries … and still didn’t get it to work
Here is the scenario:
I have this Form(image) and a Table called “SO Master “ fields (SO, Qty, Reviewed date, received date……)


I need to run a code or SQL in the background to count how many SO (reviewed and received) and the sum of their Qty for 2 given dates (yesterday and today)
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
* Use a query to generate the results
* Bind the query to your form
* Set the Control Sources of the textboxes on the form to the fields from the query

For the query:

* Include only the two date fields in the query
* In the Criteria row under Reviewed Date and Received Date put this:
Code:
Date() Or Date() - 1
and untick the Show box under these two fields
* Right-click any of the fields and select Totals
* Using SO-Reviewed-Yesterday and Qty-Received-Today as examples, you will do something like this in a new column in the query:
Code:
SOReviewedYesterday: Count(IIF([[COLOR=Red]Reviewed Date[/COLOR]] = Date - 1, 1, Null))
QtyRecievedToday: Sum(IIF([[COLOR=Red]Recieved Date[/COLOR]] = Date, [COLOR=Red]Qty[/COLOR], Null))
 

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
* Use a query to generate the results
* Bind the query to your form
* Set the Control Sources of the textboxes on the form to the fields from the query

For the query:

* Include only the two date fields in the query
* In the Criteria row under Reviewed Date and Received Date put this:
Code:
Date() Or Date() - 1
and untick the Show box under these two fields
* Right-click any of the fields and select Totals
* Using SO-Reviewed-Yesterday and Qty-Received-Today as examples, you will do something like this in a new column in the query:
Code:
SOReviewedYesterday: Count(IIF([[COLOR=red]Reviewed Date[/COLOR]] = Date - 1, 1, Null))
QtyRecievedToday: Sum(IIF([[COLOR=red]Recieved Date[/COLOR]] = Date, [COLOR=red]Qty[/COLOR], Null))


thanks man,
just to add ()():
Code:
SOReviewedYesterday: Count(IIF([[COLOR=red]Reviewed Date[/COLOR]] = [B][COLOR=blue]([/COLOR][/B]Date[B][COLOR=blue]()[/COLOR][/B] - 1[B][COLOR=blue])[/COLOR][/B], 1, Null))
QtyRecievedToday: Sum(IIF([[COLOR=red]Recieved Date[/COLOR]] = Date[B][COLOR=blue]()[/COLOR][/B], [COLOR=red]Qty[/COLOR], Null))

also the part
* Right-click any of the fields and select Totals
generates a Groupping error, but after removing the Totals option works fine ;)
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
Happy to hear!

also the part
* Right-click any of the fields and select Totals
generates a Groupping error, but after removing the Totals option works fine ;)
If you change Group By to Expression it will be fine ;)
 

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
and your suggestion to change to Expression works .. thanks

but it only works is Reviewed Date = Recieved Date !!
here is what i did to try to fill all the Text boxes up:
Code:
[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]SOReviewedYesterday: Count(IIf([Reviewed]=(Date()-1),[Sales_Order],Null))
QtyReviewedYesterday: Sum(IIf([Reviewed]=(Date()-1),[Qty],Null))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]SORecievedYesterday: Count(IIf([Recieved]=(Date()-1),[Sales_Order],Null))
QtyRecievedYesterday: Sum(IIf([Recieved]=(Date()-1),[Qty],Null))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]
SOReviewedtoday: Count(IIf([Reviewed]=Date(),[Sales_Order],Null))
QtyReviewedtoday: Sum(IIf([Reviewed]=Date(),[Qty],Null))
SORecievedToday: Count(IIf([Received]=Date(),[Sales_Order],Null))
QtyRecievedToday: Sum(IIf([Received]=Date(),[Qty],Null))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
I spelt Recieved wrongly :eek: It should have Received. So the amended code is:
Code:
SOReviewedYesterday: Count(IIf([Reviewed]=(Date()-1),[COLOR=Red]1[/COLOR],Null))
QtyReviewedYesterday: Sum(IIf([Reviewed]=(Date()-1),[Qty],Null))
SORec[COLOR=Red]ei[/COLOR]vedYesterday: Count(IIf([Rec[COLOR=Red]ei[/COLOR]ved]=(Date()-1),[COLOR=Red]1[/COLOR],Null))
QtyRec[COLOR=Red]ei[/COLOR]vedYesterday: Sum(IIf([Rec[COLOR=Red]ei[/COLOR]ved]=(Date()-1),[Qty],Null))

SOReviewedtoday: Count(IIf([Reviewed]=Date(),[COLOR=Red]1[/COLOR],Null))
QtyReviewedtoday: Sum(IIf([Reviewed]=Date(),[Qty],Null))
SORec[COLOR=Red]ei[/COLOR]vedToday: Count(IIf([Received]=Date(),[COLOR=Red]1[/COLOR],Null))
QtyRec[COLOR=Red]ei[/COLOR]vedToday: Sum(IIf([Received]=Date(),[Qty],Null))
All the bits I amended in red.
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
Show me a set of records and what you expect to get for each field. Also show me a screenshot of your query in design view.

By the way, are your Review Date and Received Date fields defined as Date/Time data types?
 

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
the Fields are Date/Time.

here is what i get from the Query:


here is what it should looks like:

 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
Attach your db with some records and I'll have a quick look.
 

vbaInet

AWF VIP
Local time
Today, 15:39
Joined
Jan 22, 2010
Messages
26,374
The calculations are accurate. See the attachment and add it up.

That is showing the records that have a Received Date and Reviewed Date that falls between Today and Yesterday.
 

Attachments

  • ReceivedReviewed.JPG
    ReceivedReviewed.JPG
    17 KB · Views: 66

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
I saw where are you coming from! But that’s not what the Form was for and that’s not the calculation I want :( because there are order that reviewed today but received 5 days later ...


 

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
here is what i did and got the correct result
change the date in the Query for Today: to :
Between Date() And (Date()+1)
 

Pelerin13

Registered User.
Local time
Today, 08:39
Joined
Mar 23, 2011
Messages
56
actualy this is the one that did the trick

Between (Date()-2) And (Date()+1)
 

Users who are viewing this thread

Top Bottom