Tech Support Guy banner
Status
Not open for further replies.
1 - 15 of 15 Posts

·
Registered
Joined
·
308 Posts
Discussion Starter · #1 ·
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!
 

·
Registered
Joined
·
7,837 Posts
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.
 

·
Registered
Joined
·
308 Posts
Discussion Starter · #3 ·
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.
 

·
Registered
Joined
·
869 Posts
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!
 

·
Registered
Joined
·
308 Posts
Discussion Starter · #5 ·
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?
 

·
Registered
Joined
·
869 Posts
I think it could be done using a form, but not really sure!
I'll check and poste U back!
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
869 Posts
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!
 

·
Registered
Joined
·
308 Posts
Discussion Starter · #11 ·
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!
 

·
Registered
Joined
·
308 Posts
Discussion Starter · #13 ·
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?
 

·
Registered
Joined
·
7,837 Posts
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.
 

·
Registered
Joined
·
308 Posts
Discussion Starter · #15 ·
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.
 
1 - 15 of 15 Posts
Status
Not open for further replies.
Top