Access Calculating the underlying query??

[pablavo]

Hi all

I know the heading makes out it's a Query problem, however, it's definitely a Report problem.

I have a report and the underlying record source, which is a query, spans 4 tables. I'm concered with two of the tables...

If I look at the query I can see the one side duplicated to accomodate for the many side. So, if there is one Amount on the one side (£20,000) and it's sent in two installements (£10,000 each) then the £20,000 is duplicated twice in the query.

I can hide duplicates etc, in the report, however, when I use
=Sum([Amount]) to add the total I get £40,000 even though the duplicates are hidden.

Here's the main probelm. I can't calculate the installements instead (which would work) for reasons to do with future dates. Also, I can't make the Many side records a subreport (which would also work) because I'm using dates from that side as criteria against the report.

Does anyone know of a way I can calculate the "Amount" without calculating the duplicates?

Thankyou for any help in advance!

 

[Rich]

Why won't a sub report work?

 

[pablavo]

Thanks for getting back to me Rich!

The reason (I believe) a subreport wouldn't work is that I have to take dates from the many side ( the installments table) for criteria against the report. These are Schedule dates that the money would be disbursed.

If I create a subreport with the Installment table included, and take this table out of the main query. I wouldn't be able to use these dates as criteria because now they are in the subreport...

In less there's a way of using the subreport tables as criteria for the main parent report!?

I can't calculate the installments on the report because at this point there would only be dates to signify when the money would be scheduled to be disbursed. and the amount that has been approved would be the only field to calculate ( which is on the one side) It's the process.

I'm sure there's another way around it...

 

[pablavo]

Thanks for getting back to me Rich!

The reason (I believe) a subreport wouldn't work is that I have to take dates from the many side ( the installments table) for criteria against the report. These are Schedule dates that the money would be disbursed.

If I create a subreport with the Installment table included, and take this table out of the main query. I wouldn't be able to use these dates as criteria because now they are in the subreport...

In less there's a way of using the subreport tables as criteria for the main parent report!?

I can't calculate the installments on the report because at this point there would only be dates to signify when the money would be scheduled to be disbursed. and the amount that has been approved would be the only field to calculate ( which is on the one side) It's the process.

I'm sure there's another way around it...

 

[Rich]

So you would want to filter the subreport by a user inputted date?

 

[pablavo]

I don't understand what you mean Rich? Have I not explained it correctly?