Solved Report using data from two queries (1 Viewer)

[inq80]

Hi,

Could someone please help point me in the right direction for this? I've tried looking online but I cannot find a solution (must be searching the wrong terms).

In my head this should be simple, but i'm clearly not doing something right.

I've got two reports set up from individual queries. One shows the sales of a product and the other shows the claims on a product.

Each report has a total for each product on it, and it'd be really handy to get the total from the claims report to show on the sales report.

I've tried using a formula which references the query data rather than the report, but i just get an error?

Can you not display data from two queries on one report?

As an example the formula on the sales report for one of the products is:

=Sum(IIf([SalesQuery]![Product]="Aggregated Fleet",[SalesQuery]![OriginalNet],0))

and the formula on the claims report is:

=Sum(IIf([ClaimsQuery]![Product]="Aggregated Fleet",[ClaimsQuery]![excessValue],0))

But work fine on their own reports, but the claims data just won't pull through on the sales report.

What am i missing? assuming this is an MS Access thing i just aint aware of?

Edit:

The query for the claims data has a start and end date parameter set for it in a form. This is apparently relevant

Thanks

 

[theDBguy]

Hi. Have you tried using DSum()?

 

[inq80]

Hi. Have you tried using DSum()?

I've not but just tried and cant be getting it right as it still errors:

=DSum("ExcessValue","ClaimsDataQuery","Product = Agreggated Fleet")

thanks for the suggestion though.

 

[theDBguy]

I've not but just tried and cant be getting it right as it still errors:

=DSum("ExcessValue","ClaimsDataQuery","Product = Agreggated Fleet")

thanks for the suggestion though.

Try:

=DSum("ExcessValue","ClaimsDataQuery","Product = " & [Agreggated Fleet])

 

[inq80]

Try:

=DSum("ExcessValue","ClaimsDataQuery","Product = " & [Agreggated Fleet])

tried it and the "enter Parameter value" box popped up for Aggregated Fleet

I changed to it "Aggregated Fleet" and just get error.

I'm still at a lost as to why Access is finding it so difficult to reference a value off a query it quick easily finds on another report?

 

[inq80]

update:

It's definitely the reference to the criteria it doesn't like.

Just tried without and it showed the total:

=DSum("ExcessValue","ClaimsDataQuery", )

Just need to figure out how to get the parameter to work now

 

[theDBguy]

update:

It's definitely the reference to the criteria it doesn't like.

Just tried without and it showed the total:

=DSum("ExcessValue","ClaimsDataQuery", )

Just need to figure out how to get the parameter to work now

We'll get it eventually. Try this:

=DSum("ExcessValue","ClaimsDataQuery","Product = 'Agreggated Fleet'")

 

[inq80]

thanks - tried and it goes blank?

I've also tried:

=DSum("ExcessValue","ClaimsDataQuery","Product"='Agreggated Fleet') - goes blank

=DSum("ExcessValue","ClaimsDataQuery",[Product] ="Agreggated Fleet") - returns error

 

[inq80]

does parameters effect it?

The query it's pulling the data from is created from a form which sets a startDate and endDate.

I really want the query originally to be filterable by dates and products but i struggled to get it to work with more than one parameter.

 

[inq80]

Also, just so i learn...

can anyone say why this didn't work?

=Sum(IIf([ClaimsQuery]![Product]="Aggregated Fleet",[ClaimsQuery]![excessValue],0))

 

[theDBguy]

does parameters effect it?

The query it's pulling the data from is created from a form which sets a startDate and endDate.

I really want the query originally to be filterable by dates and products but i struggled to get it to work with more than one parameter.

If the query has parameters, then yes, DSum() probably can't see it.

 

[theDBguy]

Also, just so i learn...

can anyone say why this didn't work?

=Sum(IIf([ClaimsQuery]![Product]="Aggregated Fleet",[ClaimsQuery]![excessValue],0))

It doesn't work because you can't refer to queries or tables like that.

 

[inq80]

If the query has parameters, then yes, DSum() probably can't see it.

If i take out the parameters can I add them to the code instead?

The claims data i need isn't just for a particular product, it has to be between two particular dates as well

 

[gemma-the-husky]

can you (left) join the sales query to the claims query. Then your report will show all sales, and claims where appropriate.
Now the sales with no claims might cause nulls to be shown in the claims column - and the total of anything with a null will be null. So that's the issue - get the nulls to show as zero, rather than null.

or have two reports. One for sales, and one for just sales WITH claims.

 

[inq80]

can you (left) join the sales query to the claims query. Then your report will show all sales, and claims where appropriate.
Now the sales with no claims might cause nulls to be shown in the claims column - and the total of anything with a null will be null. So that's the issue - get the nulls to show as zero, rather than null.

or have two reports. One for sales, and one for just sales WITH claims.

Thanks.

I'll have a go and see what happens

 

[theDBguy]

If i take out the parameters can I add them to the code instead?

The claims data i need isn't just for a particular product, it has to be between two particular dates as well

I'd say give it a try and let us know what happens.

 

[inq80]

Managed to get it working by changing the query .

Altered the joins so all the required Data showed me on one query and just used that to popular the report.

 

[theDBguy]

Managed to get it working by changing the query .

Altered the joins so all the required Data showed me on one query and just used that to popular the report.

Glad to hear you got it sorted out. Good luck with your project.