Obtain data from Query for Report

[Guest]

Hello,

I have created a report that gives me budget information from my tables. I
also created a query (qry_equipment_amt) that displays records with Invoice
Amounts.

Is there a way to reference the query from the report? I would like the sum
of Invoice Amounts from the query I created.


I have tried this =Sum(invoiceAmt).[qry_equipment_amt].Query but it seems to
give me an error.

Any help would be appreciated.

 

[Guest]

You can use the DSUM function to pull from any table or query in your DB.
Here is an example: DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")

The first part "[Freight]" is the field you want to sum.

The second part "Orders" is the source of the data which, can be a table or
query.

The third part "[ShipCountry] = 'UK'" is the criteria you want to use if you
only want to calculate the sum of a subset of data in the query or table.

 

[Guest]

Hi thanks, That worked except it gives me total of all the invoices. In my
report I have data grouped by Partner_ID and the DSUM equation is calcualated
in the Partner_ID Footer.

In my other query(qry_equipment_amt) it displays records based on a
Partner_ID, so for ex:
Partner_ID Amount
2 5000
6 100000
2 500
6 300

So I would like the group in my report (eg:2) to match the partner_id in my
query(qry_equipment_amt) and only add up the 2's for that group. So the
respose should be $5500.00 for partner # 2.

Instead each partner displays the sum of all the Invoice Amounts $105800.

This is what I have but it just continues to give me the sum of all the
invoice amounts.

DSUM("[InvoiceAmt], "qry_equipment_amt", "[Partner_ID = Partner_ID]")

(partner id in my report query should match the partner Id of the other
query I cerated qry_equipment_amt.)

You can use the DSUM function to pull from any table or query in your DB.
Here is an example: DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'")

The first part "[Freight]" is the field you want to sum.

The second part "Orders" is the source of the data which, can be a table or
query.

The third part "[ShipCountry] = 'UK'" is the criteria you want to use if you
only want to calculate the sum of a subset of data in the query or table.

Hello,

I have created a report that gives me budget information from my tables. I
also created a query (qry_equipment_amt) that displays records with Invoice
Amounts.

Is there a way to reference the query from the report? I would like the sum
of Invoice Amounts from the query I created.


I have tried this =Sum(invoiceAmt).[qry_equipment_amt].Query but it seems to
give me an error.

Any help would be appreciated.