p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: resolvinq query


Message #1 by "Howard Stone" <ququmber@h...> on Sat, 1 Jun 2002 17:18:31
I am working on a db created by someone unknown.  The db has tblCustomer 
and tblInvoice in 1-Many relationship.  tblInvoice has a field called 
Amount_Paid that shows the amount owing for the invoice.

The table also has a field called Type that uses  two values, 1 for the 
balance and -2 for payments made towards the balance owed on the invoice.  
When a order is placed and the total is say $200.00 it is entered as one 
record and the value 1 is entered in the Type field showing that this 
owing. A $200.00 entry  is also entered in the Amount_Paid. An invoice 
number is entered in the InvoiceNo field.

If a payment of $200.00 is made towards the balance another record is 
created in the same table using the same invoice number, a value of -2 is 
entered in the Type field.  This -2 shows that it is a payment. A $200.00 
entry is entered in the Amount_Paid field.

The problem is that both entry for payment and invoice amount is using the
same field. 

I am running a query to get the Customer name,payments made and invoices 
that has balances.  I can get outstanding invoices by using 1 in the 
criteria field if the query.  I am unable to establish how much is paid 
there is no distinct field to show a payment being made. I cannot use the 
same field with different criterion twice.  I could run two separate 
queries but it important to have both on the same report.

How can I resolve this.  I hope I am clear in my explaination.

Thanks
Message #2 by "John Ruff" <papparuff@c...> on Sat, 1 Jun 2002 10:52:10 -0700
You are right to create two queries. One for Type is 1 (criteria
Type=1), call it qryAmountDue, and one for Type -2 (criteria=Type-2),
call it qryAmountPaid.  In both queries include the Invoice Number.  In
the qryAmountPaid query, create a derived field and call it
AmountDue:[Amount_Paid]. In the qryAmountPaid query, create a derived
field and call it AmountPaid:[Amount_Paid}.

Now create a third query (qryBalanceDue) and add qryAmountDue and
qryAmountPaid as the source.  Link them together on the Invoice number.
You can then do a Group by invoice number and sum the two fields,
AmountDue and AmountPaid.

Now base your report's recordsource on this qryBalanceDue.


John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity

xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 



-----Original Message-----
From: Howard Stone [mailto:ququmber@h...] 
Sent: Saturday, June 01, 2002 5:19 PM
To: Access
Subject: [access] resolvinq query


I am working on a db created by someone unknown.  The db has tblCustomer

and tblInvoice in 1-Many relationship.  tblInvoice has a field called 
Amount_Paid that shows the amount owing for the invoice.

The table also has a field called Type that uses  two values, 1 for the 
balance and -2 for payments made towards the balance owed on the
invoice.  
When a order is placed and the total is say $200.00 it is entered as one

record and the value 1 is entered in the Type field showing that this 
owing. A $200.00 entry  is also entered in the Amount_Paid. An invoice 
number is entered in the InvoiceNo field.

If a payment of $200.00 is made towards the balance another record is 
created in the same table using the same invoice number, a value of -2
is 
entered in the Type field.  This -2 shows that it is a payment. A
$200.00 
entry is entered in the Amount_Paid field.

The problem is that both entry for payment and invoice amount is using
the same field. 

I am running a query to get the Customer name,payments made and invoices

that has balances.  I can get outstanding invoices by using 1 in the 
criteria field if the query.  I am unable to establish how much is paid 
there is no distinct field to show a payment being made. I cannot use
the 
same field with different criterion twice.  I could run two separate 
queries but it important to have both on the same report.

How can I resolve this.  I hope I am clear in my explaination.

Thanks

Message #3 by "Wesley Kendrick" <wez.k@n...> on Sat, 1 Jun 2002 21:20:49 +0100
Hi Howard, you could run two queries and either insert the results of the
second query into the report using the DLookup function, or you could base a
report on it and insert the second report into the first report as a
sub-report.

Regards, wesley Kendrick

----- Original Message -----
From: "Howard Stone" <ququmber@h...>
To: "Access" <access@p...>
Sent: Saturday, June 01, 2002 5:18 PM
Subject: [access] resolvinq query


> I am working on a db created by someone unknown.  The db has tblCustomer
> and tblInvoice in 1-Many relationship.  tblInvoice has a field called
> Amount_Paid that shows the amount owing for the invoice.
>
> The table also has a field called Type that uses  two values, 1 for the
> balance and -2 for payments made towards the balance owed on the invoice.
> When a order is placed and the total is say $200.00 it is entered as one
> record and the value 1 is entered in the Type field showing that this
> owing. A $200.00 entry  is also entered in the Amount_Paid. An invoice
> number is entered in the InvoiceNo field.
>
> If a payment of $200.00 is made towards the balance another record is
> created in the same table using the same invoice number, a value of -2 is
> entered in the Type field.  This -2 shows that it is a payment. A $200.00
> entry is entered in the Amount_Paid field.
>
> The problem is that both entry for payment and invoice amount is using the
> same field.
>
> I am running a query to get the Customer name,payments made and invoices
> that has balances.  I can get outstanding invoices by using 1 in the
> criteria field if the query.  I am unable to establish how much is paid
> there is no distinct field to show a payment being made. I cannot use the
> same field with different criterion twice.  I could run two separate
> queries but it important to have both on the same report.
>
> How can I resolve this.  I hope I am clear in my explaination.
>
> Thanks
>

