=sum(IIf fields in a date range query

[weeblebiker]

=sum(IIf fields in a date range query for a report

I need to build a report on a repair datebase i made
the report need to be able to sum the fields in a date range
can I do a date range query and sum the entries in the selected fields in the same query? or do I need to do the date range, then do another query on the first query to sum the fields? for the final report?

maybe this is a query form question?

 

[PNGBill]

Have you tried either of your two options? Test query would have taken less time then typing the question.

Just test a couple of fields to see if it works.

You should be able to do this on one query.

Date range is the date field with the criteria set or requested. All selected records will result for the Dates on and between the two dates supplied.

Then use the Totals button to group/sum the records. Use Count or another "Total" for fields not needed in the Group Or Sum.

Sometimes it may be cleaner to use a new query for the sum where the first query has many fields but then you probably need a third query to bring that result back into your data.

If you have problems, provide some fields names and values and or post your sql's

 

[PNGBill]

This sql will count and sum as requested for Dates Between Start Date and End Date

SELECT Count(TblOrders.OrderNumber) AS CountOfOrderNumber, TblOrders.OrderDate, Count(TblOrderDetail.OrderLineID) AS CountOfOrderLineID, Count(TblOrderDetail.ProductID) AS CountOfProductID, Sum(TblOrderDetail.ProductQty) AS SumOfProductQty
FROM TblOrders INNER JOIN TblOrderDetail ON TblOrders.OrderNumber = TblOrderDetail.OrderID
GROUP BY TblOrders.OrderDate
HAVING (((TblOrders.OrderDate) Between [Enter Start date] And [Enter End date]));

 

[PNGBill]

This query will give you a Sum of the first Query's Sum.

SELECT Sum(QryOrdersDates1.SumOfProductQty) AS SumOfSumOfProductQty
FROM QryOrdersDates1;

 

[weeblebiker]

Thanks for the reply
I'm suming the number of instances each of three words apears from a drop down box in the form dumping to the table field and also summing a few checkboxes ("yes" results) int the records in the same table within a date range.
I have the date range query working with all the chosen fields showing in the date range.

I thought I needed to use an IIF expression to convert the words to 1's than sum the "1"'s.
I don't know how access 2003 works really, so a query field can manipulate data from another column in the same query?

or am I making this way harder and the date range is the default first operation so I don't need to have the field entries listed in the query?

so I can just put a field with the expression "=Sum(IIf([Oil leak]="Bellows",1,0))" (where "oil leak" is the name of the field from the table and "Bellows" is one of the choices in that field) along side the date range field in the query and I'll get a number only from within the date range and not the whole table?

excuse my ignorance, I'm trying to take care of that

 

[PNGBill]

Don't be afraid to test your if on one or two fields and see if it returns the result you want (and is correct!)

You could do a Date Query first so all other queries just us e the selected data from Query One instead of using all the data in your table /s when most may not be needed. Your choice on this issue.

Select Queries can Manipulate Data from fields in the query or fields on tables used by the query - This is very common.
You make a new Field - ItemSaleValue: (([QtySold]*[UnitPrice])*[Discount])/100 should give you a new field in your query that has the discounted Value for the Line on your Sales Invoice.

 

[weeblebiker]

got it working now and am building everything.
I did a date range query then a second sum query on the ranged query and reported off the second query.

I do have another Question: how do I include the dates from the date range boxes in the report?

 

[PNGBill]

How to include Input Data in your Report?

On your Form where you call the queries to run, add one or two Unbound Text Box Controls, Date Formatted. Name Them "StartDate" and "EndDate" & the labels StartDateLabel etc.

You can now edit your Query, replace the Date Criteria with a link to the above Form Controls. Why I mentioned One or Two dates is that if the period is always the same eg, 6 months then one date will work and the other query field is a calculated field using DateAdd.

Now, You enter the date/s in the form and the query uses this date. Your Report can also use this date provided the Form is not Closed. It may not be on your screen when the report is displayed but it is still open.
Add Two Unbound Text Box Controls to your Report Header and link one or two to your Form, again the option is if the period is fixed you can use a Calculation for the 2nd Date.

 

[PNGBill]

This, in the Control Source of your Report Date Text Box Control will get data from your Form. Replace FormName and FormTextBoxControlName with your names.

=[Forms]![FrmName]![FormTextBoxControlName]

 

[Brianwarnock]

got it working now and am building everything.
I did a date range query then a second sum query on the ranged query and reported off the second query.

I can think of no reason why this could not be done in 1 query.

Brian

 

[PNGBill]

More experience...

Brian, mental block here. Can a query get the Date Criteria from a Form with DLookup ?

 

[Brianwarnock]

Why would you want to do it any other way than that which you have described, but I think no , is a Form a Domain? I don't know the answer as I would never consider it so I'll have to pass.

He should have a Form with the 1 or 2 date controls and a command button that runs the Report based on the Query.

Brian

 

[PNGBill]

I have explained the Form and Report link but not the Form and Query Link.

Can DLookup in the Query, use the Unbound Text Box Controls in the Form?

 

[Brianwarnock]

I have explained the Form and Report link but not the Form and Query Link.

Can DLookup in the Query, use the Unbound Text Box Controls in the Form?

Sorry I don't understand this post, its getting late for an old guy like me.

I have agreed with you approach using Forms!formname!controlname and suggested that as I would never use Dlookup as you descibe I cannot say if it works. I might test it out of curiosity but not tonight.

Brian

 

[PNGBill]

To get the Dates on the Form to be the Criteria in the Query we can also make them the Filter. See this Code

Private Sub ReportDate_AfterUpdate()

    Me.Filter = "TRNACTDTE = #" & Format(Me.ReportDate, "mm/dd/yyyy") & "#"
    
    Me.FilterOn = True
    
    Me.Requery
    
    Me.OrderBy = "TRNACTDTE DESC, LDPK"
    Me.OrderByOn = True
    
End Sub

Call for help if you are not sure about "Events" but basically what happens above is when the form is opened all data is displayed and this is the AfterUpdate Event for the Unbound Text Box Control ReportDate.
TRNACTDTE is the Bound Text Box Control on the Form.

There is also and OrderBy part to this.

 

[Brianwarnock]

Bill I think that you are in danger of overwhelming the biker with options, I think he is relatively new. The simple approach is all he needs.

Brian

 

[PNGBill]

Thanks Brian Early morning here

 

[PNGBill]

Agreed, Simple way I have used is to make a new table, call it TblDateRange.

Two fields, StartDate and EndDate.

Back to your Form. Add a SubForm from TblDateRange with the two fields above as the Bound TextBox Controls.

Your Query can now use DLookup as the Date Criteria.

Between (DLookup("[StartDate]", "TblDateRange")) and (DLookup("[EndDate]", "TblDateRange"))

Now, you enter the dates in the form and Click the Command Button on the form that will run the query/s - just call the last query. and you will have your data.

The Report part, as mentioned, will still use the two Form Controls.

I will try and attach a sample database that shows how all this can come together.

 

[weeblebiker]

Thanks for the replies, Yes I have just enough knowledge to be frustrated at this point.finally following the rational behind how access works but iits still a black box to me.
I need to read through the responses a bit more.
Thanks again!

 

[PNGBill]

See attached Database and open FrmDateRange Use dates entered - you can input your own if you wish, and click the button.