Solved: Gathering Simple Statistics In Microsoft Access 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 
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.
 

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.
 
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!
 

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?
 
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!
 

TW.87

Thread Starter
Joined
Dec 29, 2005
Messages
308
Aj_old said:
I think it could be done using a form, but not really sure!
I'll check and poste U back!
Thank you! I really appreciate your help.
 

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.
 
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!
 

TW.87

Thread Starter
Joined
Dec 29, 2005
Messages
308
Aj_old said:
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!
Would you please be able to provide more detailed instructions on your suggestion?

Thank you for your help, too!
 

TW.87

Thread Starter
Joined
Dec 29, 2005
Messages
308
slurpee55 said:
look at the database I uploaded - I already did it for you!
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?
 
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top