1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Gathering Simple Statistics In Microsoft Access 2007

Discussion in 'Business Applications' started by TW.87, Oct 25, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    Hello everyone!

    I have just finished building a very, very simple database in Microsoft Access 2007.

    The database consists of the following tables:
    • CD: Albums
    • CD: Singles
    • DVD: Movies
    • DVD: Music
    • DVD: Special Interest
    • DVD: Television
    As you can guess, each table catalogues my multimedia collection

    What I would like to do, is to create something in Microsoft Access 2007 (I am relatively unfamiliar with the program, so am not sure as to what or how I would do this) that, on one screen, could give me a tally of how many CD albums, CD singles, DVD movies, music DVD's, special interest DVD's and DVD television I have. I would also like it to update itself automatically should I add or remove a record from any of the above tables.

    Any help on this would be greatly appreciated!
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I don't have 2007, so I can't help you there - I think you can save a copy as a 2003 version - if you can, save it, zip it and upload it here and we can look at it. Off hand, do you have separate tables for each type (e.g. a table called cd albums, another called cd singles, etc.) or do you have all the items listed in one table with a listing at the end stating what it is? The latter could be done several ways, too, including a drop-down list/combo box. But with the raw data in hand you could have a nice database finished soon.
     
  3. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    Thank you for your reply!

    I'd prefer not to upload the database, as it has a lot of other personal information in it, as the multimedia list is part of a detailed insurance catalogue. Sorry to be a nuisance when you're offering to help.

    Yes, though, there are seperate tables for each of the categories I listed above.

    Can you provide any further help without the file itself?

    Thank you again for your response.
     
  4. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Woudn't b easyer to have just 2 tables one for information about CD/DVD and other with types of CD/DVD (CD: Albums; CD: Singles;DVD: Movies;DVD: Music;DVD: Special Interest;DVD: Television). And when U write information about a CD/DVD, U can through a combobox, choose the type of the CD/DVD from that list.

    In my opinion it would be easyer to manage and to get those statistics!
     
  5. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    In terms of the database, yes, it probably would be easier, but in terms of what I am using the database for, no it's not.

    Is there still a way to get the statistics I am looking for?
     
  6. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    I think it could be done using a form, but not really sure!
    I'll check and poste U back!
     
  7. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    Thank you! I really appreciate your help.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    TW.87, the way to do this is with a Query which is set to display "Totals".
    This will automatically "Group by" the various categories and you need to set the RecordID field to "Count".
    You can display the data in the query, on a Form or Report.
     
  9. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    U should make one query set to show totals for each of Your tables.
    After that create a blank form, on that form insert subforms based on queryes form above (each subform one diffret query).
    This shoul work!
    If not , then, if is posible, make a copy of Your DB, and delete all Ur personal info, leave only tables with CD/DVD, with some sample info! And post it, then we could give U a better answer!
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Attached is one way to do it - not necessarily the best, but it works ;)
     

    Attached Files:

  11. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    Would you please be able to provide more detailed instructions on your suggestion?

    Thank you for your help, too!
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    look at the database I uploaded - I already did it for you!
     
  13. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    My apologies - thank you so much for doing that!

    I am interested in how you made it, though. I'd like to know the process, if you have some time to explain it to me?
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Not too difficult. Per your description of what you had built, I made a table for each type of media. Note that they all have an autonumber column. I then made 1 query for each table, which, if you look at it in design view, only contains the number column from the table. In design view still for each query, I clicked on the summary key (looks rather like an E but with > rather than - in the center) and chose Count, and saved the query. So all a query does is count how many numbers appear in the column (note - you can select a lot of other things - as you use Access, you will find uses for them).
    I then created a blank report - no wizard. I went to the design view for the report. I selected the Subreport icon on the toolbar and dragged it to the blank report. A wizard walked me through assigning the subreport to one of the queries I had just built and created automatically a subreport for the report. I repeated this for each query, went and adjusted a few things in the subreports (took out the headers, for instance, which just looked odd on the final report) and I was done.
    This could have been other ways - combining all the queries into one query and creating a report, using the wizard if you want, to base it on that total query. If the tables had a column that always had a value of 1 in the cell in each row, then the report could have been based directly on the tables, summing that column's values. Finally, using VBA there are still more things that could be done.
     
  15. TW.87

    TW.87 Thread Starter

    Joined:
    Dec 29, 2005
    Messages:
    308
    Thank you very much for your help.

    I'm a little lost with it, but I'll have a re-read and keep fiddling, as I'm sure the answer is here. If I have anymore questions, you can expect to hear back from me, ha ha!

    Thanks again, everyone.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/643148

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice