Obtain data from Query for Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.)

NChris said:
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.

Tina said:
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.
Click to expand...
Click to expand...
 
Back
Top