(newbie) Calculate balance from multiple records?

I have 2 databases - "Customer Details" & "Invoices". I want to create a "Balance" field in the "Customer Details" database that calculates the total amount owing of unpaid "Invoice" records? Any suggestions appreciated.

Sum (Invoice::Invoice_Amount) Where Invoice is the relationship between "Customer Details" and "Invoices" and Invoice_Amount is the total of each invoice. -Scott in SoCal Larry <larry@none.com> wrote in message news:<180620042352494804%larry@none.com>... > I have 2 databases - "Customer Details" & "Invoices". > > I want to create a "Balance" field in the "Customer Details" database > that calculates the total amount owing of unpaid "Invoice" records? > > Any suggestions appreciated.

Thanks Scott When creating a relationship between "Customer Details" and "Invoices" should I Match data from "Customer_Name" field in "Customer Details" database with "Invoice_Amount" field in "Invoices"database? This didn't work. I assume the calculated "Balance" field will update automatically? Larry In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott Davis <scott_davis_@hotmail.com> wrote: > Sum (Invoice::Invoice_Amount) > > Where Invoice is the relationship between "Customer Details" and > "Invoices" and Invoice_Amount is the total of each invoice. > > -Scott in SoCal > > > Larry <larry@none.com> wrote in message > news:<180620042352494804%larry@none.com>... > > I have 2 databases - "Customer Details" & "Invoices". > > > > I want to create a "Balance" field in the "Customer Details" database > > that calculates the total amount owing of unpaid "Invoice" records? > > > > Any suggestions appreciated.

Okay I got it. Match Customer_Name with Customer_Name. Thanks again. In article <190620042238313315%larry@none.com>, Larry <larry@none.com> wrote: > Thanks Scott > > When creating a relationship between "Customer Details" and "Invoices" > should I Match data from "Customer_Name" field in "Customer Details" > database with "Invoice_Amount" field in "Invoices"database? > > This didn't work. I assume the calculated "Balance" field will update > automatically? > > Larry > > > In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott > Davis <scott_davis_@hotmail.com> wrote: > > > Sum (Invoice::Invoice_Amount) > > > > Where Invoice is the relationship between "Customer Details" and > > "Invoices" and Invoice_Amount is the total of each invoice. > > > > -Scott in SoCal > > > > > > Larry <larry@none.com> wrote in message > > news:<180620042352494804%larry@none.com>... > > > I have 2 databases - "Customer Details" & "Invoices". > > > > > > I want to create a "Balance" field in the "Customer Details" database > > > that calculates the total amount owing of unpaid "Invoice" records? > > > > > > Any suggestions appreciated.

Larry wrote: > Okay I got it. Match Customer_Name with Customer_Name. > > Thanks again. > > > In article <190620042238313315%larry@none.com>, Larry <larry@none.com> > wrote: > > >>Thanks Scott >> >>When creating a relationship between "Customer Details" and "Invoices" >>should I Match data from "Customer_Name" field in "Customer Details" >>database with "Invoice_Amount" field in "Invoices"database? >> >>This didn't work. I assume the calculated "Balance" field will update >>automatically? >> >>Larry >> >> >>In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott >>Davis <scott_davis_@hotmail.com> wrote: >> >> >>>Sum (Invoice::Invoice_Amount) >>> >>>Where Invoice is the relationship between "Customer Details" and >>>"Invoices" and Invoice_Amount is the total of each invoice. >>> >>>-Scott in SoCal >>> >>> >>>Larry <larry@none.com> wrote in message >>>news:<180620042352494804%larry@none.com>... >>> >>>>I have 2 databases - "Customer Details" & "Invoices". >>>> >>>>I want to create a "Balance" field in the "Customer Details" database >>>>that calculates the total amount owing of unpaid "Invoice" records? >>>> >>>>Any suggestions appreciated. You should (always) use a unique ID field for this sort of match. What happens if you have two customers with the same name? Create a Customer_ID and make it a unique auto enter serial e.g. C00001 increment 1, you can use replace command to enter value series for existing records. The C prefix has advantages; it can prevent sort problems, avoid sort issues and help admin in complex keys... and the 00001 allows one short of 100,000 customers before it glitches. You also have a Unique Invoice_ID no? Chris Brown Neurosurgery University of Adelaide

In article <40d62644@yorrell.saard.net>, Chris Brown <cbrown@medicine.adelaide.edu.au> wrote: <snip> > You should (always) use a unique ID field for this sort of match. What > happens if you have two customers with the same name? Create a > Customer_ID and make it a unique auto enter serial e.g. C00001 > increment 1, you can use replace command to enter value series for > existing records. The C prefix has advantages; it can prevent sort > problems, avoid sort issues and help admin in complex keys... and the > 00001 allows one short of 100,000 customers before it glitches. You > also have a Unique Invoice_ID no? > > Chris Brown > Neurosurgery > University of Adelaide > That sounds like a WISE idea, thanks for your input. Larry

Personally, I don't see any advantages to using anything but numbers for primary keys. Could you give an example of when a prefix would be useful? Chris Brown <cbrown@medicine.adelaide.edu.au> wrote in message news:<40d62644@yorrell.saard.net>... > Larry wrote: > > Okay I got it. Match Customer_Name with Customer_Name. > > > > Thanks again. > > > > > > In article <190620042238313315%larry@none.com>, Larry <larry@none.com> > > wrote: > > > > > >>Thanks Scott > >> > >>When creating a relationship between "Customer Details" and "Invoices" > >>should I Match data from "Customer_Name" field in "Customer Details" > >>database with "Invoice_Amount" field in "Invoices"database? > >> > >>This didn't work. I assume the calculated "Balance" field will update > >>automatically? > >> > >>Larry > >> > >> > >>In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott > >>Davis <scott_davis_@hotmail.com> wrote: > >> > >> > >>>Sum (Invoice::Invoice_Amount) > >>> > >>>Where Invoice is the relationship between "Customer Details" and > >>>"Invoices" and Invoice_Amount is the total of each invoice. > >>> > >>>-Scott in SoCal > >>> > >>> > >>>Larry <larry@none.com> wrote in message > >>>news:<180620042352494804%larry@none.com>... > >>> > >>>>I have 2 databases - "Customer Details" & "Invoices". > >>>> > >>>>I want to create a "Balance" field in the "Customer Details" database > >>>>that calculates the total amount owing of unpaid "Invoice" records? > >>>> > >>>>Any suggestions appreciated. > > > You should (always) use a unique ID field for this sort of match. What > happens if you have two customers with the same name? Create a > Customer_ID and make it a unique auto enter serial e.g. C00001 > increment 1, you can use replace command to enter value series for > existing records. The C prefix has advantages; it can prevent sort > problems, avoid sort issues and help admin in complex keys... and the > 00001 allows one short of 100,000 customers before it glitches. You > also have a Unique Invoice_ID no? > > > > Chris Brown > Neurosurgery > University of Adelaide

example; building complex keys, looking at tables of records as an admin the pattern is a visual reinforcement when looking for errors, and is real handy when building them to confirm the right sequences are going in C000356 R7654 P0006 H00456 A99973 )client/referral/procedure/hospital/account...) it also means you can easily identify the component ID in a problem record you want to go off and look at in the relevant table And so that nobody starts thinking what is wrong with initial design, that these sorts of problems arise to start with; when you inherit existing solutions, that have mutated over years, there can be a zillion inconsistencies in what was entered. sorting serial numbers often becomes necessary to become serial text, there are situations where conversion will drop leading zeroes, looking at admin tables of thousands of records when data scrubbing is a lot easier if every record value has the same format ( 7, 5, 5, 6, 6 as above) sort 1.2.3....9.10.11, rather than 1, 10, 100, 2, 3, 4, 400... Scott Davis wrote: > Personally, I don't see any advantages to using anything but numbers > for primary keys. Could you give an example of when a prefix would be > useful? > > Chris Brown <cbrown@medicine.adelaide.edu.au> wrote in message news:<40d62644@yorrell.saard.net>... > >>Larry wrote: >> >>>Okay I got it. Match Customer_Name with Customer_Name. >>> >>>Thanks again. >>> >>> >>>In article <190620042238313315%larry@none.com>, Larry <larry@none.com> >>>wrote: >>> >>> >>> >>>>Thanks Scott >>>> >>>>When creating a relationship between "Customer Details" and "Invoices" >>>>should I Match data from "Customer_Name" field in "Customer Details" >>>>database with "Invoice_Amount" field in "Invoices"database? >>>> >>>>This didn't work. I assume the calculated "Balance" field will update >>>>automatically? >>>> >>>>Larry >>>> >>>> >>>>In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott >>>>Davis <scott_davis_@hotmail.com> wrote: >>>> >>>> >>>> >>>>>Sum (Invoice::Invoice_Amount) >>>>> >>>>>Where Invoice is the relationship between "Customer Details" and >>>>>"Invoices" and Invoice_Amount is the total of each invoice. >>>>> >>>>>-Scott in SoCal >>>>> >>>>> >>>>>Larry <larry@none.com> wrote in message >>>>>news:<180620042352494804%larry@none.com>... >>>>> >>>>> >>>>>>I have 2 databases - "Customer Details" & "Invoices". >>>>>> >>>>>>I want to create a "Balance" field in the "Customer Details" database >>>>>>that calculates the total amount owing of unpaid "Invoice" records? >>>>>> >>>>>>Any suggestions appreciated. >> >> >>You should (always) use a unique ID field for this sort of match. What >>happens if you have two customers with the same name? Create a >>Customer_ID and make it a unique auto enter serial e.g. C00001 >>increment 1, you can use replace command to enter value series for >>existing records. The C prefix has advantages; it can prevent sort >>problems, avoid sort issues and help admin in complex keys... and the >>00001 allows one short of 100,000 customers before it glitches. You >>also have a Unique Invoice_ID no? >> >> >> >>Chris Brown >>Neurosurgery >>University of Adelaide

I try to create one table for each entity and then use cross-reference tables for many-to-many relationships. Unfortunately, Filemaker pre-Version 7 pushes you to use fewer tables (having to configure security on each file, pushing the limit on number of files that Filemaker Server can host, etc.). I see your point though. Thanks for the info! Chris Brown <cbrown@medicine.adelaide.edu.au> wrote in message news:<40d7aa60$1@yorrell.saard.net>... > example; building complex keys, looking at tables of records as an admin > the pattern is a visual reinforcement when looking for errors, and is > real handy when building them to confirm the right sequences are going in > C000356 R7654 P0006 H00456 A99973 > )client/referral/procedure/hospital/account...) > > it also means you can easily identify the component ID in a problem > record you want to go off and look at in the relevant table > > And so that nobody starts thinking what is wrong with initial design, > that these sorts of problems arise to start with; when you inherit > existing solutions, that have mutated over years, there can be a zillion > inconsistencies in what was entered. > > > > sorting > serial numbers often becomes necessary to become serial text, there are > situations where conversion will drop leading zeroes, looking at admin > tables of thousands of records when data scrubbing is a lot easier if > every record value has the same format ( 7, 5, 5, 6, 6 as above) > > > sort 1.2.3....9.10.11, rather than 1, 10, 100, 2, 3, 4, 400... > > > > > > Scott Davis wrote: > > > Personally, I don't see any advantages to using anything but numbers > > for primary keys. Could you give an example of when a prefix would be > > useful? > > > > Chris Brown <cbrown@medicine.adelaide.edu.au> wrote in message news:<40d62644@yorrell.saard.net>... > > > >>Larry wrote: > >> > >>>Okay I got it. Match Customer_Name with Customer_Name. > >>> > >>>Thanks again. > >>> > >>> > >>>In article <190620042238313315%larry@none.com>, Larry <larry@none.com> > >>>wrote: > >>> > >>> > >>> > >>>>Thanks Scott > >>>> > >>>>When creating a relationship between "Customer Details" and "Invoices" > >>>>should I Match data from "Customer_Name" field in "Customer Details" > >>>>database with "Invoice_Amount" field in "Invoices"database? > >>>> > >>>>This didn't work. I assume the calculated "Balance" field will update > >>>>automatically? > >>>> > >>>>Larry > >>>> > >>>> > >>>>In article <e10f5973.0406181454.15ef105a@posting.google.com>, Scott > >>>>Davis <scott_davis_@hotmail.com> wrote: > >>>> > >>>> > >>>> > >>>>>Sum (Invoice::Invoice_Amount) > >>>>> > >>>>>Where Invoice is the relationship between "Customer Details" and > >>>>>"Invoices" and Invoice_Amount is the total of each invoice. > >>>>> > >>>>>-Scott in SoCal > >>>>> > >>>>> > >>>>>Larry <larry@none.com> wrote in message > >>>>>news:<180620042352494804%larry@none.com>... > >>>>> > >>>>> > >>>>>>I have 2 databases - "Customer Details" & "Invoices". > >>>>>> > >>>>>>I want to create a "Balance" field in the "Customer Details" database > >>>>>>that calculates the total amount owing of unpaid "Invoice" records? > >>>>>> > >>>>>>Any suggestions appreciated. > >> > >> > >>You should (always) use a unique ID field for this sort of match. What > >>happens if you have two customers with the same name? Create a > >>Customer_ID and make it a unique auto enter serial e.g. C00001 > >>increment 1, you can use replace command to enter value series for > >>existing records. The C prefix has advantages; it can prevent sort > >>problems, avoid sort issues and help admin in complex keys... and the > >>00001 allows one short of 100,000 customers before it glitches. You > >>also have a Unique Invoice_ID no? > >> > >> > >> > >>Chris Brown > >>Neurosurgery > >>University of Adelaide

Similiar Articles:

comp.databases.mysql - page 38
newbie: "complex" query 2 2 (4/25/2006 9 ... Dear Mysql-ians, I want to calculate the standard deviation of data that are in multiple ... 47 PM) Hi, I just loaded 71,000 records ...

Showing a searched for date in a report - comp.databases.filemaker ...
If they want to show all records, then, Go ... Recommendations for Newbie - comp.lang.asm.x86 But ... build a simple report form that shows all ... Calculate multiple ...

list file through filter by recent date - comp.lang.awk
... but you can assign multiple ... Calculating age on a specific date - comp ... above ... take about 20 minutes to go through all the records. ...

[comp.publish.cdrom] CD-Recordable FAQ, Part 1/4 - comp.publish ...
Archive-name: cdrom/cd-recordable/part1 Posting-Frequency: monthly Last-modified: 2008/10/09 Version: 2.71 Send corrections and updates to And...

input & output in assembly - comp.lang.asm.x86
I'm a complete newbie so plz excuse Theoretical bit ... even more confusing because you've got to handle multiple ... use the techniques that will give the best /balance ...

ntpd, boot time, and hot plugging - comp.protocols.time.ntp ...
... all the mitigation algorithms using multiple-sample ... it's better to remove it and let the server re-calculate ... of the initial time estimate could be used to balance ...

Re: PDE toolbox initial condi - comp.soft-sys.matlab
Re: PDE toolbox initial condi Follow

top 10 uses for random data compression?? anyone? - comp ...
Disinformation feeds on disinformation, and whatever the record shows is shunted ... Almost no coalitions widely calculate the medical prison. All enthusiastic surprises ...

How to calculate median for a newbie?: Microsoft, Access, 2003
How to calculate median for a newbie? ... i have some problem on calculate Balance of the debit ... Hi, The Median is simply the middle record so ...

Microsoft Access: Newbie question on calculated fields - database ...
calculate the balance using the formula "=[Previous Balance ... question in suming & filtering/deleting records. newbie ... Newbie Question - Building multiple parts ...

7/8/2012 12:33:03 PM