Prev: I Can't Do This
Next: crosstab - multiple values required
From: Ann on 15 Apr 2010 09:52
Hello –

I have a form that has multiple fields on it…the fields are:

BillingAmount1
BillingAmount2
BillingAmount3
And so on…up to 7 Billing Amounts

BillingAmountPaid1
BillingAmountPaid2
BillingAmountPaid3
And so on…up to 7 Billing Amount Paid

BillingAmountDue1
BillingAmountDue2
BillingAmountDue3
And so on…up to 7 Billing Amount Due

I need to create a query that will only show me the Billing Amounts Due that
are greater than 0. Basically, this would be an Invoice report that would
only show me if there are any bills still outstanding.

Also, I have a calculated field in the form called “Remaining Balance” that
takes the total Billing Amount Due and subtracts the total Billing Amount
Paid and shows me the remaining balance. Another way to say what I would
like to see is if the Remaining Balance is greater than 0. I don't know how
to query off of the calculated field though.

I don't know how to create this query (without using SQL, since I don't know
it).

Please help!

Thanks in advance.

From: KARL DEWEY on 15 Apr 2010 11:21
The ONLY information you provided was on the form and its display text boxes.

No one can create a query without knowing that table and field names used to
feed data to the form.

--
Build a little, test a little.


"Ann" wrote:

> Hello –
>
> I have a form that has multiple fields on it…the fields are:
>
> BillingAmount1
> BillingAmount2
> BillingAmount3
> And so on…up to 7 Billing Amounts
>
> BillingAmountPaid1
> BillingAmountPaid2
> BillingAmountPaid3
> And so on…up to 7 Billing Amount Paid
>
> BillingAmountDue1
> BillingAmountDue2
> BillingAmountDue3
> And so on…up to 7 Billing Amount Due
>
> I need to create a query that will only show me the Billing Amounts Due that
> are greater than 0. Basically, this would be an Invoice report that would
> only show me if there are any bills still outstanding.
>
> Also, I have a calculated field in the form called “Remaining Balance” that
> takes the total Billing Amount Due and subtracts the total Billing Amount
> Paid and shows me the remaining balance. Another way to say what I would
> like to see is if the Remaining Balance is greater than 0. I don't know how
> to query off of the calculated field though.
>
> I don't know how to create this query (without using SQL, since I don't know
> it).
>
> Please help!
>
> Thanks in advance.
>
From: John W. Vinson on 15 Apr 2010 14:27
On Thu, 15 Apr 2010 06:52:01 -0700, Ann <Ann(a)discussions.microsoft.com> wrote:

>Hello �
>
>I have a form that has multiple fields on it�the fields are:
>
>BillingAmount1
>BillingAmount2
>BillingAmount3
>And so on�up to 7 Billing Amounts
>
>BillingAmountPaid1
>BillingAmountPaid2
>BillingAmountPaid3
>And so on�up to 7 Billing Amount Paid
>
>BillingAmountDue1
>BillingAmountDue2
>BillingAmountDue3
>And so on�up to 7 Billing Amount Due

A Form is NOTHING BUT A WINDOW.

It does not contain any data. It only shows you the data in your table; you
cannot base a query upon a Form, only upon a Table.

If (as I suspect) your form reflects the structure of your table, then your
table design is *wrong*, and it will be very difficult to get the result you
want. This might be a decent spreadsheet design, but if you have a one
(account) to many (bills) relationship, you should model it as a one to many
relationship between a table of Accounts and a table of Bills, similarly with
Payments. Would the Amount Due be calculated on the basis of bills minus
payments? If so, it should be calculated on the fly, not stored in any table
field at all.

Please explain your table structure.

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: I Can't Do This
Next: crosstab - multiple values required