By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,550 Members | 1,733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,550 IT Pros & Developers. It's quick & easy.

Wierd Query Question

P: n/a
Hi All, I've searched the usenet/web high and low and cant seem to
find any guidelines for the query i'm trying to write.

I have made a database that contains all my invoices. Invoices that
contain the status "DUE" are then displayed on a report.

My question is this.

I have about 50 companies in there now and Have my report set up to
break pages after each one.

For example, ACME Paperclips might look like this

ACME PAPER CLIPS TOTAL: $38.32

12/23/03 BILL 1 $30.00
01/03/04 BILL 2 $8.32

However, each week i have about 30 to 40 invoices that are being
displayed on one page each.

Is there a way to say "If ACME paperclips has more than one bill "DUE"
print it on a seperate page, if it doesnt, print it at the end in a
different report without pagebreaks named "OTHER""

Basically, i like to attach the sheets that come out of these reports
to the front of a group of bills. However, if i only have one invoice
each from say 30 companies, id just like to group those as an "other"
pile and have like a 2 page sheet summerizing those ones as opposed to
one cover sheet per invoice.

Any ideas? Anyone still awake? :-) Thanks!
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Am Tue, 13 Jul 2004 11:57:06 -0700 schrieb Erica:
Hi All, I've searched the usenet/web high and low and cant seem to
find any guidelines for the query i'm trying to write.

I have made a database that contains all my invoices. Invoices that
contain the status "DUE" are then displayed on a report.

My question is this.

I have about 50 companies in there now and Have my report set up to
break pages after each one.

For example, ACME Paperclips might look like this

ACME PAPER CLIPS TOTAL: $38.32

12/23/03 BILL 1 $30.00
01/03/04 BILL 2 $8.32

However, each week i have about 30 to 40 invoices that are being
displayed on one page each.

Is there a way to say "If ACME paperclips has more than one bill "DUE"
print it on a seperate page, if it doesnt, print it at the end in a
different report without pagebreaks named "OTHER""


Hello,

how about this:

add a field to the recordsource of your report maybe "duestate"

duestate:iif(myTestdate<=invoiceduedate;"Due";"emp ty string"))

and build a group with page break on "duestate".

With subsequent entries you will be able to add other states.
HTH
Karpi
<fluctuat nec mergitur>
Nov 13 '05 #2

P: n/a
sh****@easterncontractors.com (Erica) wrote in
news:b7**************************@posting.google.c om:
Hi All, I've searched the usenet/web high and low and cant
seem to find any guidelines for the query i'm trying to write.

I have made a database that contains all my invoices. Invoices
that contain the status "DUE" are then displayed on a report.

My question is this.

I have about 50 companies in there now and Have my report set
up to break pages after each one.

For example, ACME Paperclips might look like this

ACME PAPER CLIPS TOTAL: $38.32

12/23/03 BILL 1 $30.00
01/03/04 BILL 2 $8.32

However, each week i have about 30 to 40 invoices that are
being displayed on one page each.

Is there a way to say "If ACME paperclips has more than one
bill "DUE" print it on a seperate page, if it doesnt, print it
at the end in a different report without pagebreaks named
"OTHER""

Basically, i like to attach the sheets that come out of these
reports to the front of a group of bills. However, if i only
have one invoice each from say 30 companies, id just like to
group those as an "other" pile and have like a 2 page sheet
summerizing those ones as opposed to one cover sheet per
invoice.

Any ideas? Anyone still awake? :-) Thanks!


first you need to make a totals query. that counts the number of
due invoices for each company. I'll use the name TotalsQuery

Now create a query to use in the report that joins the query
created above to your invoices table. Create a calculated field
and paste in the expression

iif([totalsquery].[countofDueInvoices] =1, 1,0)

Sort on this field.

Modify the Company field in the query.
iif([TotalsQuery].[countofDueInvoices] =1,"Other",[Company])

That will do what you want.

Bob Quintal

..

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.