create totals based on a field values...

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

Guest

I am trying to create totals for my invoices table.

So for each record, if the invoice id is the same, I want to creat a total
based on that? How do I go about doing that?

For example, if I have invoice number 0018 that has 4 records for a price
of $50.00 each, and I have invoice number 0019 that has 2 records for a price
of $35.00 each. Can I create a total calculation for invoice 0018 of $200.00
and an total calculation for invoice 0019 of $70.00?

Hope this is clear..

Thanks in advance..

Brook
 
Brook said:
I am trying to create totals for my invoices table.

So for each record, if the invoice id is the same, I want to creat a total
based on that? How do I go about doing that?

For example, if I have invoice number 0018 that has 4 records for a price
of $50.00 each, and I have invoice number 0019 that has 2 records for a price
of $35.00 each. Can I create a total calculation for invoice 0018 of $200.00
and an total calculation for invoice 0019 of $70.00?
Click to expand...


SELECT InvoiceID, Sum(Price)
FROM Invoices
GROUP BY InvoiceID
 
ok, I created my qryinvoice totals and added the code you provided me and it
works great, now I would like to add more field from two other tables, when
I add my tables, join by primary key, my query then returns no values.

Any ideas on what I should be doing ?

Let me know if you need more information.

Brook
 
Brook said:
ok, I created my qryinvoice totals and added the code you provided me and it
works great, now I would like to add more field from two other tables, when
I add my tables, join by primary key, my query then returns no values.
Click to expand...


Defnitely need more details.

What are these other tables?
How do they relate to the Invoices table?
What result are you looking for?
What have you tried so far?
 
hello marshall,

here is the additional information:

3 tables:

tblinvoices:
Fields & Types
invoiceid Long Integer
invoicetype Text
invoicenum Long Integer
invoicenumber Text
invoicedate Date/Time
Company Text
Contact Text
BillingAddr1 Text
BillingAddr2 Text
BillingAddr3 Text
BillingCity Text
BillingState Text
BillingZip Text
Phone Text
Fax Text
ShippingAddr1 Text
ShippingAddr2 Text
ShippingAddr3 Text
ShippingCity Text
ShippingState Text
ShippingZip Text
ShippingCompany Text


tblinvoicedetails:
Fields

invoiceid Long Integer
orderid Long Integer
serialnumber Text
DesignNumber Text
DesignName Text
Quality Text
Size Text
SqFt Text
PricePerSqFoot Currency
TotalPrice Currency
shippingcost Currency


tblinvoicepayments:
Fields:

invoiceid Text
invoicedate Date/Time
invoicenumber Text
invoicetotal Currency
paymentnumber Long Integer
paymentdate Date/Time
paymentamount Currency
Balance Due Currency


The 3 tables have primary Key's of the invoiceid, and all have the invoiceid
relationships set up.

What I am ultimately trying to accomplish is set up an A/R qry to accept
payments for my invoices.

I have tried to set up the 3 invoiceid relationships, made sure all have the
same format, and have recreated the qry.

Thanks for your help!

Brook
 
Still not sure what result you want, but I think it might be
something like:

SELECT tblinvoices.invoiceid,
tblinvoices.Company,
Sum(tblinvoicedetails.TotalPrice
+ tblinvoicedetails.shippingcost) As InvAmt,
Sum(tblinvoicepayments.paymentamount) As TotPaid
FROM tblinvoices LEFT JOIN tblinvoicedetails
ON tblinvoices.invoiceid = tblinvoicedetails.invoiceid
LEFT JOIN tblinvoicepayments
ON tblinvoices.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoices.invoiceid,
tblinvoices.Company
 
Marshall,

what I am ultimately trying to accomplish is to setup a completely
independent Accounts Receivables table/query for my invoices. The reason is,
is that I don't want to have to go into each of my invoices/frminvoices to
accept payments. B/c on any given day I might receive 50 payments and that
would be 50 times that I would have to go into 50 different invoices.

So I am trying to setup an indepent qry that will calculate and store my
invoicetotals so I can create a form to have the opportunity to enter
multiple payments to multiple invoices at any given time... if you can give
me a better method... I am open.

I do want to thank you for the time and effort you are putting into
helping me!

When I paste the code into a new query, and tro to save... I am getting
the following message:

Syntax Error (missing operator) in query expression 'tblinvoices.invoiceid
= tblinvoicedetails.invoiceid LEFT JOIN tblinvoicepayments ON
tblinvoices.invoiceid = tblinvoicepayments.invoiceid'.

I am baffled for I am new to this type of setup...

Any ideas?

Thanks again..

Brook

Marshall Barton said:
Still not sure what result you want, but I think it might be
something like:

SELECT tblinvoices.invoiceid,
tblinvoices.Company,
Sum(tblinvoicedetails.TotalPrice
+ tblinvoicedetails.shippingcost) As InvAmt,
Sum(tblinvoicepayments.paymentamount) As TotPaid
FROM tblinvoices LEFT JOIN tblinvoicedetails
ON tblinvoices.invoiceid = tblinvoicedetails.invoiceid
LEFT JOIN tblinvoicepayments
ON tblinvoices.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoices.invoiceid,
tblinvoices.Company
--
Marsh
MVP [MS Access]

