Pulling in data from another table

[dbarton]

Greetings, I have two tables; one has just variances entered, the other is the total number of events for the month (normal events + variances). The variances become the numerator, and the total events is the denominator.

The report does counts of variances based on location, type of variance, etc...
This is working just fine, but I also need to show on the report the number of variance/total events (%).

I am struggling with pulling the data for total events into the variance report.
The variances all have dates, the totals have start and end dates (monthly)

For the variances, my query asks for date range, but I want this same date range to be used for pulling in totals from the other table.

I am trying to use DSUM("monthlytotal", "tblTOTALS",Between "StartDate=input start date from report query" And "input end date from report query")

Should I setup a form that after the start/end dates are enter, the report is launched using these dates?

 

[vbaInet]

Create two fields in the query with the date range parameter and use those fields in the DSum() function.

For example if the start date parameter reads "[Enter Start Date]", add another column in your query like this "StartDateCriteria: [Enter Start Date]" and this will become a field called StartDateCriteria in the query. Don't worry Access won't ask you twice for the same criteria.

NB: Remember to add those parameters to the Parameters list... very important.

 

[dbarton]

This is late in coming, but I want to thank vbaInet for his suggestion and that it did ultimately lead me to solving my problem. To generate the report, I did set up a form with user input of date range, then used these dates to pull in the data I wanted. What was missing was the logic to pull in the data from the other table (number of total events/month) to use as the denominator - based on the date range. Here's what eventually worked.
=DSum("LABnum","tblTOTALS","[StartDate]>=[txtFDate] And [EndDate]<=[txtTDate]"). where LABnum is a monthly total. I then pull in this value and use it in another calculated field. The reports limitation is it is dependant to pulling by month, not by partial month.

 

[vbaInet]

Good job dbarton!

For completeness, I would advise that you use the full reference to the form:

=DSum("LABnum","tblTOTALS","[StartDate]>=[COLOR=Blue][Forms]![FormName]![/COLOR][txtFDate]  And [EndDate]<=[COLOR=Blue][Forms]![FormName]![/COLOR][txtTDate]")

With regards the limitation you mentioned, what do you mean by partial months? Give some examples. Is it Jan, Feb, Mar... etc?

 

[dbarton]

vbaInet, when data is entered in the tblTotals, it is a monthly totals, so for the month of June a single value of 100 may be entered. When they enter the to and from dates on the form, the limitation was they needed to enter the full month, 6/1/15 to 6/30/15. If they entered 6/1/15 to 6/15/15, it wouldn't make sense as you will always get a value for the full month. I also wanted to allow them to enter multiple months, so if they want to pull a report for April through June, they could do this. I indicated this on the form to use the first and last days of the month they want to report on.

I will add the full path for the form.

Thanks again for your help vbaInet!
Don

 

[vbaInet]

So basically you want the month totals to be based on the date ranges entered? I.e. if the user enters 6/1/2015 to 6/15/2015 you only want the totals between the 1st and the 15th of June?

If that's the case then the record source (i.e. table/query) of the DSum() function needs to be raw values and not totalled values. That way it can sum based on the range entered.