A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

ACCESS DVD Database



 
 
Thread Tools Display Modes
  #11  
Old May 28th, 2007, 05:28 PM posted to microsoft.public.access.gettingstarted
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default ACCESS DVD Database

My data is not simple. The main view sheet looks at about twelve sheets
for its compilation.

Each "table" (sheet) is comprised of a single ID column (field) and some
data, like title or such. That would be quite normalized if I were to
import each of those tables into access as separate tables, no?


Well, the "trick" or concept here is that you DO NOT want to create (well,
import)
a table for just one piece of information. Normalizing your data simply
means you build
a correct data model that models your needs. So, if you have a field that is
the
description for the movie, then you do NOT want to add the extra complexity
of
creating a extra table. If you ONLY have one field, and it not repeating
data (like
many actors), then that field should be in your main table. So, to answer
should
you import this data into "many" tables, the answer is only going to be yes
if
that data is repeating. So, if you have a table that has "many" actors, then
that would continue to be another table. So, we would have two tables so far

tblDVD
--- tblActorsInMovie

So, simply making lots of tables is generally not a good idea for the sake
of making more tables. You ONLY create related tables if the related data
is repeating. In your case, repeating data could be in these extra lookup
tables, or even in the main table (eg: actor1, actore2 is two fields, but
they should be like the above setup, and this I can have as many
actors for a single dvd movie as I need. (no hard coding of how many
actors with fields named actor1, actor2 etc.).


All I would have to do presumably is tie them all together with the ID
field, which is the unique identifier for each.


You main tblDVD would use autonumber id. For child tables, they need
a standard long number field that will be used to point back to the main
tblDVD id. So, the child tables don't really need autonumber fields (but,
you can and should consider just leaving the default "id" as the primary
key for those child tables. All of those child tables will need a plane
Jane standard long number field for the relation. When you add a child
record YOU MUST SET this value. (if you use a sub-form,t hen ms-access
will set this value for you if you setup the relational correctly).



It is not clear if you have "repeating" data for a single dvd (by
repeating,
I mean "many" actors can be attached to one movie).


I intend on incorporating an actors list for each title as well. In
that table, each actor would have a list of ID numbers associated with
each film he or she has appeared in.


Well, don't confuse the actors table with that of "tblActorsInMove".

In our above design so far, we don't have a table of actors. however, that
table could be created, but it not really going to be a enforced
relationship. It just a table of actors.

So, we have now:

tblDVD
--- tblActorsInMovie

tblActorsInMoive will be:
fields:
id dvd_id actor_id

Now, it going to be very hard to remember the actor_id, and have to type in
the id (primary key) of the tblActor table (the pk of all of our tables is
assumed to be id). So, in our form for tblActorsInMove, we likely use a
combo box that lets us view/search by actor name, but stores the id.

So, we have to crate a table for the across (since then we only every type
in the actor name once).

tblDVD
--- tblActorsInMovie

tblActors

Note how I not really set tblActores up in the relation. It not really an
enforced relation. If I add a new DVD, or delete a DVD, we not going to do
anything to the tblActors. It just a simple table of actors that we have.
However, we will MOST certainly delete the list of actors that belong to
that dvd in the tblActoresInMove table when we delete a dvd.

Problem is that I knew how to set up such relationships in Paradox quite
easily, as I was used to the API, and interface for construction of the
database app.


The above setup should get you started. I suppose, you could relate the
tblActoresInMove to the tblActores (you can do this, but it would really
only be for documenting purposes - will not actauly do anything for you)


No. Each title has a single ID number, and several pieces of data to go
with it, like UPC code, sound, aspect, etc.


Well, if you don't have any repeating data, then just place all of the
fields
in one main table. Extra tables will be just extra work, and not benefit you
in any way. IF you still need, or use the "id" you currently have, then
have it exposed in a field, but I would continue to use the internal
autonumber
id for the relationships stuff (you, or your end users will NEVER even see
this internal autonumber...as it just for the relationship, and not for
humans to see, or use as some identification for the dvd.


I have a complete database. I have a complete actors list and
directors list.


Great, so now we have:

tblDVD --- tblActorsInMovie

tblActors
tblDirectoors

So, we have ONE standard long number field in the tblDVD that will store the
tblDriecots
(that means we will have only ONE director for each DVD). You could as
mentioned type in this value by memory, but it going to be hard to know the
directors "id" number to type into that field in tblDVD (once again, we use
a combo box on the form. And, once again, this is not really relationship,
but more a lookup value for our easy sake.

So, would consider the diagram like:

tblDVD --- tableDirctors
--- tblActorsInMovie---tblActors



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



  #12  
Old May 28th, 2007, 11:20 PM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Mon, 28 May 2007 11:05:02 -0400, "John Marshall, MVP"
wrote:

Be careful Jack. These newsgroups are for FREE help and steve is imfamous
for trying to lure people away so they have to pay for his services.

John... Visio MVP


Why thank you, John. Despite sending the screenshot to him, your
scenario is definitely on my list of things that will not be happening.


"JackShephard" wrote in
message news
On Mon, 28 May 2007 12:50:33 GMT, "Steve"
wrote:

Jack,

Send me the screenshot of the frontend.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications


Done, but that is an outgoing email account only, so no need to reply
via email.





"JackShephard" wrote in
message ...
Hello folks,

I have a HUGE Excel 2007 DVD spreadsheet with a nice front end, and a
section to cover my purchased library.

If anyone is interested in looking, I can post a screenshot in a binary
Misc pictures group to show you the front end I have made for it using
lookups and a little visual basic code to paste the disc cover images.

What I would like to do is make a nice Access version of this database.
Particularly since it currently carries ove3r 110,000 records, which
makes it hard for excel.

I currently have several sheets in one workbook, all of which have an
ID column and then a column for the data, such as DVD Title, and MPAA
Rating, and where I bought it, MSRP Price, sound, Disc Aspect ratio,
etc.

So, essentially, I have a series of two column sheets, and one at the
end that carries all of my purchased data, and the pointers to the image
files. The first sheet in the workbook is my "master view panel", which
is a vertically oriented pane of all the details for a single title.

That sheet has a tough time importing into access, since the
orientation makes it hard for access to garner what would be fields,
etc.
That sheet is also where all the VLOOKUPS, and VB Code is at.

All of my attempts at constructing an ACCESS front end fail to get me
there.

I was used to "Paradox" years ago where user view panes were "forms".

Is this paradigm still true? Do I bring in the tables, make the
relationships, and then create a form for viewing/stepping
though/searching the data? Or is that now a "report" of some kind?

None of the templates I found online even come close to approaching
what I am looking to do, and my nil experience with access makes it even
harder.

I felt I could import all of the sheets, one by one, and then create
the indexed relationships to each. The problem is that access wants to
put in its own ID field every time.

It would seem, I got "issues". :-]

I have posted the screenshot in alt.binaries.pictures.misc though the
volume of posts there make it a nightmare to haul in headers from.

The post is DVD Database screen shot, for anyone interested.

Thanks for any help you can provide. I am a total Access newbie. :-[


  #13  
Old May 28th, 2007, 11:27 PM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Mon, 28 May 2007 15:05:30 GMT, "Steve"
wrote:

Jack,

I looked at the screenshot you sent me and I can duplicate your Excel work
book and the frontend in an Access database for a very reasonable fee. If
you are interested, get back to me.


Funny. This group is for getting assistance with access.

Funny that you USE this free group, presented to us by Microsoft, as a
launch pad for your snake oil tripe.

Funny that the only reason you wanted to see my screenshot was because
you wanted to make your tripe offer.

This is a getting started group. I have developed databases before,
and all I need are a few pointers with an interface and development model
I am unfamiliar with. This database has no difficult concepts being used
in it. All are quite basic. My needs revolve around basic
familiarization.

Take a hike, slithering snake oil boy.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications


Snipped stupid link.
  #14  
Old May 28th, 2007, 11:31 PM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Mon, 28 May 2007 11:35:09 -0400, "John Marshall, MVP"
wrote:

True to form. You had no intention of providing free help, you only asked
for the screenshot so you could entrap a new victim.

Jack: There are quite a few experts on these newsgroups who would gladly
help for free. As you can see, Albert has already offered. Do not worry
about the amount of information you have, there are several ways to import
your informatio in to a new Access database.

John... Visio MVP


Thank you, John. I trimmed the post so as not to put up his "link to
shame" any more.

Would it not be easier for me to use a simple, ten or twenty record
"snapshot" of my data so as to make all of my changes process a bit
faster at first?

I had some long wait times trying to deal with the entire data set
before.

I wonder if anyone went through the pain of wading through the
alt.binaries.pictures.misc group to find my screen shot there.

Maybe it would be nice if MS provide a group that handled jpeg posts or
the like. I know what vulnerability such a group would invite (idiots
that want to post trash). Sad world.

Thanks for your help.
  #15  
Old May 28th, 2007, 11:41 PM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

So for all of the "only appears as one info item per DVD entry/title
data", I should incorporate all such data into a single table?

That makes sense. That way, my purchase info would be a second table,
and the actors a third, and the director table another as some films do
have more than one, and each director and actor are associated (usually)
with more than one film/title as it were.

So I should be able to do this with only about three or four tables,
one of which will be huge, and the others relatively small.

Thanks for your help. I will re-merge all of the once per title data
into a single table, and try to wade through my old paradigm thoughts
about tables and relationships, and hopefully will become more familiar
with access. The DVD template online was fairly confusing. I'd rather
make the database, and main view form, and then make the "main
switchboard" paradigm stuff. That is one of the things that confused me,
but I am muddling through.


On Mon, 28 May 2007 10:28:13 -0600, "Albert D. Kallal"
wrote:

My data is not simple. The main view sheet looks at about twelve sheets
for its compilation.

Each "table" (sheet) is comprised of a single ID column (field) and some
data, like title or such. That would be quite normalized if I were to
import each of those tables into access as separate tables, no?


Well, the "trick" or concept here is that you DO NOT want to create (well,
import)
a table for just one piece of information. Normalizing your data simply
means you build
a correct data model that models your needs. So, if you have a field that is
the
description for the movie, then you do NOT want to add the extra complexity
of
creating a extra table. If you ONLY have one field, and it not repeating
data (like
many actors), then that field should be in your main table. So, to answer
should
you import this data into "many" tables, the answer is only going to be yes
if
that data is repeating. So, if you have a table that has "many" actors, then
that would continue to be another table. So, we would have two tables so far

tblDVD
--- tblActorsInMovie

So, simply making lots of tables is generally not a good idea for the sake
of making more tables. You ONLY create related tables if the related data
is repeating. In your case, repeating data could be in these extra lookup
tables, or even in the main table (eg: actor1, actore2 is two fields, but
they should be like the above setup, and this I can have as many
actors for a single dvd movie as I need. (no hard coding of how many
actors with fields named actor1, actor2 etc.).


All I would have to do presumably is tie them all together with the ID
field, which is the unique identifier for each.


You main tblDVD would use autonumber id. For child tables, they need
a standard long number field that will be used to point back to the main
tblDVD id. So, the child tables don't really need autonumber fields (but,
you can and should consider just leaving the default "id" as the primary
key for those child tables. All of those child tables will need a plane
Jane standard long number field for the relation. When you add a child
record YOU MUST SET this value. (if you use a sub-form,t hen ms-access
will set this value for you if you setup the relational correctly).



It is not clear if you have "repeating" data for a single dvd (by
repeating,
I mean "many" actors can be attached to one movie).


I intend on incorporating an actors list for each title as well. In
that table, each actor would have a list of ID numbers associated with
each film he or she has appeared in.


Well, don't confuse the actors table with that of "tblActorsInMove".

In our above design so far, we don't have a table of actors. however, that
table could be created, but it not really going to be a enforced
relationship. It just a table of actors.

So, we have now:

tblDVD
--- tblActorsInMovie

tblActorsInMoive will be:
fields:
id dvd_id actor_id

Now, it going to be very hard to remember the actor_id, and have to type in
the id (primary key) of the tblActor table (the pk of all of our tables is
assumed to be id). So, in our form for tblActorsInMove, we likely use a
combo box that lets us view/search by actor name, but stores the id.

So, we have to crate a table for the across (since then we only every type
in the actor name once).

tblDVD
--- tblActorsInMovie

tblActors

Note how I not really set tblActores up in the relation. It not really an
enforced relation. If I add a new DVD, or delete a DVD, we not going to do
anything to the tblActors. It just a simple table of actors that we have.
However, we will MOST certainly delete the list of actors that belong to
that dvd in the tblActoresInMove table when we delete a dvd.

Problem is that I knew how to set up such relationships in Paradox quite
easily, as I was used to the API, and interface for construction of the
database app.


The above setup should get you started. I suppose, you could relate the
tblActoresInMove to the tblActores (you can do this, but it would really
only be for documenting purposes - will not actauly do anything for you)


No. Each title has a single ID number, and several pieces of data to go
with it, like UPC code, sound, aspect, etc.


Well, if you don't have any repeating data, then just place all of the
fields
in one main table. Extra tables will be just extra work, and not benefit you
in any way. IF you still need, or use the "id" you currently have, then
have it exposed in a field, but I would continue to use the internal
autonumber
id for the relationships stuff (you, or your end users will NEVER even see
this internal autonumber...as it just for the relationship, and not for
humans to see, or use as some identification for the dvd.


I have a complete database. I have a complete actors list and
directors list.


Great, so now we have:

tblDVD --- tblActorsInMovie

tblActors
tblDirectoors

So, we have ONE standard long number field in the tblDVD that will store the
tblDriecots
(that means we will have only ONE director for each DVD). You could as
mentioned type in this value by memory, but it going to be hard to know the
directors "id" number to type into that field in tblDVD (once again, we use
a combo box on the form. And, once again, this is not really relationship,
but more a lookup value for our easy sake.

So, would consider the diagram like:

tblDVD --- tableDirctors
--- tblActorsInMovie---tblActors

  #16  
Old May 29th, 2007, 01:09 AM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default ACCESS DVD Database

From your original post ----

All of my attempts at constructing an ACCESS front end fail to get me
there.

None of the templates I found online even come close to approaching
what I am looking to do, and my nil experience with access makes it even
harder.

It would seem, I got "issues". :-]

Thanks for any help you can provide. I am a total Access newbie. :-[

-------
My offer to provide you help was sincere. My offer was to create an Access
database for you that duplicated what you have in your Excel workbook. If
you accepted you would have the database up and running in a short period of
time. From your original post it seemed that was what you are looking for.
It was your choice!

BTW, relook at Mr. Marshall's two responses and try and find any bit of help
regarding you creating a database that duplicates your Excel workbook. There
is none. He is only good for spewing venom. He's not an Access MVP take
note; he's a disgrace to the standards of MVP.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications






"JackShephard" wrote in
message ...
On Mon, 28 May 2007 15:05:30 GMT, "Steve"
wrote:

Jack,

I looked at the screenshot you sent me and I can duplicate your Excel work
book and the frontend in an Access database for a very reasonable fee. If
you are interested, get back to me.


Funny. This group is for getting assistance with access.

Funny that you USE this free group, presented to us by Microsoft, as a
launch pad for your snake oil tripe.

Funny that the only reason you wanted to see my screenshot was because
you wanted to make your tripe offer.

This is a getting started group. I have developed databases before,
and all I need are a few pointers with an interface and development model
I am unfamiliar with. This database has no difficult concepts being used
in it. All are quite basic. My needs revolve around basic
familiarization.

Take a hike, slithering snake oil boy.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications


Snipped stupid link.



  #17  
Old May 29th, 2007, 01:23 AM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Tue, 29 May 2007 00:09:28 GMT, "Steve"
wrote:

My offer to provide you help was sincere.



No. YOUR ATTEMPT to suck cash is all that was sincere.
  #18  
Old May 29th, 2007, 01:23 AM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Tue, 29 May 2007 00:09:28 GMT, "Steve"
wrote:

. If
you accepted you would have the database up and running in a short period of
time.



You asked for money. That is NOT what this group is here for. You need
to get that THROUGH your thick skull.

This group is NOT your SPAM outlet.
  #19  
Old May 29th, 2007, 01:26 AM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Tue, 29 May 2007 00:09:28 GMT, "Steve"
wrote:

BTW, relook at Mr. Marshall's two responses and try and find any bit of help
regarding you creating a database that duplicates your Excel workbook.



BTW, I'd bet that any issues I have, that I post here once I get started
will be dealt with, and he very well may be one of those that assist me.

You, on the other hand, were of NO assistance at all, and merely want
to "quote" someone some lame, overpriced figure for what is obviously
simple assistance.
  #20  
Old May 29th, 2007, 01:27 AM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Tue, 29 May 2007 00:09:28 GMT, "Steve"
wrote:

There
is none. He is only good for spewing venom. He's not an Access MVP take
note; he's a disgrace to the standards of MVP.



And you are exactly what? A money grubber, at best. Do you not have a
day job?
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.