creating searchable reports or querys with totals...or is this don

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

Guest

Is it possible to create a report (report because I must create sum fields)
with search capabilities? Or is this something forms are used for?

Specifically....
I need to create a report that takes info from 3 different tables...I can do
that with a union query.
I also need to make a total field...I can create a report from the union
query that sums.
My problem: Because the report is large I need to be able to search a
field...like a query, or .xls.

So is there a way to do this?
 
You cannot search a report.

Could you create another query that uses the UNION query as an input
"table", and performs the grouping and aggregation you need?
 
Not sure I know what you mean or how to do it.

I've created my union that contains (key) phone number, each line is a
different charge for that line (line charges, call charges, 411 charges)...I
want to see a sum after all the records for each number. I could make a query
for each number but we have 932 distinct numbers.

What I want is to created a list we can search by phone number (to easily
find where on the form/report/whatever this number is listed) which would
look something like...

555-0001 line charge $20.00
555-0001 voice mail $4.00
555-0001 long distance Chicago $0.31
555-0001 long distance LA $1.72
Total: $26.03

555-0002 line charge $20.00

It does not have to be editable but it must be searchable so we can find a
number fast.
 
1. Create a new query, using your existing UNION query as the source
"table".

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the PhoneNumber field into the grid.
Accept Group By in the Total row under this field.

4. Drag the ChargeType field into the grid.
Accept Group By in the Total row.

5. Drag the Amount field into the grid.
In the Total row under this field, choose Sum.

6. Save the query.

7. Create a new form based on this saved query.
Use Continuous View (tabular layout).
In the Form Footer section (View menu), include a text box with Control
Source:
=Sum([SumOfAmount])
Save the form.

8. Create a new unbound form. Add a text box named (say) txtPhone.

9. Add the form saved at step 7 as a subform.
Set these properties for the subform control:
Link Master Fields txtPhone
Link Child Fields PhoneNumber

10. Save the form.

When you open the form, it will load without any records in the subform. Now
when you enter a number into the unbound text box, the summary of charges
will list in the subform, with the total in the subform footer.

You now have a searchable, summarised interface with the total.
 
Back
Top