here is the additional information:

3 tables:

tblinvoices:
Fields & Types
invoiceid Long Integer
invoicetype Text
invoicenum Long Integer
invoicenumber Text
invoicedate Date/Time
Company Text
Contact Text
BillingAddr1 Text
BillingAddr2 Text
BillingAddr3 Text
BillingCity Text
BillingState Text
BillingZip Text
Phone Text
Fax Text
ShippingAddr1 Text
ShippingAddr2 Text
ShippingAddr3 Text
ShippingCity Text
ShippingState Text
ShippingZip Text
ShippingCompany Text


tblinvoicedetails:
Fields

invoiceid Long Integer
orderid Long Integer
serialnumber Text
DesignNumber Text
DesignName Text
Quality Text
Size Text
SqFt Text
PricePerSqFoot Currency
TotalPrice Currency
shippingcost Currency


tblinvoicepayments:
Fields:

invoiceid Text
invoicedate Date/Time
invoicenumber Text
invoicetotal Currency
paymentnumber Long Integer
paymentdate Date/Time
paymentamount Currency
Balance Due Currency


The 3 tables have primary Key's of the invoiceid, and all have the invoiceid
relationships set up.

What I am ultimately trying to accomplish is set up an A/R qry to accept
payments for my invoices.

I have tried to set up the 3 invoiceid relationships, made sure all have the
same format, and have recreated the qry.
Click to expand...
Click to expand...
 
Brook said:
what I am ultimately trying to accomplish is to setup a completely
independent Accounts Receivables table/query for my invoices. The reason is,
is that I don't want to have to go into each of my invoices/frminvoices to
accept payments. B/c on any given day I might receive 50 payments and that
would be 50 times that I would have to go into 50 different invoices.

So I am trying to setup an indepent qry that will calculate and store my
invoicetotals so I can create a form to have the opportunity to enter
multiple payments to multiple invoices at any given time... if you can give
me a better method... I am open.
Click to expand...


Oh boy, that's a different kind of operation than I thought
you were asking about. Since a Totals type query like I
posted before is not updatable, it con not be used to
edit/add data to your database. It would be fine to use to
display the totals on the screen or in a report, but not in
an editable form.

To be able to enter payment information, you need to use a
form based on the Payments table. You may(?) already have
this somehow, but I don't know how your entire application
is designed.

As for the totals you were trying to calculate in a query,
that may, or may not, be useful to know when just entering
the payments data, but those totals should NOT be stored in
a table.

It seems to me, that the only info about an invoice that
would be needed when entering a payment is the invoice
number. On the other hand, it would also be viable to enter
payments from a customer without caring which invoice(s?)
the payment is applied to.

From where I sit, it looks like you need to step back from
the query design and think more deeply about specifying the
entire operation before proceeding with an implementation.
 
Marshall,

I don't know if I am just not thinking of this right, but I don't
understand why this is such a big issue? Once my invoice is created, it
doesn't change therefore the totals don't change...

That is why I was trying to set up a table or query to accept the
totalinvoice amount and build from there to accept payments so that I can
create a report showing invoice total less payment 1 less payment etc equals
Balance Due, and I could send that Balance Due Report to my clients..

Am I just thinking irrationally as far as codeing aspect ms access?

Thanks,

Brook
 
It might be a big issue because You are asking for advice on
how to do something that has yet to be fully explained to me
(or others). In general an invoice is no more final than
any other data. What happens if a customer calls in to
complain that the invoice is inaccurate and you agree with
them? Most likely, the invoice should be edited to make it
correct and the total you have stored must be recalculated
to bring them in line with the changes, right? The usual
way to deal with this is store the quantity and price values
for each item in the invoice details and to just calculate
the invoice total whenever you need to see it. The balance
due amount is another calculation that needs to be done on
the fly (for similar reasons). This is why you would use a
query, form or report calculations to display those values
as needed.

As for recording the receivables as they come in, you only
need to know the invoice number while making entries in the
Payments table. This would commonly be done by just
entering the invoice number in its field in the payments
form or using some other kind of mechanism to select the
payment's matching invoice number. The totals are not
needed at this point.

To generate an updated statement for the customer, you would
run a report that performs the needed, up to date,
calculations.

And, no, I don't believe you are thinking irrationally. I
just think all the issues have yet to be fully explained.
 
Brook said:
I am trying to create totals for my invoices table.

So for each record, if the invoice id is the same, I want to creat a total
based on that? How do I go about doing that?

For example, if I have invoice number 0018 that has 4 records for a price
of $50.00 each, and I have invoice number 0019 that has 2 records for a price
of $35.00 each. Can I create a total calculation for invoice 0018 of $200.00
and an total calculation for invoice 0019 of $70.00?

Hope this is clear..

Thanks in advance..

Brook
Click to expand...
HI Brook,

I would do it by query
and make the expression total_inv: [number]*[price]
just need to select the invoice in the invoice column
criteria= like "*" & [invoice#] & "*"
 
Back
Top