I have a table with the following information:
OrderID RequestRecieved PackageSent Department
The data in the fields is as follows:
OrderID: A numeric id number
RequestReceived: Date
PackageSent: Date
Department: One or two character initial (There are 7 departments in total)
I am attempting to create a report that looks roughly like the following:
D1Rec D1Snt D2Rec D2Snt etc. Yesterday CountYD1Rec CountYD1Snt CountYD2Rec CountYD2Snt etc. Today CountTD1Rec CountTD1Snt CountTD2Rec CountTD2Snt etc. Growth CTD1Rec-CTD2Rec. etc.
So, D1Rec is Department 1 Received and CountYD1Rec is a count of all of department 1's orders received up through yesterday and CountTD1Rec is a count of all department 1's orders received up through today and so on and so forth. And the growth line just subtracts line 2 from line 1.
In order to complete this, I created a blank report, put in the labels and for the counts wrote the following: =SUM( IIf( [Department] = D1 And [RequestReceived] < Date(),1,0)) and then put the same thing in, changing it to "PackageSent" instead of "RequestReceived" for the Snt items and changing the Department initials as appropriate. However, when I actually run the report it gives me an error for the upper left value and all the others are just blank in the first two lines and 0s in the growth line (except the first Growth cell which gives me another error). Is there a better or more efficient way to do this? I tried to create a query that looked right that I could base the report on, but I couldn't seem to get all the details I wanted in the right order. (It is very important that the Department names be horizontal, for instance.) If not, any thoughts on what might be causing the error?
Thanks! :banghead:
OrderID RequestRecieved PackageSent Department
The data in the fields is as follows:
OrderID: A numeric id number
RequestReceived: Date
PackageSent: Date
Department: One or two character initial (There are 7 departments in total)
I am attempting to create a report that looks roughly like the following:
D1Rec D1Snt D2Rec D2Snt etc. Yesterday CountYD1Rec CountYD1Snt CountYD2Rec CountYD2Snt etc. Today CountTD1Rec CountTD1Snt CountTD2Rec CountTD2Snt etc. Growth CTD1Rec-CTD2Rec. etc.
So, D1Rec is Department 1 Received and CountYD1Rec is a count of all of department 1's orders received up through yesterday and CountTD1Rec is a count of all department 1's orders received up through today and so on and so forth. And the growth line just subtracts line 2 from line 1.
In order to complete this, I created a blank report, put in the labels and for the counts wrote the following: =SUM( IIf( [Department] = D1 And [RequestReceived] < Date(),1,0)) and then put the same thing in, changing it to "PackageSent" instead of "RequestReceived" for the Snt items and changing the Department initials as appropriate. However, when I actually run the report it gives me an error for the upper left value and all the others are just blank in the first two lines and 0s in the growth line (except the first Growth cell which gives me another error). Is there a better or more efficient way to do this? I tried to create a query that looked right that I could base the report on, but I couldn't seem to get all the details I wanted in the right order. (It is very important that the Department names be horizontal, for instance.) If not, any thoughts on what might be causing the error?
Thanks! :banghead:
