Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Question Unanswered: Help with counting records and then totaling them

    Hello all,

    It has been some time since I've visited the forum or at least posted a question but I'm stuck once again on something that I'm sure is relatively easy to do.

    I have 3 tables. Table 1 contains a listing of serial numbers for the different phones in inventory (whether on the shelf or distributed to a customer). Table 2 lists different monthly fees (like a cell phone) but right now I only have one monthly fee listed but this table will soon have multiple entries of different amounts. Table 3 lists the total amount billed to each phone for airtime. Each phone incurs the monthly fee whether or not it is used during the month.

    I need to count the number of records in table 1, multiply by the montly fee and then add that number to the total amount billed for one complete listing of monthly expenditures for all phones. I also want a similar query to total by a user selectable year for an annual total.

    Table 1
    SernoID
    PhoneSerno

    Table 2
    FeeID
    MonthlyFee

    Table 3
    ChargeID
    Month
    Year
    PhoneSerno
    PhoneCharges

    Any help is always and most gratefully appreciated!

    KC

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Creating an append / insert query will achieve what you want
    Code:
    INSERT INTO Table3 ( PhoneSerNo, PhoneCharges, [Month], [Year] )
    SELECT Table1.PhoneSerNo, Table2.MonthlyFee, Month(Now()) AS Expr1, Year(Now()) AS Expr2
    FROM Table1, Table2;
    however its neither clever or a good idea
    I think you need to revisit your data model and try to refine it before capturing any data

    first off give your tabnles a meaningfull name Tabel1,Table2,Table3 doesn't cut it
    DTPhones,DTFees & DTCharges may
    next be carefull about using SQL, or VBA reserved words such as MONTH & YEAR

    The query above works by effectively doing as UNION - if you add another fee to your fees table you will get a row added to the charges table for each phone and each fee. Eg if you have 3 phones and 1 fee - 2 charges, 3 phones, 2 fees 6 charges etc....)

    your reference to the phone in your charges table is the phones serial number - can you guarantee that you will never ever have a duplicated serail number for a phone. There may be an international standard which dictates that, but I don't know. you have defined the phone with a autonumbe primary key - so I'd suggest you use that autonumber column, or if you are saying the phone serial number is unique accross manufacturers then you don't need the autonumber column, in which case you can use the serial number.

    I'd expect to seem some sort of linkage between a phone and the fee(s) applciable to it (eg you may have a monthly cost for calls, SMS, MMS & insurance) there is no defined link

    I'd expect to see some form of connection back to a fee payer

    I'd expect to see a better form of charges table, why are you splitting out the year and month int he charges table - oif you store the one value (say period end) as a date then you can extract the month & day as required using the MONTH() & YEAR()

    perhaps it would be worthwhile relooking at your assignment and then have a look at this site. Then make another effort to model your requirements. Then by all means come back here if there is seomthing your don't understand or isn't clear to you.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Quote Originally Posted by healdem
    Creating an append / insert query will achieve what you want
    Code:
    INSERT INTO Table3 ( PhoneSerNo, PhoneCharges, [Month], [Year] )
    SELECT Table1.PhoneSerNo, Table2.MonthlyFee, Month(Now()) AS Expr1, Year(Now()) AS Expr2
    FROM Table1, Table2;
    however its neither clever or a good idea
    I think you need to revisit your data model and try to refine it before capturing any data

    first off give your tabnles a meaningfull name Tabel1,Table2,Table3 doesn't cut it
    DTPhones,DTFees & DTCharges may
    next be carefull about using SQL, or VBA reserved words such as MONTH & YEAR

    The query above works by effectively doing as UNION - if you add another fee to your fees table you will get a row added to the charges table for each phone and each fee. Eg if you have 3 phones and 1 fee - 2 charges, 3 phones, 2 fees 6 charges etc....)

    your reference to the phone in your charges table is the phones serial number - can you guarantee that you will never ever have a duplicated serail number for a phone. There may be an international standard which dictates that, but I don't know. you have defined the phone with a autonumbe primary key - so I'd suggest you use that autonumber column, or if you are saying the phone serial number is unique accross manufacturers then you don't need the autonumber column, in which case you can use the serial number.

    I'd expect to seem some sort of linkage between a phone and the fee(s) applciable to it (eg you may have a monthly cost for calls, SMS, MMS & insurance) there is no defined link

    I'd expect to see some form of connection back to a fee payer

    I'd expect to see a better form of charges table, why are you splitting out the year and month int he charges table - oif you store the one value (say period end) as a date then you can extract the month & day as required using the MONTH() & YEAR()

    perhaps it would be worthwhile relooking at your assignment and then have a look at this site. Then make another effort to model your requirements. Then by all means come back here if there is seomthing your don't understand or isn't clear to you.
    The table names are actually different as I just used them for the example.

    As far as using month and year (in their own tables) I agree with you but the month and year is all I need to have in the report. I tried to create a date format for mmm/yyyy but never got it to work. I would MUCH rather do it that way. I have finally figured this out. Definitely needed that last cup of coffee. Now there are now Month and Year tables. I've made it a field in table 3 called "ChargePeriod"

    Serial numbers are never duplicated as they are embedded on the phones SIM and used for billing.

    There is a relationship between the PhoneSerno and the PhoneCharges but I'm also thinking now (had my 3rd cup of coffee) that I should have MonthlyFee in the table as well and have the appropriate relationship. One of the reasons I didn't do this is because every phone will have some monthly fee it incurs but not airtime. The airtime is what is in the Table3 example.

    My company pays for all of the bills and is later reimbursed by the customer.

    I've got a total of 5 tables but only the 3 listed are required to do the totals.

    I am definitely open to any suggestions on how to better model the requirement.

    The data that I receive that is used to populate the database is:
    Billing Period (Month/Year)
    Phone Serial Numbers
    Monthly Fee
    Airtime Charges

    Thanks for the insight and help. I'll be checking out the link and see if I can make so changes that will make sense.
    Time to get back at it. I'm making a bit of progress. Thanks again.

    KC
    Last edited by kccpo; 06-14-06 at 09:38.

  4. #4
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Red face

    Ok, I THOUGHT I had changed the date format so it only displayed month and year but I was wrong.
    Entry Example: APR/2006
    Result: 4/1/2006

    I need it to just stay APR/2006 or even APR 2006 and then be able to reference either the month and/or year in my query so I can total on it.

    Here is my code that isn't working so far:

    SELECT SUM([Monthly Charges by SerialNo].[Phone Charges]) AS [Annual Total]
    FROM [Monthly Charges by SerialNo]

    WHERE [Monthly Charges by SerialNo].ChargePeriod Like "*" & [Enter Year To Total] & "*"

    UNION SELECT COUNT([SerialNo Listing].[SerialNo]) AS [STotal]
    FROM [BGAN SIM Listing]

    UNION SELECT SUM ([Monthly Fee].[Monthly Fee]) AS [MFee]
    FROM [Monthly Fee];


    Results of the query are sums from each of the tables listed separately all in currency format.
    I need the number of serial numbers multiplied by the monthly fee ([STotal]*[MFee] for a total of monthy fees. Then I need to add the result to the total of charges (in this case [Annual Charges]) for a grand total of all.

    Thanks.

    KC

  5. #5
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Really banging my head against the wall on this. I will continue to work on it but definitely am open to suggestions/help.

    Thanks

    KC

    Quote Originally Posted by kccpo
    Ok, I THOUGHT I had changed the date format so it only displayed month and year but I was wrong.
    Entry Example: APR/2006
    Result: 4/1/2006

    I need it to just stay APR/2006 or even APR 2006 and then be able to reference either the month and/or year in my query so I can total on it.

    Here is my code that isn't working so far:

    SELECT SUM([Monthly Charges by SerialNo].[Phone Charges]) AS [Annual Total]
    FROM [Monthly Charges by SerialNo]

    WHERE [Monthly Charges by SerialNo].ChargePeriod Like "*" & [Enter Year To Total] & "*"

    UNION SELECT COUNT([SerialNo Listing].[SerialNo]) AS [STotal]
    FROM [BGAN SIM Listing]

    UNION SELECT SUM ([Monthly Fee].[Monthly Fee]) AS [MFee]
    FROM [Monthly Fee];


    Results of the query are sums from each of the tables listed separately all in currency format.
    I need the number of serial numbers multiplied by the monthly fee ([STotal]*[MFee] for a total of monthy fees. Then I need to add the result to the total of charges (in this case [Annual Charges]) for a grand total of all.

    Thanks.

    KC

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't understand why you are usign your union query to find the sum of charges, when you already have your monthly phone charges in your table3

    where are you using this information? - is it in a report, if so then it may make more sense to use the SQL aggregation in the reports group / p gae / report footers

    as regards to formatting the date what format() are you using?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    One of the reasons that I'm using the union query is that I need to multiply the monthly fee times the number of phones. I figured since I was doing that then I would just include the phone charges. Everything is adding up but just doesn't total everything.

    Right now I'm just trying to get the query done but eventually it will be a report. The db is in its early stages and just want to be able to perform the action before designing a report.

    I was able to figure out the date finally. I just needed to muddle it over a bit and let the caffeine kick in.

    If I need to design the report before the query then I guess I can do that. I just like to get the queries done first and then go in and design reports once I get all queries done.

    I'm not sure what you mean by "use the SQL aggregation in the reports group / p gae / report footers". This is nothing I've ever done so not familar with it.

    Thanks again for your patience and help.

    KC

    Quote Originally Posted by healdem
    I don't understand why you are usign your union query to find the sum of charges, when you already have your monthly phone charges in your table3

    where are you using this information? - is it in a report, if so then it may make more sense to use the SQL aggregation in the reports group / p gae / report footers

    as regards to formatting the date what format() are you using?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    stick to the KISS principle
    you cna do all the aggregation or statistiacal analysis in the reports
    providing your base data is right (ie you know how much a phone costs per month (theoretically you do - its from table3) then the rest should be easy

    if you create a monthly report footer, and a report footer
    and insert a control and set the data source = sum([<myphonechargescolumn>] then the report will do the summing for you
    create a second control and set the data source = count([<myphonechargescolumn>] then the report will contain the number of pjhones in the month.

    repeat the first column in the report footer and it will tell you the summ of charges in the report.

    we have already identified a way of creating the monthly charges for a phone, although I dstill think you physical design is suspect it shoudl work
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    I am DEFINITELY in agreement with the KISS way of doing it.

    What do you mean by the physical design being suspect? If you have a better way of setting it up I would love to hear it. Right now this thing is very small but is going to rapidly grow over the next 6+ months.

    Thanks again. I'll look into the report footer info as soon as I am able to get to working on it again.

    KC


    Quote Originally Posted by healdem
    stick to the KISS principle
    you cna do all the aggregation or statistiacal analysis in the reports
    providing your base data is right (ie you know how much a phone costs per month (theoretically you do - its from table3) then the rest should be easy

    if you create a monthly report footer, and a report footer
    and insert a control and set the data source = sum([<myphonechargescolumn>] then the report will do the summing for you
    create a second control and set the data source = count([<myphonechargescolumn>] then the report will contain the number of pjhones in the month.

    repeat the first column in the report footer and it will tell you the summ of charges in the report.

    we have already identified a way of creating the monthly charges for a phone, although I dstill think you physical design is suspect it shoudl work

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by kccpo
    .....What do you mean by the physical design being suspect? If you have a better way of setting it up I would love to hear it. Right now this thing is very small but is going to rapidly grow over the next 6+ months.....

    post #2 just about covers it....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •