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
  #1  
Old May 28th, 2007, 10:16 AM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

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. :-[
  #2  
Old May 28th, 2007, 10:47 AM posted to microsoft.public.access.gettingstarted
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default ACCESS DVD Database

Have you looked at the template he

http://office.microsoft.com/en-us/te...CT102144001033

The above is by no means complete, but if you take a look at the table
designs, you can see how ms-access performs without the need for
vlookups....

And, there also some sample table designs here to give you some ideas:

http://www.databaseanswers.com/data_models/index.htm

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



  #3  
Old May 28th, 2007, 10:57 AM posted to microsoft.public.access.gettingstarted
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default ACCESS DVD Database

In addition to what I posted, the question becomes how normalized you want
your data to be.

Further, do you have provisions for the actors in movie? Do you want to be
able to search by actors?

The normal approach here is to import the data, and then decide if you want
to re-form the data to multiple tables. or, if you just want to keep your
simple data as you have.

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). if you just have a plain
Jane flat file without any repeating data, then the import is easy. If you
have "repeating" data for a single dvd, then more efforts in design will be
needed.

As for the link to the image field, I would actually just leave that as a
normal text field. You can then place a image control on the form..and it
will display the image for you.

Take a look at the sample. And, if you want to get fancy, you could have the
data base pull the data from the IMDB (so, you can have a list of actors
etc. for your DVD's, but don't have to type them in).



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



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

On Mon, 28 May 2007 03:47:02 -0600, "Albert D. Kallal"
wrote:

Have you looked at the template he

http://office.microsoft.com/en-us/te...CT102144001033


Yes, thanks but that DVD database app was the one I was describing as
pretty sad.

It is an empty database.

I have a 31MB full database that I want to build a front end around.

Since I have never seen a really powerful Access database or front end,
I don't know how or where to start.

The above is by no means complete, but if you take a look at the table
designs, you can see how ms-access performs without the need for
vlookups....


Yes. I know that databases typically perform these functions in a much
more native manner.

And, there also some sample table designs here to give you some ideas:

http://www.databaseanswers.com/data_models/index.htm


I will browse through and try to find the most complicated one I can,
so that I can see what some of its features are, and how they might apply
to my development. Thanks.
  #5  
Old May 28th, 2007, 12:33 PM posted to microsoft.public.access.gettingstarted
JackShephard
external usenet poster
 
Posts: 21
Default ACCESS DVD Database

On Mon, 28 May 2007 03:57:06 -0600, "Albert D. Kallal"
wrote:

In addition to what I posted, the question becomes how normalized you want
your data to be.

Further, do you have provisions for the actors in movie? Do you want to be
able to search by actors?

The normal approach here is to import the data, and then decide if you want
to re-form the data to multiple tables. or, if you just want to keep your
simple data as you have.


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?

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

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. So, it would be a "many to one".
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.

In Access, I am lost on this. I tried once to create some relationships
and got a crash, so I am thinking of making a spreadsheet that is just a
small sample of my current massive workbook, and getting it going with
that, then filling in the data later.


if you just have a plain
Jane flat file without any repeating data, then the import is easy.


I had no problem importing excel sheets into tables in access. I did
have a problem with my main view sheet because it was oriented vertically
on a field by field basis, instead of across like most sheets, and access
puked on that import.

The thing I am having a problem with is that access wants to put its own
ID field in front of mine, and making the relationships between tables,
and finally, form construction to view all the data one "film" record at
a time. I can add the search features later.

If you
have "repeating" data for a single dvd, then more efforts in design will be
needed.


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

The actor's table will be the only place where a given ID number will
be repeated many times.


As for the link to the image field, I would actually just leave that as a
normal text field. You can then place a image control on the form..and it
will display the image for you.


Yes. Something that in excel required a visual basic macro as the
images float over fields (cells), not in them.


Take a look at the sample. And, if you want to get fancy, you could have the
data base pull the data from the IMDB (so, you can have a list of actors
etc. for your DVD's, but don't have to type them in).


I have a complete database. I have a complete actors list and
directors list. I just need to piece them together in access, and am
having a hard time getting started with it. The crash/lockup was one
thing, so I'll start with a concatenated set of films instead of my
entire spreadsheet when I go to establish all the relationships, which is
where it crashed last time.
  #6  
Old May 28th, 2007, 01:50 PM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default ACCESS DVD Database

Jack,

Send me the screenshot of the frontend.

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






"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. :-[



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

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. :-[


  #8  
Old May 28th, 2007, 04:05 PM posted to microsoft.public.access.gettingstarted
John Marshall, MVP
external usenet poster
 
Posts: 482
Default ACCESS DVD Database

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

"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. :-[




  #9  
Old May 28th, 2007, 04:05 PM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default ACCESS DVD Database

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.

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






"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. :-[




  #10  
Old May 28th, 2007, 04:35 PM posted to microsoft.public.access.gettingstarted
John Marshall, MVP
external usenet poster
 
Posts: 482
Default ACCESS DVD Database

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

"Steve" wrote in message
hlink.net...
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.

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






"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. :-[





 




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 12:19 PM.


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