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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EdLeeYoung - 01 Dec 2005 20:43 GMT

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?

Reply to this Message

Allen Browne - 02 Dec 2005 01:54 GMT

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?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Is it possible to create a report (report because I must create sum
> fields)
[quoted text clipped - 10 lines]
>
> So is there a way to do this?

Reply to this Message

EdLeeYoung - 05 Dec 2005 17:23 GMT

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.

> You cannot search a report.
>
[quoted text clipped - 15 lines]
> >
> > So is there a way to do this?

Reply to this Message

Allen Browne - 06 Dec 2005 03:43 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Not sure I know what you mean or how to do it.
>
[quoted text clipped - 39 lines]
>> > My problem: Because the report is large I need to be able to search a
>> > field...like a query, or .xls.

Reply to this Message