Message #4 by "Randy Cornish" <rlcornish@c...> on Sun, 2 Jun 2002 00:45:15
Would it work to create two calculated fields in your query (see below):

   Credits: IIF([Amount_Paid] = -2,[Amount_Paid],0)

   Debits:  IIF([Amount_Paid] = 1,[Amount_Paid],0)

I did not test this, I just typed it in.  I also have not seen what you 
are trying to do with your query.  Is it an aggregate query or a simple 
SELECT query?

If this is going the wrong direction, please repost.  I tend to agree 
with the other posters that doing two separate queries (or redesigning 
the table) is a better approach.

R

> I am working on a db created by someone unknown.  The db has 
tblCustomer 
a> nd tblInvoice in 1-Many relationship.  tblInvoice has a field called 
A> mount_Paid that shows the amount owing for the invoice.

> The table also has a field called Type that uses  two values, 1 for the 
b> alance and -2 for payments made towards the balance owed on the 
invoice.  
W> hen a order is placed and the total is say $200.00 it is entered as 
one 
r> ecord and the value 1 is entered in the Type field showing that this 
o> wing. A $200.00 entry  is also entered in the Amount_Paid. An invoice 
n> umber is entered in the InvoiceNo field.

> If a payment of $200.00 is made towards the balance another record is 
c> reated in the same table using the same invoice number, a value of -2 
is 
e> ntered in the Type field.  This -2 shows that it is a payment. A 
$200.00 
e> ntry is entered in the Amount_Paid field.

> The problem is that both entry for payment and invoice amount is using 
the
s> ame field. 

> I am running a query to get the Customer name,payments made and 
invoices 
t> hat has balances.  I can get outstanding invoices by using 1 in the 
c> riteria field if the query.  I am unable to establish how much is paid 
t> here is no distinct field to show a payment being made. I cannot use 
the 
s> ame field with different criterion twice.  I could run two separate 
q> ueries but it important to have both on the same report.

> How can I resolve this.  I hope I am clear in my explaination.

> Thanks
Message #5 by "Howard Stone" <ququmber@h...> on Sun, 2 Jun 2002 13:01:27
Thank you both for your help.  If I were creating the database I would 
add a separate field for the payment amount rather than having the Type 
field and entering 1 or -2 as I described in the database design. I would 
not put the payment amount and amount owed in the same field.

The database is an Open Systems database.  This company I would expect 
would have experienced programmers who are knowledgeable in database 
optimization, so, I can only conclude that there are reasons that I am 
not knowledgeable about that would make them design the database this way.

Can someone comment on the advantages/disadvantages, efficiency, etc, of 
putting the payment amount and invoice amount in separate fields (my 
design) versus their design? 

Any comments would further my understanding of database design and my 
knowledge base.

Thanks a lot for all comments.




> Hi Howard, you could run two queries and either insert the results of 
the
second query into the report using the DLookup function, or you could 
base a
report on it and insert the second report into the first report as a
sub-report.

Regards, wesley Kendrick

----- Original Message -----
From: "Howard Stone" <ququmber@h...>
To: "Access" <access@p...>
Sent: Saturday, June 01, 2002 5:18 PM
Subject: [access] resolvinq query


> I am working on a db created by someone unknown.  The db has tblCustomer
> and tblInvoice in 1-Many relationship.  tblInvoice has a field called
> Amount_Paid that shows the amount owing for the invoice.
>
> The table also has a field called Type that uses  two values, 1 for the
> balance and -2 for payments made towards the balance owed on the 
invoice.
> When a order is placed and the total is say $200.00 it is entered as one
> record and the value 1 is entered in the Type field showing that this
> owing. A $200.00 entry  is also entered in the Amount_Paid. An invoice
> number is entered in the InvoiceNo field.
>
> If a payment of $200.00 is made towards the balance another record is
> created in the same table using the same invoice number, a value of -2 
is
> entered in the Type field.  This -2 shows that it is a payment. A 
$200.00
> entry is entered in the Amount_Paid field.
>
> The problem is that both entry for payment and invoice amount is using 
the
> same field.
>
> I am running a query to get the Customer name,payments made and invoices
> that has balances.  I can get outstanding invoices by using 1 in the
> criteria field if the query.  I am unable to establish how much is paid
> there is no distinct field to show a payment being made. I cannot use 
the
> same field with different criterion twice.  I could run two separate
> queries but it important to have both on the same report.
>
> How can I resolve this.  I hope I am clear in my explaination.
>
> Thanks
>


  Return to Index